MySQL: Trouble trying to get my SQL statement to work when condition is true but can also accept false

Talktomegoose 0
Viewed 28

I have the following statement:

SELECT tableA.*, tableB.* FROM tableA, tableB WHERE tableA.userUUID = ? AND tableB.uuid=tableA.tableBUUID

So when a record in table A has a valid tableBUUID and the tableBUUID exists in tableB then the query runs file. But when I removed the tableBUUID from table B without removing it from the record in tableA then the query returns nothing (Which is obviously expected)

I am wondering how I can change the query so that even if the tableBUUID does not match anything, the query will still return what it can even if its nothing/ blank columns from tableB?

Thanks all

mysql
1 Answers

You can achieve this via a LEFT JOIN:

SELECT tableA.*, tableB.* 
  FROM tableA 
  LEFT JOIN tableB ON tableB.uuid = tableA.tableBUUID
WHERE tableA.userUUID = ?

Fiddle.

Performing a LEFT JOIN will return data from tableA and matching records from tableB if it exists in tableB, otherwise it'll return only the records from tableA.

LEFT JOIN

In the syntax you posted above in your question, you're essentially doing a INNER JOIN by comma separating your tables. This is the old syntax, moving forward you should explicitly write INNER JOIN like so:

SELECT * FROM tableA a INNER JOIN tableB b ON a.column_key = b.column_key

Read more about it in the following previous question:
What's the difference between comma separated joins and join on syntax in MySQL?

GRIV0
Related