SQL Join: Select rows with no match in second table

Question

I am trying to join two tables while still getting the results from table A even if there is no matching record in table B. I think the where clause is causing the problem but I can't seem to fix it.

SELECT a.id, a.title, a.validation, b.data 
FROM client_option_detail AS a LEFT OUTER JOIN client_data AS b 
ON a.id = b.client_option_detail_id 
WHERE a.client_option_id = ?
AND (b.client_id IS NULL OR b.client_id = ?)

UPDATE: database layout

client_option       client_option_detail 
-------------       ------------------- 
id                  id
                    title
                    validation          
                    client_option_id


client          client_data
-------         ------------ 
id              client_id
                client_option_detail_id             
                data

database sample:

client_option       client_option_detail 
-------------      ------------------------------------------- 
id                  id | title | validation | client_option_id
-------------      -------------------------------------------
1                  1   | test1 |            | 1  
2                  2   | test2 |            | 1                               
                   3   | test3 |            | 1
                   4   | test4 |            | 2


client              client_data (primary key - client_id + client_option_detail_id)
--------            ------------------------------------------- 
 id                 client_id | client_option_detail_id | data
--------            -------------------------------------------
 1                  1         | 1                       | data1
 2                  1         | 2                       | data2
                    1         | 4                       | data3
                    2         | 3                       | data4
                    2         | 1                       | data5

sample query:

SELECT a.id, a.title, a.validation, b.data 
FROM client_option_detail AS a LEFT OUTER JOIN client_data AS b 
ON a.id = b.client_option_detail_id 
WHERE a.client_option_id = 1
AND (b.client_id IS NULL OR b.client_id = 1)

required output:

    ------------------------------------------- 
     id | title | validation | data
    -------------------------------------------
     1  | test1 |            | data1                                 
     2  | test2 |            | data2
     3  | test3 |            |

Which is your table "A" and "B" supposed to be. Are you looking for clients or options detail. Also, you should always qualify your columns with the table (or alias) the column comes from when your table structures are not listed to confirm. — DRapp, Feb 15 '16 at 03:21
@DRapp I've updated the code in my question. I am trying to grab all client_option_detail (table A) rows that match the client_option_id. Also I want the matching data field from client_data (table B) that has a specific client_id. — meeeee, Feb 15 '16 at 04:59
So are you looking for a UNION of both? The client_Data table doesn't have the same structure, or is it you want ALL option details for one specific client PLUS anyone else who specifically has the option ID as the parameter? — DRapp, Feb 15 '16 at 05:14
@DRapp close, what i want is ALL option details for one specific client PLUS the data from client_data that matches the client and the option detail. BTW client and client_data are not the same tables — meeeee, Feb 15 '16 at 06:56

Kason · Answer 1 · 2016-02-15T02:29:20.583

0

SELECT client_option_detail.id, title, validation, data 
FROM client_option_detail LEFT OUTER JOIN client_data 
ON client_option_detail.id = client_data.client_option_detail_id

It should be okey. I think is your where condition has some problem.
For example : client_option_detail.client_option_id = ? ,? is it contain all the id in client_option_detail ?

edited Feb 15 '16 at 02:29

answered Feb 15 '16 at 02:02

Kason

787
3
14

I want to get all the records from table A, including the ones that are not matched. I tried it anyway but it didn't help. – meeeee Feb 15 '16 at 02:23
not sure what you are asking. Every client_option_detail has a unique client_option_id if that's what you mean. – meeeee Feb 15 '16 at 03:17
sorry its not unique it's a foreign key that is always populated – meeeee Feb 15 '16 at 03:24

score 0 · Answer 2 · edited May 23 '17 at 12:13

You do not need the AND (b.client_id IS NULL OR b.client_id = ?)

SELECT a.id, a.title, a.validation, b.data 
FROM client_option_detail AS a 
LEFT JOIN client_data AS b 
ON a.id = b.client_option_detail_id 
WHERE a.client_option_id = ?

output will be something like:

id     title     validation     data
1      foo       bar            sample_data1
2      apple     oranges        null
3      try       it             sample_data2

Like in the sample output number 2, data will be null if there is not matched from table B and information from table A will still be shown

also, if you really wanted to filter based on specified client_id from B, then just put or on the last part like below. It means that if the data contains either specified client_id from B or if it has the specified client_option_id from A, then it will be retrieved.

SELECT a.id, a.title, a.validation, b.data 
FROM client_option_detail AS a 
LEFT JOIN client_data AS b 
ON a.id = b.client_option_detail_id 
WHERE a.client_option_id = ? OR b.client_id = ?

for more information, you might want to look at this post

UPDATE:

Try below codes for your update:

SELECT a.id, a.title, a.validation, b.data 
FROM client_option_detail AS a 
LEFT JOIN client_data AS b 
ON a.id = b.client_option_detail_id AND b.client_id = a.client_option_id
WHERE a.client_option_id = 1

or this, i dont know which is right or the one that best suits for your needs but both yields same result based on your sample data:

SELECT a.id, a.title, a.validation, b.data 
FROM client_option_detail AS a 
LEFT JOIN client_data AS b 
ON a.id = b.client_option_detail_id AND b.client_id = 1
WHERE a.client_option_id = 1

tried using SQLize:

I hope I got it right this time. Good luck

I don't want to get the client id. i want to filter results based on the client id — meeeee, Feb 15 '16 at 07:22
yes, we are not getting the client id, we are filtering those who have specified client id. did u ran this code? — Ceeee, Feb 15 '16 at 07:26
tried it, didn't work. i've updated my question to include a sample. hopefully that will help you guys understand what I need — meeeee, Feb 15 '16 at 08:04
see my update @meeeee :) i like your name :D ahahaha ceeee and meeeee lol — Ceeee, Feb 15 '16 at 13:51

SQL Join: Select rows with no match in second table

2 Answers2