Jump to content

Archived

This topic is now archived and is closed to further replies.

gladiator83x

Querying Bringing About Multiplicity

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.