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

[ekestes]

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

 

[rhodesa]

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
    }
  }
?>

[ekestes]

  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!

[fenway]

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;

[rhodesa]

I'm no SQL expert, but wouldn't that return one record for each entry in 'changes', but only join it with one matching entry from systems?

[ekestes]

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