List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 3 1999 7:49pm
Subject:Re: Joins on one to many relationships
View as plain text  
Wes Bangerter wrote:
> 
> Hello,
> 
> I'm trying to solve a problem using a join on two tables with a one-to-many
> relationship. With the two tables below, suppose I have one record in
> 'content' and two records in 'data'. All three records would have the same
> contentID. Is is possible to return one record, with the data joined from
> all three records? I imagine that I would need to us AS in order to have it
> return fields with unique names, like
> "SELECT dataContent AS dataName..." This however, uses dataName literally,
> and not as its value. Any Ideas?
> 
> CREATE TABLE content (
>   contentID int(11) UNSIGNED DEFAULT '0' NOT NULL auto_increment,
>   categoryID int(11) UNSIGNED DEFAULT '0',
>   contentTimeStamp int(11) UNSIGNED NOT NULL,
>   PRIMARY KEY (contentID)
> );
> 
> CREATE TABLE data (
>   dataID int(11) UNSIGNED DEFAULT '0' NOT NULL auto_increment,
>   contentID int(11) UNSIGNED DEFAULT '0' NOT NULL,
>   dataName varchar(64),
>   dataContent blob,
>   PRIMARY KEY (dataID)
> );
> 
> Thanks,
> Wes

What yo u search for is:
SELECT
	con.*
	, a.dataName
	, b.dataName
FROM
	content AS con
	, data AS a
	, data AS b
WHERE
	a.contentID=con.contentID
	AND con.contentID=b.contentID

Tschau
Christian

Thread
Joins on one to many relationshipsWes Bangerter3 May
  • Re: Joins on one to many relationshipsChristian Mack3 May