SQL query: return specific value in 2nd table, else return NULL

Question

I'm trying to make a join on 2 tables - let's call them CAT and DRINK. What I'm trying to do is return only a specific "Type" from the DRINK table, else return NULL for that table. That said, I still want all rows from my CAT table.

So if the type of drink I'm trying to return is "Milk", the result of my query: -

Garfield    | Milk 
Tom         | Milk 
Hello Kitty | NULL

In the above example, Garfield and Tom have "Milk" in the DRINK table (they might also have some other values, like "Wine" or "Beer") and Hello Kitty does not have "Milk" (hence the NULL).

I've been trying to solve this doing some UNION or UNION ALL queries combined with a WHERE on "Type" (am I on the right path here?) but have had no luck.

Would anybody please be able to point me in the right direction?

Thanks.

Sounds like maybe you are looking for an Outer join: w3schools.com/sql/sql_join_left.asp — Andrew
– Andrew, Commented Feb 4, 2014 at 15:56
@Andrew Those pages (W3schools) have so many errors that I think that links to them should be removed as spam - from a professional site as SO. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Feb 4, 2014 at 16:00
@ypercube, I still think the links are useful. W3 links are way easier to understand for beginners than MS's syntax pages. — Sev09
– Sev09, Commented Feb 4, 2014 at 16:06
@Sev09 I'd rather they spend extra time to learn the correct way, then spend less time to learn the wrong way. — Kermit
– Kermit, Commented Feb 4, 2014 at 16:07

Lamak · Accepted Answer · 2014-02-04 15:58:18Z

5

Use LEFT JOIN:

SELECT C.Cat, D.Drink
FROM CAT AS C
LEFT JOIN DRINK AS D
    ON C.CatId = D.CatId
    AND D.Drink = 'Milk'

answered Feb 4, 2014 at 15:58

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query: return specific value in 2nd table, else return NULL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related