1

just having an issue with a mySQL query. Basically I have three tables

  1. tag
  2. directorycolumn
  3. directorycolumntags

There is a many to many relationship between directorycolumn and tag hence the directorycolumntags table acting as the junction table. I am trying to construct a query that will return the tag name from the tag table using a left join between the other two tables.

This is my Query:

SELECT t.name FROM tag t 
LEFT JOIN directorycolumntags dct 
ON t.id = dct.tag_id
AND dct.directorycolumn_id = '178'

But rather than just returning the tags associated with columns with the id 178 it returns all the tags.Can anyone help me on this?

This is the data in the directorycolumntags table:

id  directorycolumn_id  tag_id  description created
29      178                1          \N    2014-11-05 17:31:22
30      178                2          \N    2014-11-05 17:31:30
31      178                3          \N    2014-11-05 17:31:42

This is the data in the tag table:

id  name    description
31  Tag 1   This is Tag 1
32  Tag 2   This is Tag 2
33  Tag 3   This is Tag 3
34  Tag 4   This is Tag 4
35  Tag 5   This is Tag 5

This is the result of my above query:

name
Tag 1
Tag 2
Tag 3
Tag 4
Tag 5

If I change the query to an INNER JOIN Like so:

SELECT t.name FROM tag t 
JOIN directorycolumntags dct 
ON t.id = dct.tag_id
AND dct.directorycolumn_id = '178'

I am returned no results. This is strange as when I execute this query

SELECT * FROM directorycolumntags WHERE directorycolumn_id = 178

I get the following results:

id  directorycolumn_id  tag_id  description created
29       178               1        \N       2014-11-05 17:31:22
30       178               2        \N       2014-11-05 17:31:30
31       178               3        \N       2014-11-05 17:31:42
Javacadabra
  • 5,578
  • 15
  • 84
  • 152

2 Answers2

2

When you do LEFT JOIN you get all the rows from the LEFT Table and NULL values for not matched rows from the right table

if you want all the tag values for column 178, just use INNER JOIN

SELECT t.name FROM tag t 
JOIN directorycolumntags dct 
ON t.id = dct.tag_id
AND dct.directorycolumn_id = '178'

Based on the EDIT done to the question the join on tag.id and directorycolumntags.tag_id are not yielding any results as there is no match.

i think you need to do this, to get your expected results

SELECT t.name FROM tag t 
JOIN directorycolumntags dct 
ON concat('Tag ',dct.tag_id) = t.name
AND dct.directorycolumn_id = 178
radar
  • 13,270
  • 2
  • 25
  • 33
  • 1
    While I agree this should be an `Inner Join`, would his query, as written, not be equivalent? – Siyual Nov 05 '14 at 17:48
  • Your explanation makes sense, but the sample code you've provided returns no results at all. – Javacadabra Nov 05 '14 at 17:49
  • @Siyual, yes putting condition on where clause or ON caluse is same, if it is INNER JOIN, but different if it is left join. http://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause – radar Nov 05 '14 at 17:50
  • @Javacadabra, that means you don't have tags for the column 178. select * from directorycolumntags where directorycolumn_id='178' should give zero results. is your id column varhcar or integer – radar Nov 05 '14 at 17:52
  • @RADAR Not what I meant, though. He has a `Left Join` with a `Where`, which would be the same as an `Inner Join` with either an `On` or `Where`. Compiled, your query would be the same as the one he already has. – Siyual Nov 05 '14 at 17:52
  • @Siyual, left join with where, will still get all tag names from the left join as where is on the second table not on the first table column . – radar Nov 05 '14 at 17:53
  • @RADAR, I edited my initial question based on your last comment with the results of that query, it is infact returning rows – Javacadabra Nov 05 '14 at 17:55
  • @RADAR Ah! - I see what you mean now. Right, that makes sense. – Siyual Nov 05 '14 at 17:55
  • @Javacadabra, how can you compare t.id and tag_id in that case? – radar Nov 05 '14 at 17:57
  • @Javacadabra, updated the answer, the ids are not matching so results are not found, i think you wanted to match on tag name – radar Nov 05 '14 at 18:06
  • Thank you @RADAR that is working perfectly, although, im not fully sure exactly why, would you be able to expand on it a little bit. Thank you regardless – Javacadabra Nov 05 '14 at 18:47
1

You are changing your Left Join to an Inner Join with the Where clause.

Move the Where statement to your On condition.

SELECT t.name FROM tag t 
LEFT JOIN directorycolumntags dct 
ON t.id = dct.tag_id
AND dct.directorycolumn_id = '178'

Edit: Based on @RADAR's response, he is correct in assuming it should be an Inner Join rather than an Outer Join. This should show you what you're looking for, though, credit should go to him:

SELECT t.name FROM tag t 
JOIN directorycolumntags dct 
ON t.id = dct.tag_id
WHERE dct.directorycolumn_id = '178'
Siyual
  • 16,415
  • 8
  • 44
  • 58