Sql Join Help

[logic4fun]

I am planning to join two tables and am in a process to figure out the fastest possible query.
For eg:

Table A:
--------------------------------
Name Time purchased sold

larry 10:00 4 3
jamie 11:00 2 0
chris 11:00 1 1

---------------------------------

Table B
-------------------------------
Name Time offered

wayne 10:00 3
larry 10:00 6
jamie 11:00 5
--------------------------------

I NEED THE RESULT SET AS

Name Time purchased sold offered

wayne 10:00 0 0 3
larry 10:00 4 3 6
jamie 11:00 2 0 5
chris 11:00 1 1 0


It is something like I want combinations if the join satifies i.e. a.name = b.name and a.time = b.time . And also i need individual columns.

Any suggestions

thanks in advance
logic4fun

 

[PHV]

You have to play with outer join and Nz or coalesce function.
BTW in both table it would be better to have an index on (name,time)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[logic4fun]

I tried using a full outer join as follows

select a.name,a.time,purchased,sold,offered
from tableA a
Full outer join
tableB b
ON
a.name = b.name
and a.time = b.time;


when i do this i get the data i need but as i am select a.name and a.time i am not getting the left over name and time in the second table i.e. table B. how to over come.

the result set of above query looks as follows

Name Time purchased sold offered

- - - - 3
larry 10:00 4 3 6
jamie 11:00 2 0 5
chris 11:00 1 1 -


thanks
logic4fun

 

[PHV]

Something like this ?
select
case when isnull(a.name) then b.name else a.name end,
case when isnull(a.time) then b.time else a.time end,
purchased,sold,offered
from tableA a
Full outer join
tableB b
ON
a.name = b.name
and a.time = b.time;


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[logic4fun]

thanks for the query it works fine if i do that way for the join of full table but if i want to exclude some rows based on time its not working.

i tried this
select
case when isnull(a.name) then b.name else a.name end,
case when isnull(a.time) then b.time else a.time end,
purchased,sold,offered
from tableA a
Full outer join
tableB b
ON
a.name = b.name
and a.time = b.time
where a.time between '10:30' and '11:00';

it removes data from one table completely. which i dont want.

Basically i also want the above join to work for a particular time frame.

Any suggestions ..

thanks
logic4fun

 

[rrrkrishnan]

Try this.

select
case when isnull(a.name) then b.name else a.name end,
case when isnull(a.time) then b.time else a.time end,
purchased,sold,offered
from (select * from tableA where time between '10:30' and '11:00') a
Full outer join (select * from tableB where time between '10:30' and '11:00') b
ON
a.name = b.name
and a.time = b.time