OUTER JOIN WITH THREE TABLES - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

OUTER JOIN WITH THREE TABLES

Author

Topic

annamaria
Starting Member

30 Posts

Posted - 2007-04-22 : 11:46:24

I need help for this query
I must select from three tables.
I want to select ALL THE RECORDS from table1.
I want to select some fields from table2 (which doesn't have values for all the records of table1): I want to select field2.1 and field2.2 from table2 only if (field2.1 < chosen date and field2.2 > chosen date). Where shall I write this selection?;
Now I have all records of table1 with some records bearing data of table2.
Then I have table3 (it doesn't have values for every record of table1) and I want to outer join also this one.
For example:
Table1
field1 field2 field3
sec.1 500 USD
sec.2 300 EUR
sec.3 400 GBP
Table2
field1a field2a field 2b
sec.2 20070210 20070410
sec.3 20070415 20071015
Table3
fieldA fieldB
USD 1,36
GBP 0.678
YEN 161,593
DESIRED RESULT:
field1 field2 field3 field2a field2b fieldBA
sec.1...500.....USD......---.......---........1,36
sec.2...300.....EUR...20070210...20070410.....---
sec.3...400.....GBP...20070415...20071015.....0,678

Or maybe instead of the first left outer join I could write:
select
field1, ......,
case when (field2a < chosen date and field 2b > chosen date) as field2a and field2b (?????)
from table1, table2 left outer join table3 (excuse me if I dare...)

Another question: are there problems with left outer joins if I have nested tables inside the query?

Anna - Italy (Verona)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-22 : 12:10:00

Here is what you were asking for with the exception of

"(field2.1 < chosen date and field2.2 > chosen date)"

That did not make sense becuase no date could be both lower then the start date and higher then the stop date using the sample data you gave.

I think you meant that the date you specify will fall inbetween those two dates, so that's how I wrote the query.


DECLARE @DatetoCheck datetime
set @DatetoCheck = '03/10/2007'

Select		a.field1,a.Field2,a.Field3,b.Field2a,b.field2b,c.Fieldb 
from		table1 a 
left join	table2 b on 
		a.field1	=	b.field1a 
left join	Table3 c on
		a.field3	=	c.FieldA
where		b.field2a	is	null
or		@DateToCheck	
between		b.field2a 
and		b.field2b

annamaria
Starting Member

30 Posts

Posted - 2007-04-22 : 13:22:43

DECLARE @DatetoCheck datetime
set @DatetoCheck = '03/10/2007'
Select a.field1,a.Field2,a.Field3,b.Field2a,b.field2b,c.Fieldb
from table1 a
left join table2 b on
a.field1 = b.field1a
left join Table3 c on
a.field3 = c.FieldA
where b.field2a is nullor @DateToCheck
between b.field2a
and b.field2b
[/code]
[/quote]

Thank you for you answer. I don't think the line in red could work,
because table2 doesn't have a record (related to that item) with field2a = null. It doesn't have the record at all.
What could we do?
2nd question: are there problems if I use nested tables in my queries?
Thank you
Anna - Verona (Italy)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-22 : 13:33:58

What that statment says is

Table 2 either has no related field OR If it does then make sure the DateTocheck is in between the two records. The query should be accurate unless that is not what you wanted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources