WHERE clause with MySQL LEFT JOIN

Question

I am having trouble building a LEFT JOIN query to filter out specific data.

TableA

+-----------------+-------------+
| name (UNIQUE)   | description |
+-----------------+-------------+
| test_1          | desc_1      |
| test_2          | desc_2      |
| test_3          | desc_3      |
| test_4          | desc_4      |
+-----------------+-------------+

TableB

+--------+-------+
| name   | data  |
+--------+-------+
| test_1 | val_1 |
| test_2 | val_1 |
| test_1 | val_2 |
| test_1 | val_3 |
+--------+-------+

Using the following query I get the below result:

SELECT tableA.name, tableA.description tableB.data
FROM tableA 
LEFT JOIN tableB
ON tableB.name=tableA.name
WHERE tableB.data='val_1'

Result:

+--------+-------+-------------+
| name   | data  | description |
+--------+-------+-------------+
| test_1 | val_1 | desc_1      |
| test_2 | val_1 | desc_2      |
+--------+-------+-------------+

Here is the result I am looking for:

+--------+-------+-------------+
| name   | data  | description |
+--------+-------+-------------+
| test_1 | val_1 | desc_1      |
| test_2 | val_1 | desc_2      |
| test_3 | NULL  | desc_1      |
| test_4 | NULL  | desc_2      |
+--------+-------+-------------+

Is it possible to build a query that filters like that?

how did you get this raw test_3 | NULL | desc_1 ? why test_3 linked to desc_1 what is logic here? — Alex
– Alex, Commented Feb 19, 2015 at 3:51

Entalyan · Accepted Answer · 2015-02-19 03:52:35Z

4

Because you put a filter on TableB in the WHERE clause, you remove all rows from the final output that have 'val_1' in the TableB.data column. If you make the condition part of the LEFT JOIN, you filter the records coming from TableB, but still get all records from TableA.

Your query would look like this:

SELECT tableA.name, tableA.description, tableB.data
FROM tableA 
LEFT JOIN tableB
ON tableB.name=tableA.name
AND tableB.data='val_1'

This will output:

NAME DESCRIPTION DATA
test_1 desc_1 val_1 
test_2 desc_2 val_1 
test_3 desc_3 (null) 
test_4 desc_4 (null)

The output you describe, with the combinations:

test_3 desc_1 (null)
test_4 desc_2 (null)

Does not seem to be a sensible output for this query, because that data isn't available in the tables, or I'm missing some information.

answered Feb 19, 2015 at 3:52

Entalyan

9418 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Grant Martin Over a year ago

I watered it down for the sake of example, it makes sense with the way it works in my PHP script (matching user skills to possible user skills). Thank you for you help!

Entalyan Over a year ago

You're welcome. And thanks for accepting the answer, I just joined earlier today and this apparently gives me points, of which I didn't have any yet :)

Alex · Accepted Answer · 2015-02-19 03:54:21Z

0

SELECT tableA.name, tableA.description, b.data
FROM tableA 
LEFT JOIN ( 
SELECT * FROM tableB
WHERE tableB.data='val_1' ) as b
ON b.name=tableA.name

answered Feb 19, 2015 at 3:54

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Collectives™ on Stack Overflow

WHERE clause with MySQL LEFT JOIN

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related