Re: [LIP] An SQL question about using multiple tables

Sandip Bhattacharya wrote:
> [Crossposted. Sorry.]
> 
> Background:
> I have one master table A, and other supplementary tables B,C and D such that   
> for every row of A there can be one or more corresponding rows in B,C,D. 
> There is another supplementary table E with which A has a one-to-one 
> relationship.
> 
> Problem:
> Given three search criteria resulting in AB, AC, and AD respectively, I need 
> to display results so that I get ( AB intersection AC intersection AD) and I 
> need to display unique rows of A on teh screen joined with corresponding row 
> of E. A typical multiple parameter search operation in any database with 
> normalized tables.
> 
> Constraints:
> Am using (sigh) mysql 3.23. No subqueries, no INTERSECT.


If the AB AC and AD criteria are an OR we're talking LEFT JOINs, 
otherwise inner joins will do. the rest seems pretty straightforward. 
intersection meaning where they all match.  that's what inner joins do...

for example,
SELECT ... FROM a,b,c,d,e WHERE a.id=b.id AND a.id=c.id AND a.id=d.id 
AND a.id=e.id and then you can do a DISTINCT (a.id) or whatever

you can call it inner join instead of , and use the ON clause, that 
makes it more readable. effect is the same though.

Cheers!
Pankaj
--
( 2b || ! 2b )