Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    2

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

    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 
    = ? 

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by lou73 View Post
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2010
    Posts
    2
    Quote Originally Posted by r937 View Post
    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 
    = ? 

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •