CODEWITHSUNDEEP
mysqlunion
Maurice
Maurice

Reputation: 1157

Having problems using UNION in a LEFT JOIN query

I have the following joined query:

SELECT t1.*, t2.*
FROM TabContent t1
LEFT JOIN HighScore t2
  ON t1.Name = t2.Layout
WHERE TabID IN (1,2,3)
ORDER BY TabID, LayoutName

Result:

+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
|TabConID|  Name  | Tiles  | Layers | TabID  |Score ID|  fbID  | Layout |  Score |  Time  |  Stars |  Date  |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 1      | beetle |   30   |    5   |   1    |   1    |  2222  | beetle |  293   |  13:00 |   1    |  date  |
| 2      |elephant|   31   |    4   |   1    |  NULL  |  NULL  |  NULL  |  NULL  |  NULL  |  NULL  |  NULL  |
| 3      | dragon |   31   |    5   |   2    |   2    |  3333  | dragon |  232   |  08:35 |   2    |  date  |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+

Now I want to show all Names (and the data that comes with it) that fbID 2222 owns. I did that like this:

SELECT t1.*, t2.* FROM TabContent t1 LEFT JOIN HighScore t2 ON t1.Name = t2.Layout WHERE fbID = 2222 AND TabID IN (1,2,3) ORDER BY TabID, LayoutName

Result:

+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
|TabConID|  Name  | Tiles  | Layers | TabID  |Score ID|  fbID  | Layout |  Score |  Time  |  Stars |  Date  |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 1      | beetle |   30   |    5   |   1    |   1    |  2222  | beetle |  293   |  13:00 |   1    |  date  |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+

So far so good! The thing is, there are more Names (elephant, dragon). I want to show these too, but leave the data (accept the TabConID, Name, Tiles, Layers, TabID) blank.

This is the result I need:

+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
|TabConID|  Name  | Tiles  | Layers | TabID  |Score ID|  fbID  | Layout |  Score |  Time  |  Stars |  Date  |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 1      | beetle |   30   |    5   |   1    |   1    |  2222  | beetle |  293   |  13:00 |   1    |  date  |
| 2      |elephant|   31   |    4   |   1    |  NULL  |  NULL  |  NULL  |  NULL  |  NULL  |  NULL  |  NULL  |
| 3      | dragon |   31   |    5   |   2    |  NULL  |  NULL  |  NULL  |  NULL  |  NULL  |  NULL  |  NULL  |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+

The best way to do this is with UNION. If this was a normal Query and not a joined one, the query would look something like this (this is a non join example, that I am not using, but to explain things):

SELECT * FROM `table` WHERE fdID = 2222 AND TabID IN (1,2,3) ORDER BY TabID, LayoutName
UNION 
select TabConID, Name, Tiles, Layers, TabID, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM `table` where fbID != 2222

Unfortunately this is a joined query and I have no idea how to make the UNION part work with this. Also searched using Google but had no luck.

Hope someone can help!

Upvotes: 0

Views: 186

Answers (3)

Abhay
Abhay

Reputation: 6645

You do not need to do a UNION query. Simply move the "fbId" condition from WHERE to the JOIN condition.

The below query should give you the desired result:

SELECT t1.*, t2.*
FROM TabContent t1
LEFT JOIN HighScore t2
  ON t1.Name = t2.Layout AND t2.fbId = 2222
WHERE TabID IN (1,2,3)
ORDER BY TabID, LayoutName

Hope it helps!

Upvotes: 1

aleroot
aleroot

Reputation: 72636

You have to change your query and put ORDER BY at bottom, something like the following should work : 

SELECT * FROM `table` WHERE fdID = 2222 AND TabID IN (1,2,3) 
UNION 
select TabConID, Name, Tiles, Layers, TabID, NULL AS Score ID, NULL AS fbID, NULL AS Layout, NULL AS  Score FROM `table` where fbID != 2222
ORDER BY TabID, LayoutName

Upvotes: 1

wildplasser
wildplasser

Reputation: 44250

You cannot have the WHERE clause before the UNION.

To do what you want you probably need to squeeze the first "leg" of the union into a subquery.

Upvotes: 0

Related Questions