SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trouble joining two queries that work together.

    I've got 2 queries that work independently and I'm having trouble joining them into one query.

    Code:
    SELECT deals.*,
           (SELECT count(deal_id)
              FROM spam
             WHERE deal_id = deals.deal_id)
              AS spamreports,
           (SELECT count(deal_id)
              FROM liked
             WHERE deal_id = deals.deal_id)
              AS timesliked,
           (SELECT count(deal_id)
              FROM comments
             WHERE deal_id = deals.deal_id)
              AS comments,
           (SELECT count(deal_id)
              FROM expired
             WHERE deal_id = deals.deal_id)
              AS expiredreports,
           members.member_id,
           members.member_name
      FROM deals LEFT OUTER JOIN members
              ON deals.member_id = members.member_id
    HAVING spamreports <= 3 AND deal_isspam = '0'
    and

    Code:
      SELECT deal_id,GROUP_CONCAT(tag)
        FROM tags
    GROUP BY deal_id
    Any ideas? Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
      FROM deals 
    INNER 
      JOIN members
        ON members.member_id = deals.member_id
    INNER
      JOIN ( SELECT deal_id
                  , GROUP_CONCAT(tag) AS tags 
               FROM tags
             GROUP 
                 BY deal_id ) AS t
        ON t.deal_id = deals.deal_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy,

    Something's strange through.

    I've now got
    Code:
    SELECT deals.*,
           (SELECT count(deal_id)
              FROM spam
             WHERE deal_id = deals.deal_id)
              AS spamreports,
           (SELECT count(deal_id)
              FROM liked
             WHERE deal_id = deals.deal_id)
              AS timesliked,
           (SELECT count(deal_id)
              FROM comments
             WHERE deal_id = deals.deal_id)
              AS comments,
           (SELECT count(deal_id)
              FROM expired
             WHERE deal_id = deals.deal_id)
              AS expiredreports,
           members.member_id,
           members.member_name
      FROM deals
           INNER JOIN members
              ON deals.member_id = members.member_id
           INNER JOIN (  SELECT deal_id, GROUP_CONCAT(tag) AS tags
                           FROM tags
                       GROUP BY deal_id) AS t
            ON t.deal_id = deals.deal_id
    the query runs okay without any errors but doesn't return a tags column.

  4. #4
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry rudy, I was being stoopid. I never added tags to the select.

    Code:
    SELECT deals.*,tags,....
    Thanks again, btw I've bought your book.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    thanks, hope you like it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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