Only show rows where T1_F1 equals T2_F1 and T1_F2 equals T2_F2 and

[Nicholas Kormanik]

Three tables. Many fields in each.

What would be the SQL code for the following.....

Only show rows of T1 where:
(T1_F1 equals T2_F1 and T1_F2 equals T2_F2) and
(T1_F1 equals T3_F1 and T1_F2 equals T3_F2).

Thanks.

 

[Marshall Barton]

Three tables. Many fields in each.

What would be the SQL code for the following.....

Only show rows of T1 where:
(T1_F1 equals T2_F1 and T1_F2 equals T2_F2) and
(T1_F1 equals T3_F1 and T1_F2 equals T3_F2).

Sounds like an inner join of the three tables using the
above conditions:

SELECT t1.*,t2.*,t3.*
FROM (t1
INNER JOIN t2
ON t1.f1 = t2.f1
AND t1.f2 = t2.f2)
INNER JOIN t3
ON t1.f1 = t3.f1
AND t1.f2 = t3.f2

Make sure that you stay within the limit of 255 fields.

 

[Nicholas Kormanik]

Thanks for that reminder about 255 fields. I'll try your approach. Here's
what I came up with on my own in the mean time. Will this get the same
results (but in a more cumbersome way)?

The actual problem at hand is 8 tables, around ten fields in each. I want
four fields in each table to match up with the other tables. If there is a
match across all 8 tables (and each of the four particular fields), then
BINGO.

So, would the approach of inner joins be the preferable streamlined approach?


SELECT c239.*, c240.*, c241.*, c242.*, c243.*, c244.*, c245.*, c246.*

FROM c239, c240, c241, c242, c243, c244, c245, c246

WHERE ((c239!VAR1=c240!VAR1) AND (c239!VAL1=c240!VAL1) AND
(c239!VAR2=c240!VAR2) AND (c239!VAL2=c240!VAL2)
AND (c239!VAR1=c241!VAR1) AND (c239!VAL1=c241!VAL1) AND
(c239!VAR2=c241!VAR2) AND (c239!VAL2=c241!VAL2)
AND (c239!VAR1=c242!VAR1) AND (c239!VAL1=c242!VAL1) AND
(c239!VAR2=c242!VAR2) AND (c239!VAL2=c242!VAL2)
AND (c239!VAR1=c243!VAR1) AND (c239!VAL1=c243!VAL1) AND
(c239!VAR2=c243!VAR2) AND (c239!VAL2=c243!VAL2)
AND (c239!VAR1=c244!VAR1) AND (c239!VAL1=c244!VAL1) AND
(c239!VAR2=c244!VAR2) AND (c239!VAL2=c244!VAL2)
AND (c239!VAR1=c245!VAR1) AND (c239!VAL1=c245!VAL1) AND
(c239!VAR2=c245!VAR2) AND (c239!VAL2=c245!VAL2)
AND (c239!VAR1=c246!VAR1) AND (c239!VAL1=c246!VAL1) AND
(c239!VAR2=c246!VAR2) AND (c239!VAL2=c246!VAL2));

 

[Marshall Barton]

Thanks for that reminder about 255 fields. I'll try your approach. Here's
what I came up with on my own in the mean time. Will this get the same
results (but in a more cumbersome way)?

The actual problem at hand is 8 tables, around ten fields in each. I want
four fields in each table to match up with the other tables. If there is a
match across all 8 tables (and each of the four particular fields), then
BINGO.

So, would the approach of inner joins be the preferable streamlined approach?

SELECT c239.*, c240.*, c241.*, c242.*, c243.*, c244.*, c245.*, c246.*

FROM c239, c240, c241, c242, c243, c244, c245, c246

WHERE ((c239!VAR1=c240!VAR1) AND (c239!VAL1=c240!VAL1) AND
(c239!VAR2=c240!VAR2) AND (c239!VAL2=c240!VAL2)
AND (c239!VAR1=c241!VAR1) AND (c239!VAL1=c241!VAL1) AND
(c239!VAR2=c241!VAR2) AND (c239!VAL2=c241!VAL2)
AND (c239!VAR1=c242!VAR1) AND (c239!VAL1=c242!VAL1) AND
(c239!VAR2=c242!VAR2) AND (c239!VAL2=c242!VAL2)
AND (c239!VAR1=c243!VAR1) AND (c239!VAL1=c243!VAL1) AND
(c239!VAR2=c243!VAR2) AND (c239!VAL2=c243!VAL2)
AND (c239!VAR1=c244!VAR1) AND (c239!VAL1=c244!VAL1) AND
(c239!VAR2=c244!VAR2) AND (c239!VAL2=c244!VAL2)
AND (c239!VAR1=c245!VAR1) AND (c239!VAL1=c245!VAL1) AND
(c239!VAR2=c245!VAR2) AND (c239!VAL2=c245!VAL2)
AND (c239!VAR1=c246!VAR1) AND (c239!VAL1=c246!VAL1) AND
(c239!VAR2=c246!VAR2) AND (c239!VAL2=c246!VAL2));

I believe they are equivalent and one will translated to the
other (probably yours, but I haven't checked in a long time)
by the query optimizer. Try it and check the resulting
datasets and execution times to be sure.

I do recommend that you explicitly specify only the minimum
number of fields instead of using *