Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Join syntax showing unusual results

Posted on 2008-06-20
7
183 Views
Last Modified: 2011-10-03
Following on from a question I asked yesterday - (http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23499412.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
0
Comment
Question by:Maxafi
  • 3
  • 2
7 Comments
 
LVL 9

Expert Comment

by:Tone' Shelby
ID: 21852060
I know you said it's not the second row etc, but is any of the data in any of the fields for ether result even slightly different morespecifically in the c.offerid field. Even one difference in a character  in the data could pull back 2 results icluding a blank space ..You may try Trimmiming the c.offerid just to be sure. I've had trailing invisible spaces cause me grief like this before..I'd check data differences and leading / trailing blank spaces in the data first.
Hope it helps..
0
 

Author Comment

by:Maxafi
ID: 21868891
Thanks for the input, tshel.

Unfortunately, trimming hasn't helped - same result.
0
 
LVL 9

Expert Comment

by:Tone' Shelby
ID: 21879999
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...
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 9

Expert Comment

by:Tone' Shelby
ID: 22196263
Hello...checking in on you. How are you coming along. Do you need further assistance / information  on this...thanks!
0
 

Author Comment

by:Maxafi
ID: 22348445
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.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 22793294
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 26 68
What's wrong with this T-SQL Foreign Key? 7 51
Currency in SQL? 2 30
SQL - format decimal in a string 5 38
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question