CODEWITHSUNDEEP
mysql
user2701366
user2701366

Reputation: 55

MySQL, Show value of column if match exist else leave as null

I'm sure this has been asked before but can't find the answer.

I have 3 tables OWNER, CAR, HOUSE

OWNER has 2 columns id and name

CAR has 3 columns id, ownerId and cartype

HOUSE has 4 columns id, ownerId, address, country

I want to write a SQL query that gets the owners name, cartypes, and addresses that are in Sweden

Here comes the tricky part. I want all the owners names and cartypes in the result table even if they don't own a house in Sweden. Can I get all that in 1 query or do I need to use 2? How would that query look?

Upvotes: 0

Views: 1740

Answers (2)

Anil
Anil

Reputation: 2554

You should be able to accomplish this with a simple left join:

SELECT O.name, C.cartype, H.address, H.country
FROM OWNER AS O
JOIN CAR AS C ON O.id = C.ownerid
LEFT JOIN HOUSE AS H ON O.id = H.ownerid AND Ucase(H.country) = "SWEDEN"

This will always give you a list of owners and their car types, in addition, it will give you a list of those that also happen to have a house address in sweden.

Upvotes: 2

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

First you need to join the table then add new column in query by using CASE to check

SELECT o.* , c.* ,h.*, 
(CASE WHEN h.county ='sweden' THEN h.county ELSE NULL END) AS HasCountry
FROM OWNER o 
JOIN CAR c ON (c.ownerId =o.id)
JOIN HOUSE h ON (h.ownerId =o.id)

Upvotes: 1

Related Questions