Not sure what sort of query to use - JOIN?.

lou73 · #1 (**permalink**) 06-17-10, 09:52

I have 3 tables Jobs, Quotes and Questions eg:-

Jobs
-----------
JobID
JobName

Quotes
-----------
JobID
UserID

Questions
-----------
JobID
UserID

I'm trying to get a grouped result set of JobID's where UserID = ? in either Quotes or Questions I don't think I can use a JOIN as the user may have entered questions but not a quote.

Nearest thing I have so far is this but the grouping obvously won't work, I'm also not familiar with UNION and think I might be going down the wrong path.

PHP Code:


			
SELECT

  JobID, UserID

FROM Quotes

UNION ALL

SELECT

 JobID, UserID

FROM Questions

WHERE UserID = ?

r937 · #2 (**permalink**) 06-17-10, 10:26

Quote:

Originally Posted by lou73

I'm trying to get a grouped result set of JobID's

not sure what you mean by a "grouped" result

but your UNION query is fine (you should change UNION ALL to UNION, though)

lou73 · #3 (**permalink**) 06-17-10, 10:51

Quote:

Originally Posted by r937

not sure what you mean by a "grouped" result

but your UNION query is fine (you should change UNION ALL to UNION, though)

Thanks for your help.

Basically I only wanted the JobID retuned once whether it's in either table. I was thinking I needed to use the GROUP BY clause or a different approach, but I've got it working 'I think' usining your suggestion and adding in another WHERE clause as follows:-

PHP Code:


			
SELECT 

  JobID, UserID 

FROM Quotes 

WHERE UserID = ?

UNION 

SELECT 

 JobID, UserID 

FROM Questions 

WHERE UserID = ?

r937 · #4 (**permalink**) 06-17-10, 10:56

beauty!!

you fixed an error that i had completely overlooked when i said it was fine

you arrived at the correct solution despite my assistance

Internet Services

Web Development

Digital Marketing

Consumer Tech