CODEWITHSUNDEEP
mysqlsql
Gaga
Gaga

Reputation: 87

How to select using UNION all fields including NULL?

I use a query:

SELECT idFavourite AS entity, 'favourite' AS type 
FROM favourite 
WHERE idCar = 1 AND idUser = 1 
UNION
SELECT idLike AS entity, 'likes' AS type 
FROM `likes` 
WHERE idCar = 1 AND idUserLiked = 2

If second condition is not right, this query returns only one type. How I can get always two type of both queries?

New query:

SELECT likes.idLike AS likes, favourite.idFavourite AS fav FROM favourite

LEFT JOIN `likes` ON (likes.idCar = favourite.idCar) AND (likes.idUserLiked = favourite.idUser) AND
likes.idCar = 1 AND likes.idUserLiked = 2

Upvotes: 0

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

First, use union all unless you want to incur the overhead of removing duplicates (which is not possible in this case). You can do:

SELECT idFavourite AS entity, 'favourite' AS type
FROM favourite 
WHERE idCar =1 AND idUser = 1
UNION ALL
SELECT idLike AS entity, 'likes' AS type
FROM `likes` 
WHERE idCar = 1 AND idUserLiked = 2
UNION ALL
SELECT NULL, 'likes'
FROM (SELECT 1 as x) t
WHERE NOT EXISTS (SELECT 1 FROM likes WHERE idCar = 1 AND idUserLiked = 2);

Alternatively, if you only expect at most one row from the second subquery, then use aggregation:

SELECT idFavourite AS entity, 'favourite' AS type
FROM favourite 
WHERE idCar =1 AND idUser = 1
UNION ALL
SELECT MAX(idLike) AS entity, 'likes' AS type
FROM `likes` 
WHERE idCar = 1 AND idUserLiked = 2;

An aggregation query with no GROUP BY always returns one row. In this case, entity will be NULL if the WHERE clause filters out everything.

Upvotes: 1

Related Questions