gary00ie Posted February 20, 2008 Share Posted February 20, 2008 Hi, I have two tables with foreign keys i.e. $create = "CREATE TABLE IntTable ( IntId bigint(20) NOT NULL auto_increment, IntTypeId bigint(20) NOT NULL, ... year varchar(6) NOT NULL default '', PRIMARY KEY ( IntId ), FOREIGN KEY ( IntTypeId ) REFERENCES IntType ( IntTypeId ) ); "; and $create = "CREATE TABLE IntSource ( SourceId bigint(20) NOT NULL auto_increment, IntId bigint(20) NOT NULL, ... PRIMARY KEY ( SourceId ), FOREIGN KEY ( IntId ) REFERENCES IntTable ( IntId ), ); "; On one page I'm trying to display a list of interviews with the following query: $query = "SELECT DISTINCT IntTable.*, IntType.*, IntSource.* FROM IntTable "; $query .= "LEFT JOIN IntType ON IntTable.IntTypeId = IntType.IntTypeId "; $query .= "LEFT JOIN IntSource ON IntTable.IntId = IntSource.IntId "; $query .= "WHERE ( IntTable.IntTypeId = ".$id." ) ORDER BY IntTable.DateConducted;"; However, because there are multiple "Sources" for each interview, some interviews are being listed twice in the list. On this page, I need data from each table, but just need each interview to be listed once. Is there a way to fix my query to get the results I need? Thanks in advance for any help Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted February 20, 2008 Share Posted February 20, 2008 You might try using a GROUP BY clause in conjunction with MySQL's GROUP_CONCAT() function. So if an interview has the sources: Bob Jane Joe Instead of 3 rows of the same interview, you could coerce the source column to be a comma delimited list: Bob, Jane, Joe Another option if using MySQL 4.1 or above is using a sub-query. Instead of LEFT JOINing on another table, you can run a subquery and alias it to be used as another table. Within the sub-query you could use DISTINCT or some other DB functionality to cause an article to only appear once. SELECT i.*, s.* FROM `interviews`i LEFT JOIN ( SELECT DISTINCT `interview_id`, `fld1`, `fld2` FROM `sources` WHERE ... ) AS s ON s.`interview_id`=i.`id` I don't have the time or the patience at the moment to give you something tailored to your situation, but those are two database features you may not be aware of that might help you. If you get stuck post again and I might have a little more time later, or someone else might be able to give you something tailored to your exact tables. (edit) The fastest solution might be to just tack a GROUP BY `IntTable`.`IntId` onto your existing query. 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.