[SOLVED] Multiple Joins not working one Join

[dmacman]

Hi All,

 

I have a multiple join that works for everything but one. I get back the correct info, except the ClassID and Class, both repeat the first records value. I cannot figure out why. Here is my query.

 

SELECT voters.VotersID, ballots.VotersID, voters.EmailAddress,  ballots.BallotID, ratings2.ProductID, ratings2.SKU, classes.ClassID, class.Class, comments.Comments, ratings2.Rating
FROM voters, ballots, classes, comments, ratings2, class
WHERE ballots.VotersID=voters.VotersID
AND  classes.BallotID=ballots.BallotID
AND comments.BallotID=ballots.BallotID
AND ratings2.BallotID=ballots.BallotID
AND classes.ClassID=class.ClassID
GROUP BY voters.VotersID, ratings2.RatingID

 

 

Here is a snapshot of the results.

 

I tried many different approaches and they all give me the same results. As always, I appreciate the help.

 

regards,

Don

[Barand]

When you group by, you get a single row for each group by value. If there are several class names matching the value you are grouping on it can only display one. Unless you include some specific criteria, it will be the first.

[fenway]

When you group by, you get a single row for each group by value. If there are several class names matching the value you are grouping on it can only display one. Unless you include some specific criteria, it will be the first.

Sigh... I wish ONLY_FULL_GROUP_BY was part of the default SQL mode.

[dmacman]

You are correct, I was not aware that it would do that, and now that you explained it, of course it makes sense.

 

I changed it to:

 

SELECT voters.VotersID, ballots.VotersID, voters.EmailAddress,  ballots.BallotID, ratings2.ProductID, ratings2.SKU, classes.ClassID, class.Class, comments.Comments, ratings2.Rating
FROM voters, ballots, classes, comments, ratings2, class
WHERE ballots.VotersID=voters.VotersID
AND  classes.BallotID=ballots.BallotID
AND comments.BallotID=ballots.BallotID
AND ratings2.BallotID=ballots.BallotID
AND classes.ClassID=class.ClassID
ORDER BY SKU, Class

 

And now it is listed just like I wanted.

 

Thanks for the help and the explaination.

 

Regards,

Don

[fenway]

No problem... you might want to consider trying ANSI join syntax, using INNER JOIN and ON clauses to make it more legible and less error-prone, though.

 

SELECT voters.VotersID, ballots.VotersID, voters.EmailAddress,  ballots.BallotID, ratings2.ProductID, ratings2.SKU, classes.ClassID, class.Class, comments.Comments, ratings2.Rating
FROM voters
INNER JOIN ballots ON ( ballots.VotersID=voters.VotersID )
INNER JOIN classes ON (  classes.BallotID=ballots.BallotID )
INNER JOIN comments ON ( comments.BallotID=ballots.BallotID )
INNER JOIN ratings2 ON ( ratings2.BallotID=ballots.BallotID )
INNER JOIN class ON ( classes.ClassID=class.ClassID )
ORDER BY ratings2.SKU, class.Class

 

Also, make sure you use table prefixes -- and table aliases help, too.