join query - 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)

join query

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-28 : 05:26:27

Hi,
There are two table variables with the following fields:

@tblBestBidsForToday
Security_ID source_code Best_Bid Import_Date

@tblBestAsksForToday
Security_ID source_code Best_Ask Import_Date

Now I have the following query to get the data from both tables.

select
b.Security_ID,
b.Source_Code,
b.Best_Bid,
a.Best_Ask,
b.Import_Date
from
@tblBestBidsForToday b
left join @tblBestAsksForToday a on b.Security_ID = a.Security_ID
and b.Source_Code = a.Source_Code
and b.Import_Date = a.Import_Date

I would like to get all the data from both tables.
But it seems that this query does not fully work.
Because there may be records in the first table and not in the second or vice versa.

There may be a source_Code with an import_Date in the first table, this source_code can be present in the second table but not with all the same Import_Dates.

@tblBestBidsForToday
Security_ID source_code Best_Bid Import_Date
125 TR73 112.6250 2007-11-28 07:23:00
125 TTFN03 112.8750 2007-11-28 07:25:00
125 TTFN03 112.8750 2007-11-28 07:27:00
125 TTFN03 112.8750 2007-11-28 07:29:00
125 GFI01 112.9370 2007-11-28 07:31:00
125 GFI01 112.9370 2007-11-28 07:33:00
...
...

@tblBestAsksForToday
Security_ID source_code Best_Bid Import_Date
125 GFI01 113.1250 2007-11-28 07:29:00
125 GFI01 113.0620 2007-11-28 07:31:00
125 GFI01 113.0620 2007-11-28 07:33:00
125 GFI01 113.0620 2007-11-28 07:35:00
125 GFI01 113.0620 2007-11-28 07:39:00
125 GFI01 113.0620 2007-11-28 07:41:00
...
...

CURRENT RESULT (which is not quite correct):
Note, in @tblBestAsksForToday, this record does not appear in the RESULT
125 GFI01 113.1250 2007-11-28 07:29:00

RESULT
125 TR73 112.6250 NULL 2007-11-28 07:23:00
125 TTFN03 112.8750 NULL 2007-11-28 07:25:00
125 TTFN03 112.8750 NULL 2007-11-28 07:27:00
125 TTFN03 112.8750 NULL 2007-11-28 07:29:00
125 GFI01 112.9370 113.0620 2007-11-28 07:31:00
125 GFI01 112.9370 113.0620 2007-11-28 07:33:00

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-28 : 05:27:27

instead of left join you can use a full outer join

Em

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-28 : 05:31:05

This still does not give the correct data.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-28 : 05:34:08

that's because you're not selecting the a.security_id etc.

Em

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-28 : 05:37:17

Do not follow you

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-28 : 05:47:09

do you mean like this....


declare @tblBestBidsForToday table ( 
Security_ID int,source_code varchar(20), Best_Bid float, Import_Date datetime)

declare @tblBestAsksForToday table ( 
Security_ID int, source_code varchar(20), Best_Ask float, Import_Date datetime)

insert into @tblBestBidsForToday
select 125, 'TR73', 112.6250, '2007-11-28 07:23:00' union all
select 125, 'TTFN03', 112.8750, '2007-11-28 07:25:00' union all
select 125, 'TTFN03', 112.8750, '2007-11-28 07:27:00' union all
select 125, 'TTFN03', 112.8750, '2007-11-28 07:29:00' union all
select 125, 'GFI01', 112.9370, '2007-11-28 07:31:00' union all
select 125, 'GFI01', 112.9370, '2007-11-28 07:33:00'

insert into @tblBestAsksForToday
select 125, 'GFI01', 113.1250, '2007-11-28 07:29:00' union all
select 125, 'GFI01', 113.0620, '2007-11-28 07:31:00' union all
select 125, 'GFI01', 113.0620, '2007-11-28 07:33:00' union all
select 125, 'GFI01', 113.0620, '2007-11-28 07:35:00' union all
select 125, 'GFI01', 113.0620, '2007-11-28 07:39:00' union all
select 125, 'GFI01', 113.0620, '2007-11-28 07:41:00'


select
coalesce(b.Security_ID,a.security_id),
coalesce(b.Source_Code,a.source_code),
b.Best_Bid,
a.Best_Ask,
coalesce(b.Import_Date,a.import_date)
from 
@tblBestBidsForToday b
full join @tblBestAsksForToday a on b.Security_ID = a.Security_ID
and b.Source_Code = a.Source_Code
and b.Import_Date = a.Import_Date

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-28 : 05:53:53

Still the same record I mentioned is missing.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-28 : 05:56:39

really...?


125	TR73	112.625	NULL	2007-11-28 07:23:00.000
125	TTFN03	112.875	NULL	2007-11-28 07:25:00.000
125	TTFN03	112.875	NULL	2007-11-28 07:27:00.000
125	TTFN03	112.875	NULL	2007-11-28 07:29:00.000
125	GFI01	112.937	113.062	2007-11-28 07:31:00.000
125	GFI01	112.937	113.062	2007-11-28 07:33:00.000
125	GFI01	NULL	113.125	2007-11-28 07:29:00.000 
125	GFI01	NULL	113.062	2007-11-28 07:35:00.000
125	GFI01	NULL	113.062	2007-11-28 07:39:00.000
125	GFI01	NULL	113.062	2007-11-28 07:41:00.000