January 29, 2009 at 9:45 am
Hello ALL,
I have 3 tables and each table has 3 columns. For example,
table 1:
DDS CTCLEARED CTCLEAREDDATE
CT 3 1/2/2009
CT 5 1/5/2009
table 2:
DDS BOCLEARED BOCLEAREDDATE
BO 2 1/3/2009
BO 1 1/5/2009
Table 3:
DDS NHCLEARED NHCLEAREDDATE
NH 4 1/3/2009
NH 5 1/8/2009
Table 4 is the table of the sum of all tables and I called that table is Total. I have joined three tables into one table (In View)
ToTalCleared ClearedDate
3 1/2/2009
6 1/3/2009
6 1/5/2009
5 1/8/2009
Below is my script for the "Total" table
Select dbo.CTCleared.CTCleared, dbo.BOCleared.BOCleared, dbo.NHCleared.NHCleared, dbo.TotalCleared.TotalCleared, dbo.TotalCleared.ClearedDate
From dbo.CTCleared FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.CTClearedDate = dbo.TotalCleared.ClearedDate FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.BOClearedDate = dbo.TotalCleared.ClearedDate
FULL OUTER JOIN dbo.NHCleared on
dbo.NHCleared.NHClearedDate = dbo.TotalCleared.ClearedDate
Then I got the result that I wish to have
CTCleared BOCleared NHCleared TotalCleared ClearedDate
3 0 0 3 1/2/2009
0 2 4 6 1/3/2009
5 1 0 6 1/5/2009
0 0 5 5 1/8/2009
But I forgot to join the DDS column of each table to the DDS of the "Total" table.
Then I added the following codes in order to join DDS column of each table to DDS of Total table:
Select dbo.CTCleared.CTCleared, dbo.BOCleared.BOCleared, dbo.NHCleared.NHCleared, dbo.TotalCleared.TotalCleared, dbo.TotalCleared.ClearedDate, dbo.TotalCleared.DDS
From dbo.CTCleared FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.CTClearedDate = dbo.TotalCleared.ClearedDate FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.BOClearedDate = dbo.TotalCleared.ClearedDate
FULL OUTER JOIN dbo.NHCleared on
dbo.NHCleared.NHClearedDate = dbo.TotalCleared.ClearedDate
AND
dbo.CTCleared FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.DDS = dbo.TotalCleared.DDS FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.DDS = dbo.TotalCleared.DDS
FULL OUTER JOIN dbo.NHCleared on
dbo.NHCleared.DDS = dbo.TotalCleared.DDS
It gives me a syntax error and that error message is: "An expression of non-boolean type specified in a context where a condition is expected, near 'FULL' "
Would you please tell me how to fix this error?
Thank you so much
Joe
January 29, 2009 at 9:52 am
That statement compiles for me (no idea if it's doing what you want). I'm guessing it's a copy and paste error or you're missing something like : "= sometable.somecolumn"
Select dbo.CTCleared.CTCleared, dbo.BOCleared.BOCleared, dbo.NHCleared.NHCleared, dbo.TotalCleared.TotalCleared, dbo.TotalCleared.ClearedDate, dbo.TotalCleared.DDS
From dbo.CTCleared FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.CTClearedDate = dbo.TotalCleared.ClearedDate FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.BOClearedDate = dbo.TotalCleared.ClearedDate
FULL OUTER JOIN dbo.NHCleared on
dbo.NHCleared.NHClearedDate = dbo.TotalCleared.ClearedDate
--AND dbo.CTCleared
FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.DDS = dbo.TotalCleared.DDS FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.DDS = dbo.TotalCleared.DDS
FULL OUTER JOIN dbo.NHCleared on
dbo.NHCleared.DDS = dbo.TotalCleared.DDS
January 29, 2009 at 9:55 am
josephptran2002 (1/29/2009)
From dbo.CTCleared FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.CTClearedDate = dbo.TotalCleared.ClearedDate FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.BOClearedDate = dbo.TotalCleared.ClearedDate
FULL OUTER JOIN dbo.NHCleared on
dbo.NHCleared.NHClearedDate = dbo.TotalCleared.ClearedDate
AND
dbo.CTCleared FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.DDS = dbo.TotalCleared.DDS FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.DDS = dbo.TotalCleared.DDS
FULL OUTER JOIN dbo.NHCleared on
dbo.NHCleared.DDS = dbo.TotalCleared.DDS
Change you query as below
From dbo.CTCleared
FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.CTClearedDate = dbo.TotalCleared.ClearedDate
AND dbo.CTCleared.DDS = dbo.TotalCleared.DDS
FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.BOClearedDate = dbo.TotalCleared.ClearedDate
AND dbo.BOCleared.DDS = dbo.TotalCleared.DDS
FULL OUTER JOIN dbo.NHCleared on dbo.NHCleared.NHClearedDate = dbo.TotalCleared.ClearedDate
AND dbo.NHCleared.DDS = dbo.TotalCleared.DDS
Regards,
Nitin
January 29, 2009 at 10:08 am
I recommend you do a few things:
- Is it really necessary to join 7 tables all using FULL OUTER JOIN? It's really bad database design if that's the case. Most designs have one (or maybe two) rather central table where you link all other tables on.
- Good SQL design is to give an alias to all tables. If you do that, you'll see you have in one join 'AND dbo.CTCleared' left alone. In fact, in this case it's needed, because you join all the tables twice in one query.
Here I'll give an example how the tables could be joined, I left the SELECT clause, because from your query, I have no idea from which table you want the items.FROM dbo.CTCleared CTC
INNER JOIN dbo.TotalCleared TC1 on TC1.CTClearedDate = CTC.ClearedDate
INNER JOIN dbo.BOCleared BOC1 on BOC1.BOClearedDate = CTC.ClearedDate
INNER JOIN dbo.NHCleared NCH1 on NCH1.NHClearedDate = CTC.ClearedDate
INNER JOIN dbo.TotalCleared TC2 on TC2.DDS = CTC.DDS
INNER JOIN dbo.BOCleared BOC2 on BOC2.DDS = CTC.DDS
INNER JOIN dbo.NHCleared NCH2 on BOC2.DDS = CTC.DDSIf all ClearedDate and DDS items exist in all the tables, you can keep this. If they all exist in CTCleared, you make LEFT OUTER JOINs. Other than that, I try to avoid...
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
January 29, 2009 at 10:15 am
I just build a massive report like this.... you knoq the kind where they want to see everything for every part of the business on the same report.
The only way out for me was a full outer join because of depreciated widget codes (no longer in existence anywhere except history of orders).
Basically this is the only way out without full joins :
SELECT dt from dbo.Calendar where dt between @BeginDate and @EndDate
LEFT OUTER JOIN tbl1
LEFT OUTER JOIN tbl2
LEFT OUTER JOIN tbl3
LEFT OUTER JOIN tbl4
LEFT OUTER JOIN tbl5
Where dbo.Calendor holds all the dates for a couple calendar years (mine holds them from 2000 to 2040)...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply