mariadb - How to do WHERE clause in UNION and LEFT JOIN?

Question

I am trying to do a UNION on 4 tables to get the ID's from each table, WHERE the USER_ID = $_SESSION['uid']
I will explain each query to give you an idea of where I came from, and where I am going. The first query works fine, the problem: only ID's of each record. I need to left JOIN to get each records corresponding data.

SELECT DISTINCT(bid_id) FROM bids_questions
UNION SELECT DISTINCT(bid_id) FROM bids_qualify_requests WHERE type = 'Prequalified'
UNION SELECT DISTINCT(bid_id) FROM bids_documents_download
UNION SELECT DISTINCT(bid_id) FROM bids_documents_upload
WHERE uid=? GROUP BY bid_id

$stmt->bind_param('i', $_SESSION['uid']);

In the second attempt I add a left join. This query works fine, however it lacks the WHERE clause that specified the user ID.

SELECT 
bid_id.bid_id,//BID ID's  
b.title,
u.name
FROM (
SELECT DISTINCT(bid_id) FROM bids_questions
UNION SELECT DISTINCT(bid_id) FROM bids_qualify_requests WHERE type = 'Prequalified'
UNION SELECT DISTINCT(bid_id) FROM bids_documents_download
UNION SELECT DISTINCT(bid_id) FROM bids_documents_upload
) AS bid_id
LEFT JOIN bids b ON b.bid_id = bid_id.bid_id
LEFT JOIN users u on b.uid = u.uid
GROUP BY bid_id

If I try to add the WHERE clause in there, I get an error saying the column does not exist.

SELECT 
bid_id.bid_id,//BID ID's  
b.title,
u.name
FROM (
SELECT DISTINCT(bid_id) FROM bids_questions
UNION SELECT DISTINCT(bid_id) FROM bids_qualify_requests WHERE type = 'Prequalified'
UNION SELECT DISTINCT(bid_id) FROM bids_documents_download
UNION SELECT DISTINCT(bid_id) FROM bids_documents_upload
) AS bid_id
LEFT JOIN bids b ON b.bid_id = bid_id.bid_id
LEFT JOIN users u on b.uid = u.uid
WHERE bid_id.uid=?
GROUP BY bid_id

$stmt->bind_param('i', $_SESSION['uid']);

Is there a way I can add the WHERE clause that applies to every inner query? I am sure I could add a separate WHERE in each query and bind a param for each. Or maybe I select UID and bid_id from all tables then select only the UID out of that table? I want the optimal performance option as this DB will be quite large. Any ideas?

question from:https://stackoverflow.com/questions/65878736/how-to-do-where-clause-in-union-and-left-join

Answer 1

In your first attempt, the WHERE clause is only enacted on the final SELECT of all the UNIONS ... i.e. only on:

SELECT DISTINCT(bid_id) FROM bids_documents_upload WHERE uid=? GROUP BY bid_id

In this, the GROUP BY is also redundant as the DISTINCT keyword will do the same thing.

In the second attempt - your INNER QUERY can stand alone, so if the WHERE clause was valid over the UNION then it can reside inside the brackets. You have not stated where you added the WHERE clause to get the error message that you report. If it was outside the INNER select then of course it doesn;t exist as you only return bid_id from the UNION statements - no ```uid`` is included.

You will need to expand on the information provided - at least the schemas of each of the tables involved in the UNIONs as this would drive what the INNER query needs to be before joining it back to the bids table and possibly also the users table.

As you are working on a single uid it may be better to do a single lookup on the users table to extract and store the name for the uid in a variable rather than joining to the table.

If you added the additional information then you may have more success in getting a solution.