[thelist] (SQL) Query with Multi FK's to the same lookup

[Scott Dexter]

Hey Tab--

> I'm trying to think of a way to return a recordset of each Prod_ID
> with
> the Description associated with each rating:
> 
> Prod_ID	OverallRating	FeaturesRating	ValueRating	
> 1		Average		Poor			Excellent
> 2		Good			Good			Good
> 3		Good			Fair
> Excellent
> ...
> 
> 
> 
>  ...and the only way I can think to do it is to JOIN the two tables
> once
> for each rating column, but that seems awfully wasteful.  My anal
> side
> is telling me there ought to be a way to do it with a single JOIN. 
> Is
> there?


So here's your starting point then?

SELECT p.prodid, d.Description AS 'Overall', 
    d1.description AS 'Features', d2.description AS 'Value'
FROM tbProd_Rating p INNER JOIN
    tbRatingDesc d ON OverallRating = d.Rating INNER JOIN
    tbRatingDesc d1 ON FeaturesRating = d1.Rating INNER JOIN
    tbRatingDesc d2 ON ValueRating = d2.Rating

Have you looked at the query plan for this? It may not be that
wasteful under the covers. I'm chewing on it (yeah, it's more
entertaining than the real work I have this afternoon), I'll let you
know if I figure something out ....

Scott

> 
> 
> --
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !