From: Christian Mack
Date: March 24 1999 5:00pm
Subject: Re: SELECT on a one to many relationship
List-Archive: http://lists.mysql.com/mysql/905
Message-Id: <36F91A48.9FB462AD@compal.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Cyril Godefroy wrote:
> 
> Please excuse me if this is a bit off-topic, and point me to the right list.
> 
> I'm using MySQL on a web site I'm creating, in conjuncyion with PHP. I love
> it. But it's my very first taste of SQL.
> 
> One of the areas of the site is a query on ISP in France, and this is where
> I have a problem. I created my tables like that:
> Provider (ID, Name, Adress, Web, etc)
> Package (ID, ProviderID, Price, MonthlyTransfer, OS,...)
> Feature (ID, Name, Description)
> FeatureToPackage (FeatureID, PackageID)
> 
> This way, I know which providers use PHP as a feature, or give Database
> Access, or Email aliases, etc. Unfortunately, despite my reading the
> documentation, tutorials, I'm still unable to find which providers use PHP
> AND databases AND SSL (for example).
> 
> Got any tips or URLs to a good recipe? Is there a design error?
> 
> Thanks in advance
> ==================================
> Cyril Godefroy

Hi Cyril

The design is O.K. but the query is a little bit tricky.
Try:
SELECT DISTINCT
	Provider.*
FROM
	Provider
	, Package
	, FeatureToPackage AS ftp1
	, Feature AS f1
	, FeatureToPackage AS ftp2
	, Feature AS f2
	, FeatureToPackage AS ftp3
	, Feature AS f3
WHERE
	Provider.ID=Package.ProviderID
	AND Package.ID=ftp1.PackageID
	AND ftp1.FeatureID=f1.ID
	AND f1.Name='PHP'
	AND Package.ID=ftp2.PackageID
	AND ftp2.FeatureID=f2.ID
	AND f2.Name='databases'
	AND Package.ID=ftp3.PackageID
	AND ftp3.FeatureID=f3.ID
	AND f3.Name='SSL'

Tschau
Christian