Querying Bringing About Multiplicity

[gladiator83x]

Hi All,

I am querying 4 specific things at the same time that all relate to one another and I need at least 5 tables in order to gather the correct material. I was successful at doing this; however, most of the the time the query that I am using brings back the same thing multiple times. For example:

This is my query:
[color=red]$query = "SELECT topicfile.filename,commentstate.fileline, commentdata.commentfield,
commentstatehistory.metric_value    FROM
commentstate, commentdata, topic, topicfile, commentstatehistory  WHERE
commentstate.id=commentdata.commentstateid
AND commentstate.topicid = '$topic_id' AND topic.id = '$topic_id' AND
commentstate.filenumber=topicfile.sequence AND
commentstate.topicid=topicfile.topicid AND commentstatehistory.id=commentstate.id";[/color]
And this is my output: (I am only trying to see this once, not four times)

[color=red]Filename                                                                                      Line #      Description    Defect Type
6.x-nightly/vobs/checkmc/cmc_api/system/checkmc/include/checkmc.h  61  Review  Complete    Submitted
6.x-nightly/vobs/checkmc/cmc_api/system/checkmc/include/checkmc.h  61  Review  Complete    Submitted
6.x-nightly/vobs/checkmc/cmc_api/system/checkmc/include/checkmc.h  61  Review  Complete    Submitted
6.x-nightly/vobs/checkmc/cmc_api/system/checkmc/include/checkmc.h  61  Review  Complete    Submitted [/color]

I just don't know why it does that. Anyone ever run into this problem before? Or know how to fix it? My only guess is the way that I matched the variables after the WHERE in my query.

[ryanlwh]

you didn't join commentstate and topic together
[code]commentstate.topicid = '$topic_id' AND topic.id = '$topic_id'[/code]
should be
[code]commentstate.topicid = topic.id AND topic.id = '$topic_id'[/code]

[craygo]

Also should make sure you don't have a one to many relationship with one of the tables. If ANY of the id's appear more than once in ANY table, you will get multiple lines. Just because the fields you are pulling out are the same does not mean other fields have different data.