Trouble joining two queries that work together.

[doolally]

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

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

  SELECT deal_id,GROUP_CONCAT(tag)
    FROM tags
GROUP BY deal_id

Any ideas? Thanks

[r937]

  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

[doolally]

Thanks rudy,

Something's strange through.

I've now got

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.

[doolally]

Sorry rudy, I was being stoopid. I never added tags to the select.

SELECT deals.*,tags,....

Thanks again, btw I've bought your book.

[r937]

thanks, hope you like it