Maxafi
asked on
Join syntax showing unusual results
Following on from a question I asked yesterday - (https://www.experts-exchange.com/questions/23499412/Add-an-additional-Join-to-an-existing-query.html) I now have a successful query as follows;
SELECT m.ID, m.shortname, m.retailer_name, o.merchantid, o.id AS oid, o.offertitle, o.offerbody, o.a_status, o.offerauthor, o.offershortname, o.clickcount, u.id, u.username
FROM merchants m
LEFT JOIN hurry_offers o
ON m.id=o.merchantid
LEFT JOIN hurry_users u
ON o.offerauthor=u.ID
ORDER BY oid DESC
I would now like to add a further join in order to get the number of comments against each 'offer'. The query I currently have is;
SELECT m.ID, m.shortname, m.retailer_name, o.merchantid, o.id AS oid, o.offertitle, o.offerbody, o.a_status, o.offerauthor, o.offershortname, o.clickcount, u.id, u.username, count(c.offer_id) AS c_cnt, c.offer_id
FROM merchants m
LEFT JOIN hurry_offers o
ON m.id=o.merchantid
LEFT JOIN hurry_users u
ON o.offerauthor=u.ID
LEFT JOIN hurry_comments c
ON o.id=c.offer_id
GROUP BY c.offer_id
ORDER BY o.id DESC
This works in as much as it is returning the correct figures for each element (i.e the correct username, comment count etc) however I'm only getting 2 results returned at the moment. Unusually, there doesn't seem to be any reason that I can see for the 2nd of those results being returned - for example, it's not the 2nd row, it doesn't have any comments etc.
Having been working on this for some time, I'm hoping it's just something simple I've missed!
Any help would be much appreciated
SELECT m.ID, m.shortname, m.retailer_name, o.merchantid, o.id AS oid, o.offertitle, o.offerbody, o.a_status, o.offerauthor, o.offershortname, o.clickcount, u.id, u.username
FROM merchants m
LEFT JOIN hurry_offers o
ON m.id=o.merchantid
LEFT JOIN hurry_users u
ON o.offerauthor=u.ID
ORDER BY oid DESC
I would now like to add a further join in order to get the number of comments against each 'offer'. The query I currently have is;
SELECT m.ID, m.shortname, m.retailer_name, o.merchantid, o.id AS oid, o.offertitle, o.offerbody, o.a_status, o.offerauthor, o.offershortname, o.clickcount, u.id, u.username, count(c.offer_id) AS c_cnt, c.offer_id
FROM merchants m
LEFT JOIN hurry_offers o
ON m.id=o.merchantid
LEFT JOIN hurry_users u
ON o.offerauthor=u.ID
LEFT JOIN hurry_comments c
ON o.id=c.offer_id
GROUP BY c.offer_id
ORDER BY o.id DESC
This works in as much as it is returning the correct figures for each element (i.e the correct username, comment count etc) however I'm only getting 2 results returned at the moment. Unusually, there doesn't seem to be any reason that I can see for the 2nd of those results being returned - for example, it's not the 2nd row, it doesn't have any comments etc.
Having been working on this for some time, I'm hoping it's just something simple I've missed!
Any help would be much appreciated
ASKER
Thanks for the input, tshel.
Unfortunately, trimming hasn't helped - same result.
Unfortunately, trimming hasn't helped - same result.
Can you provide a Create Table statement with some sample data with the two records of data and I'll be happy to dig in and see what I can come up with...
Hello...checking in on you. How are you coming along. Do you need further assistance / information on this...thanks!
ASKER
Hi tshel,
Apologies for not replying sooner - I must have missed the email notifications.
I did eventually solve this by doing a subquery to get the comment count, which did the trick.
Apologies for not replying sooner - I must have missed the email notifications.
I did eventually solve this by doing a subquery to get the comment count, which did the trick.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hope it helps..