Data from Three Tables

I want to pull data from three tables, but if the data doesn't exist in the 3rd table, the data from the two other tables should be pulled.

I have a query now:

SELECT m.name, m.zipcode, p.lat, p.lon, meta.meta_value
FROM members AS m
INNER JOIN zipcodes AS p ON m.zipcode = p.zipcode
INNER JOIN usermeta AS meta ON m.id = meta.id
WHERE m.zipcode = p.zipcode AND meta.meta_key = 'image' 开发者_如何学JAVAm.country = "USA"

m.id and meta.id don't exist for all of them.

For those who have m.id, "image" doesn't exist for all.

I want to pull the data from the 2 other tables independently of the existence of "image" and m.id.

How can I do that?

You are using INNER JOINs; use OUTER JOINS to get NULL values in columns from joined tables where the condition is false.

For example:

SELECT m.name, m.zipcode, p.lat, p.lon, meta.meta_value
FROM members AS m
LEFT OUTER JOIN zipcodes AS p ON m.zipcode = p.zipcode
LEFT OUTER JOIN usermeta AS meta ON m.id = meta.id
WHERE m.zipcode = p.zipcode AND meta.meta_key = 'image' m.country = "USA"

How about changing the query to

SELECT m.name, m.zipcode, p.lat, p.lon, meta.meta_value
FROM members AS m
INNER JOIN zipcodes AS p ON m.zipcode = p.zipcode 
LEFT OUTER JOIN usermeta AS meta ON m.id = meta.id
WHERE m.zipcode = p.zipcode AND COALESCE(meta.meta_key,'image')='image' AND m.country = 'USA'