Jump to content

[SOLVED] Trouble with a JOIN query linking two tables by a third


ekestes

Recommended Posts

I have two tables ("changes" and "systems") linked by a third table ("links").  The layouts are something like the following:

 

changes

-----------------------------

id (int, primary key)

text1

 

systems

-----------------------------

id (int, primary key)

text2

 

links

----------------------------

id (int, primary key)

changeid (id from "changes" table)

sysid (id from "systems" table)

 

 

So a change can be linked to a single system or multiple systems by entries in the "links" table.

 

Right now I'm having trouble with a query.  What I need is a single query for text1 (from "changes") and text2 (from "systems") for all of the changes.  But this needs to be for unique changes only.  In other words, I don't want the same change showing up 100 times in my results, just because it was linked to 100 systems.  So I need to query the data for *unique* changes only.  All of the queries I'm trying are resulting in entries for every single link.  Here is my current query:

SELECT DISTINCT text1,text2 FROM changes LEFT JOIN links ON changes.id = links.changeid LEFT JOIN systems ON links.sysid = systems.id;

 

Any help would be greatly appreciated.  I'm at the point of wasting a ridiculous amount of time on this.

 

thanks

 

Link to comment
Share on other sites

Back for more eh?  :)

 

To accomplish this, you will need to first query the changes table, and loop over those entries. Then, inside that loop, query for the systems...like so:

 

<?php

  $changes = mysql_query("SELECT * FROM changes");
  while($change = mysql_fetch_assoc($changes)){
    //Get systems
    $systems = mysql_query("SELECT * FROM links L INNER JOIN systems S ON L.sysid = S.id WHERE L.changeid = '{$change['id']}'");
    while($system = mysql_fetch_assoc($systems)){
      //Loop for each system for that change
    }
  }
?>

Link to comment
Share on other sites

8)  Yeah, I thought it was turning into more of an SQL question, so I decided to phrase it a bit differently and post here.

 

I was hoping there was a way I could do a JOIN and get 1 result set with unique changes.  The reason is that I'm passing the $result to a display function that accepts a single result set.  But if there's no way to do it, I guess I'll rework the PHP instead.

 

thanks for all your help!

Link to comment
Share on other sites

To accomplish this, you will need to first query the changes table, and loop over those entries. Then, inside that loop, query for the systems...like so:

Otherwise known as a join.

 

SELECT changes.text1,systems.text2 FROM changes LEFT JOIN links ON changes.id = links.changeid LEFT JOIN systems ON links.sysid = systems.id GROUP BY changes.id;

Link to comment
Share on other sites

fenway, I bow to thee

 

This is exactly what I needed.  I was just missing the "GROUP BY"

 

This returns a complete set of results, one for each change, and I have all of the data I need (there are actually alot more fields than text1 and text2 - I just put those there to simplify the question)

 

THANK YOU

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.