Select Query for 3 tables - please. 1

[ISPrincess]

I am having a very hard time with this for whatever reason.

Say I have 3 tables (TABLENAME, FIELDNAME, VALUE)

TABLE 1
Field1
X

TABLE 2
Field1 Field2 Field3
X 1111 A
X 2222 B
Y 3333 C

TABLE 3
Field1 Field2
1111 A *
1111 B
2222 A
2222 B *
3333 C

I only want to return the records from Table3 that have field1 and field2 (combined) = field2 and field3 (combined) from table2 where field1 is = field1 in table1 which in this case is 'X'.

In order to return only the value asterisked from Table 3 - how would I write the query?

(I know this is going to be easy but I think I have been thinking about it too long or something)

 

[sunila7]

Hi,

did u try this query....

select t3.* from table3 t3,table2 t2,table1 t1 where t3.field1=t2.field2
and t3.field3=t2.field3 and t2.field1=t1.field1

Sunil

 

[ISPrincess]

I tried it now with the same results as my original query.

It returns anything where field1 is 1111 and anything where field2 is A, etc.

In other words it returns the following

TABLE 3
Field1 Field2
1111 A *
1111 B
2222 A
2222 B *

I need only records where (field1 is 1111 AND field2 is A - at the same time) - combined and where (field1 is 2222 AND field2 is B at the same time) - combined. Like 1111A and 2222B.

HELP...

 

[BobWman]

This should work:

Select Field1 From Table1 T1
Left Join Table2 T2 On T2.Field1 = T1.Field1
Left Join Table3 T3 On (T3.Field1 = T2.Field2) And (T3.Field2 = T2.Field3)

 

[ISPrincess]

First of all, thank you all.

In response to bob:

I do not need the output from table1 - I need the output from table3.
In your example you are selecting from table1. Can you clarify?

 

[BobWman]

I wrote the query incorrectly:

Select T3.Field1, T3.Field2
From Table1 T1
Left Join Table2 T2 On T2.Field1 = T1.Field1
Left Join Table3 T3 On (T3.Field1 = T2.Field2) And (T3.Field2 = T2.Field3)

There are a variety of ways to combine these tables, depending on what you need. The query above assumes that you want at least one record from every entry in table1, but only include output from table3 if it is contained in table2. You could limit this output with a where clause. For example:

Where T3.Field2 is Not Null

As your question is stated, I believe more than 1 answer would be correct.

Hope this clarifies

 

[BobWman]

Here is something that might be easier:

Select * From Table3
Where Field1+Field2 In
(Select Field2+Field3 From Table2)

 

[tlbroadbent]

Given your requirement, you don't want an outer join. Use an inner join. The IN statement may work but will be less efficient.

Select t3.Field1, t3.Field2
From table3 t3
Join table2 t2
On t3.field1=t2.field2
And t3.field2=t2.field3
Join table1 t1
On t2.field1=t1.field1 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[ISPrincess]

Thank you very much (again).

I have initially tried your suggestions and still seem to be coming up with duplicate records (in test I know I need to return 12 and am returning 38) - so I will need to analyse.

 

[tlbroadbent]

Have you tried adding a DISTINCT clause to the query?

Select DISTINCT t3.Field1, t3.Field2
From table3 t3
Join table2 t2
On t3.field1=t2.field2
And t3.field2=t2.field3
Join table1 t1
On t2.field1=t1.field1 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[just4uin]

let's try following query :

select t3.field1 , t3.field2
from table1 t1 ,table2 t2 ,table3 t3
where t3.field1 = t2.field2
and t3.field2 = t2.field3
and t2.field1 = t1.field1

 

[tlbroadbent]

Just4uin,

You query is equivalent to the query I posted earlier. However, it uses old-style JOIN syntax which Microsoft has said it may not support in future releases of SQL Server. I recommend using ANSI style JOINs. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[ISPrincess]

I thought that adding DISTINCT may have solved the problem but what I am really trying to accomplish is to gather ALL the field data (Select *) from Table3 because this data will then be placed in a flat file for network transfer.

There are many many fields in the 'real' table3 - so I do not think I can use DISTINCT (unless I misunderstand the usage of DISTINCT (which is a possibility))

Thank you for your patience.

 

[ISPrincess]

I thought that adding DISTINCT may have solved the problem but what I am really trying to accomplish is to gather ALL the field data (Select *) from Table3 because this data will then be placed in a flat file for network transfer.

There are many many fields in the real table3 - so I do not think I can use DISTINCT (unless I misunderstand the usage of DISTINCT (which is a possibility))

Thank you for your patience.

 

[tlbroadbent]

Distinct eliminates rows that are duplicated across all columns. I don't know if distinct is the correct answer in your case but if you only need to eliminate rows that are exact duplicates of another row, distinct will do that. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[BobWman]

Try eliminationg the last 2 lines in the query:

Select t3.Field1, t3.Field2
From table3 t3
Join table2 t2
On t3.field1=t2.field2
And t3.field2=t2.field3

 

[diggingaway]

ANSI style joins are the answer. Look em up. you never know.