Menu
Say I have a table:
Restaurant locations: RESTAURANT_NO | RESTAURANT_LOCATION ----------------------------------- 1 | City A 1 | City B 2 | City A 2 | City B 2 | City C 3 | City C 4 | City A 4 | City B How would I be able to group them together and also only select the RESTAURANT_NO that do not have locations in city C?
Using this example, I want to return:
RESTAURANT_NO ------------- 1 4 Since RESTAURANT_NO 2 and 3 both have locations in city C.
I do not know how to group RESTAURANT_NO together while also trying only to select the groups that meet this requirement.
EDIT: I got this working.
However, there is one last thing that I still have not been able to figure out. The following table has the ID number of people along with cities they have worked in:
PERSON_NO | CITY_NAME --------------------- 1 | City A 2 | City B 3 | City A 3 | City B 3 | City C 4 | City A 4 | City B 4 | City C How would I be able to get the PERSON_NO of all the people who have lived in all three cities, A,B, and C?
I want to return
PERSON_NO --------- 3 4 Thanks, again. I haven't had that much experience with SQL and so I'm not sure what to do.
297
The answer is yes, but only in a very certain way. Now, we want to add two more columns—let's call them other1 and other2—but we don't want to add them to the GROUP BY, because we still want only one row per foo in the results.
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. The original idea was to create the table in beginning of the query, so the (SELECT * FROM #TBL) could be used on the query itself, instead of defining the names on each GROUP BY.
To avoid grouping by a specific column that returns multiple values, you can either remove it from the query, or you can explicitly tell it which value you want. You can do this using aggregate or analytic functions, like: MAX(ExtensionID) MIN(ExtensionID)
The SQL EXCEPT operator is used to exclude like rows that are found in one query but not another. It returns rows that are unique to one result. To use the EXCEPT operator, both queries must return the same number of columns and those columns must be of compatible data types.
One way:
SELECT RESTAURANT_NO FROM restaurant WHERE RESTAURANT_NO NOT IN (SELECT RESTAURANT_NO FROM restaurant WHERE RESTAURANT_LOCATION = 'City C') 
SELECT DISTINCT Restaurant_no FROM TableX t WHERE NOT EXISTS ( SELECT * FROM TableX c WHERE c.Restaurant_no = t.Restaurant_no AND c.Restaurant_location = 'City C' ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
