ekestes Posted January 25, 2008 Share Posted January 25, 2008 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 Quote Link to comment Share on other sites More sharing options...
rhodesa Posted January 25, 2008 Share Posted January 25, 2008 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 } } ?> Quote Link to comment Share on other sites More sharing options...
ekestes Posted January 25, 2008 Author Share Posted January 25, 2008 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! Quote Link to comment Share on other sites More sharing options...
fenway Posted January 25, 2008 Share Posted January 25, 2008 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; Quote Link to comment Share on other sites More sharing options...
rhodesa Posted January 25, 2008 Share Posted January 25, 2008 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? Quote Link to comment Share on other sites More sharing options...
ekestes Posted January 25, 2008 Author Share Posted January 25, 2008 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.