Re: Double Bendback Query

[Jim R]

Jay Pipes wrote:
> I have to say I'm a bit confused as to how these
> tables relate... but that said, the following two
> statements:
>
> SELECT alias_id FROM t2 WHERE t1_id = '230'
> SELECT firstname, lastname FROM t1 LEFT JOIN t2 ON
> t1.id = t2.t1_id WHERE alias_id = ~result from
> previous query~
>
> Can be reduced to:
>
> SELECT firstname, lastname
> FROM t1
> CROSS OIN t2 as t3
> LEFT JOIN t2
> ON t1.id = t2.t1_id
> AND t2.alias_id = t3.alias_id
> WHERE t3.t1_id = '230';
>

Jay,

The relationship is this: In the first table. there can be multiple records for the same person with different presentations of their name, Joe Smith, Joe D. Smith, J. D. Smith, J. Smith and so on. The second table links the record ID of all of the different occurrences of the individual's name to a single alias ID. Using your select allows me to know any of the t1 ID's of Smith and get all of his other records from t1 by looking up his alias id in t2. In any event, your select works like a charm (when substituting t2 for t3 in the WHERE clause). Thanks so much for your help.

Jim