Reputation: 171
I only want to see the rows where the column 'Stadt'(=City) isn´t equal to 'Hauptstadt'(=Capital). my table looks like:
+----------------------+-------------------+--------------------------+---------------+
| Name | Stadt | Land | Hauptstadt |
+----------------------+-------------------+--------------------------+---------------+
| Kunstmuseum | Bern | Schweiz | Bern |
| Musée Picasso | Paris | Frankreich | Paris |
| Museum Ludwig | Köln | Deutschland | Berlin |
| Museum of Modern Art | New York | United States of America | Washington DC |
| Städel | Frankfurt am Main | Deutschland | Berlin |
+----------------------+-------------------+--------------------------+---------------+
I only want to see(desired output):
+----------------------+-------------------+--------------------------+---------------+
| Name | Stadt | Land | Hauptstadt |
+----------------------+-------------------+--------------------------+---------------+
| Museum Ludwig | Köln | Deutschland | Berlin |
| Museum of Modern Art | New York | United States of America | Washington DC |
| Städel | Frankfurt am Main | Deutschland | Berlin |
+----------------------+-------------------+--------------------------+---------------+
I tried it this:
SELECT DISTINCT Name, Stadt, Land ,Hauptstadt
FROM Museum
GROUP BY 'Name', 'Stadt', 'Land', 'Hauptstadt'
ORDER BY Name;
Output:
+----------------------+----------+--------------------------+---------------+
| Name | Stadt | Land | Hauptstadt |
+----------------------+----------+--------------------------+---------------+
| Museum of Modern Art | New York | United States of America | Washington DC |
+----------------------+----------+--------------------------+---------------+
How should my SELECT look like to get my desired ouput?
Thanks for your help!
Upvotes: 0
Views: 127
Reputation: 347
we need to group it by name and just compare the two columns if is equal or not
SELECT * FROM Museum WHERE Stadt != Hauptstadt GROUP BY Name;
Upvotes: 0
Reputation: 1271241
The single quotes in the group by
are messing you up. First, only use single quotes for string and date constants. Second, you almost never need group by
with select distinct
. Third, you need a where
clause for your condition. So try this:
SELECT DISTINCT Name, Stadt, Land ,Hauptstadt
FROM Museum
WHERE Stadt <> Hauptstadt
ORDER BY Name;
Upvotes: 1