0

I want to select a value from a third_table where something from the first_table equals another thing from the third_table. This last value from the third table might not exist!

I have the following mysql query:

$query = "SELECT ft.value1, ft.value2, st.value3, st.value4, tt.valueWantedMightNotExist 
          FROM first_table ft, second_table st, third_table tt 
          WHERE ft.value5 = st.value5" AND ft.something = tt.anothersomething;

This query returns results only if the value exists. Otherwise it returns nothing. I tried this with FULL JOINas well but ended messing up as well. Can someone explain to me how can I return a null value if the record doesn't exist? Thank you very much...

Barmar
  • 669,327
  • 51
  • 454
  • 560
Andre Calenta
  • 77
  • 2
  • 7
  • What does that quotation mark do? And how do these tables relate to one another? I think you need to start with a simple introduction to PHP and MySQL. – Strawberry Dec 23 '14 at 12:47

2 Answers2

3

You need a LEFT JOIN. This will do exactly what you want.

By default, (i.e., if you just use a comma,) you get an INNER JOIN, which will give you rows only when they match on both sides; but a LEFT JOIN will give you everything on the left, and nulls on the right where there's no match.

There's also a RIGHT JOIN, the purpose of which is left as an exercise for the astute reader :)

You should look at this question. The top-voted answer uses LEFT OUTER JOIN, but in MySQL at least, the OUTER has no effect. And the second-highest answer has a pretty picture that's rather helpful for the intuition.

Community
  • 1
  • 1
chiastic-security
  • 20,131
  • 3
  • 36
  • 65
3

You need to use LEFT JOIN if you want to get rows with no match.

SELECT ft.value1, ft.value2, st.value3, st.value4, tt.valueWantedMightNotExist
FROM first_table ft
INNER JOIN second_table st ON ft.value5 = st.value5
LEFT JOIN third_table tt ON ft.something = tt.anothersomething
Barmar
  • 669,327
  • 51
  • 454
  • 560