CODEWITHSUNDEEP
sqljoinleft-joininner-join
Marci-man
Marci-man

Reputation: 2233

SQL join statement with where clause

I have this question, from I am trying to get through. I have been trying to do this for hours, it would be really great if someone could point us in the right direction.

This is in german so I am going to translate it in English:

We only have problem with part (d)

(d) Write an SQL statement that gives names of the restaurant and Ort(Place) of the restaurant that do not order any Salat.

The expected result would be:

Da Mafia : Göttingen
Venezia : Kassel

enter image description here

Upvotes: 0

Views: 1957

Answers (2)

shawnt00
shawnt00

Reputation: 17953

It doesn't apply to LolCoder's answer, but remember to be careful with NOT IN when the subquery has nulls.

This is another approach with variations on the theme. It's not exactly fancy even though it does use an inner join. You'll probably find them all in the wild.

My preference is the first one. I'm also usually explicit about "else null" even though isn't technically necessary.

-- A
SELECT name, min(ort) as ort
FROM kunde as k INNER JOIN Liefervertrag as l on l.pizzeria = k.name
GROUP by name
HAVING count(case when l.product = 'salat' then 1 else null end) = 0

-- B
SELECT name, min(ort) as ort
FROM kunde as k INNER JOIN Liefervertrag as l on l.pizzeria = k.name
GROUP by name
HAVING sum(case when l.product = 'salat' then 1 else 0 end) = 0

-- C
SELECT name, ort
FROM kunde as k INNER JOIN Liefervertrag as l on l.pizzeria = k.name
GROUP by name, ort
HAVING count(case when l.product = 'salat' then 1 else null end) = 0

-- D
SELECT name, ort
FROM kunde as k INNER JOIN Liefervertrag as l on l.pizzeria = k.name
GROUP by name, ort
HAVING sum(case when l.product = 'salat' then 1 else 0 end) = 0

Upvotes: 3

Akash KC
Akash KC

Reputation: 16310

You can try like this: 

  SELECT name,ort FROM kunde WHERE name NOT IN (SELECT DISTINCT(pizzeria) FROM Liefervertrag WHERE product='salat')

Upvotes: 1

Related Questions