PHP / MySQL Query help

[gary00ie]

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

 

[roopurt18]

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.