special scenario join

Question

special scenario join

ivann

SSC Rookie

Points: 47
More actions
June 23, 2010 at 3:34 pm

#234238

Hi there,
I've got this scenario that I hope somebody would be able to give me a useful suggestion.
It is easy to figure out that the result of the query below will be 4 X 2 of 'two'.
create table #tt (theName nvarchar(10))
insert into #tt values ('one')
insert into #tt values ('two')
insert into #tt values ('two')
create table #tt1 (theName nvarchar(10))
insert into #tt1 values ('two')
insert into #tt1 values ('two')
select *
from #tt join #tt1
on #tt.theName = #tt1.theName
I desperately need to have two results:
first
theNametheName
oneNULL
second
theNametheName
twotwo
twotwo
If first result is rather easy to achieve then the second is a big headache. As you can see it is not a distinct, but mere recognition that both tables have two of the same values.
If one of the values from table #tt1 is deleted the the 2 results should be as follows:
first
theNametheName
oneNULL
twoNULL
second
theNametheName
twotwo
Any suggestions would be greatly appreciated.
Iva77

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

It would probably be better if you had a more real-word scenario (and please see the instructions in the link below) so we can understand what exactly you're trying to do.

And specifically, are you trying to return two separate result sets each time you query? If so, is this on a static or dynamic basis that you're splitting the results into two sets?

Lastly, even if you delete one of the records in the second table, both "Two" records in the first table can be joined on the remaining "Two" record in the second table. Now, there are ways to query that will get around this if you want to limit the one record to only joining on one record in the other table but I would want to understand better what you're trying to do.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

ivann SSC Rookie Points: 47 More actions · Answer 2

Thanks a lot for a quick reply. I apologize for my clumsiness in expression (English is not my first language) but you understood me very well :). I want to limit the one record to only joining on one record in the other table.

First of all, yes I am trying to return 2 result sets (each goes into a separate web control). Each is mutually exclusive, so once I get one result set the other would be easy to flip.

I have a table with dates and quantities. There can be more than one with the same date and same quantity. I need to find dates/quantities that have pairs in the other table and those that don’t have should go into second record set.

As in the example below, I have 2 records with '6/23/2010',2 in one table and just one in the other, so only one record should be joint.

create table #tt (theDate nvarchar(10), quantity int)

insert into #tt values ('6/22/2010',2)

insert into #tt values ('6/23/2010',2)

create table #tt1 (theDate nvarchar(10), quantity int)

insert into #tt1 values ('6/23/2010',2)

select *

from #tt left join #tt1

on #tt.theDate = #tt1.theDate

and #tt.quantity = #tt1.quantity

where #tt1.theDate is null

select *

from #tt join #tt1

on #tt.theDate = #tt1.theDate

and #tt.quantity = #tt1.quantity

Would return this:

First

theDatequantitytheDatequantity

6/22/20102NULLNULL

Second

theDatequantitytheDatequantity

6/23/201026/23/20102

And I need it to return this:

First

theDatequantitytheDatequantity

6/22/20102NULLNULL

6/23/20102 NULLNULL

Second

theDatequantitytheDatequantity

6/23/201026/23/20102

It is like finding matching socks, when there are more than one set of same socks. If I have 3 white socks I got just 1 pair + 1 sock, if I got 4 socks – I got 2 pairs.

I hope I am clearer this time around.

Iva

WayneS SSC Guru Points: 95461 More actions · Answer 3

How's this?

select *

from #tt

left join #tt1

on #tt.theDate = #tt1.theDate

and #tt.quantity = #tt1.quantity

where #tt1.theDate is null

UNION

-- UNION (vs. UNION ALL) gets rid of the duplicates.

select tt1.*, NULL, NULL

from #tt1 tt1

LEFT JOIN #tt

on #tt.theDate = tt1.theDate

and #tt.quantity = tt1.quantity

select distinct *

from #tt

join #tt1

on #tt.theDate = #tt1.theDate

and #tt.quantity = #tt1.quantity

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ben Teraberry SSChampion Points: 11845 More actions · Answer 4

As a side note ... don't use (n)char/varchar for date fields ... use a datetime datatype and save yourself a lot of trouble.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

ivann SSC Rookie Points: 47 More actions · Answer 5

Hi Wayne,

Thanks a lot. Almost there :-D.

your join produces same result if the second table has two records for 6/23/2010

create table #tt (theDate datetime, quantity int)

insert into #tt values ('6/22/2010',2)

insert into #tt values ('6/23/2010',2)

create table #tt1 (theDate datetime, quantity int)

insert into #tt1 values ('6/23/2010',2)

but in this case I need this :

first resultset:

theDatequantitytheDatequantity

2010-06-22 00:00:00.0002NULLNULL

second resultset:

theDatequantitytheDatequantity

2010-06-23 00:00:00.00022010-06-23 00:00:00.0002

In this case the other table has matching socks for both white pairs, a pair for yellow sock (6/22/2010) is still missing. (I hope you don't mind my analogy :-))

I hope this is possible.

Iva

ivann SSC Rookie Points: 47 More actions · Answer 6

Thanks Bteraberry, very useful hint. :w00t: I am learning

Ben Teraberry SSChampion Points: 11845 More actions · Answer 7

There may be an easier way, but this should work:

declare @temp table (theDate datetime, quantity int, theDate1 datetime, quantity1 int);

with cteTable (rowNum, theDate, quantity)

as

(

select ROW_NUMBER() OVER(PARTITION BY theDate ORDER BY theDate),

theDate,

quantity

from #tt

),

cteTableOne (rowNum, theDate, quantity)

as

(

select ROW_NUMBER() OVER(PARTITION BY theDate ORDER BY theDate),

theDate,

quantity

from #tt1

)

insert into @temp

select ct.theDate,

ct.quantity,

ct1.theDate,

ct1.quantity

from cteTable ct

left outer join cteTableOne ct1

on ct1.theDate = ct.theDate

and ct1.rowNum = ct.rowNum

select theDate, quantity, theDate1, quantity1

from @temp

where theDate1 is null

select theDate, quantity, theDate1, quantity1

from @temp

where theDate1 is not null

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

ivann SSC Rookie Points: 47 More actions · Answer 8

this is totally groovy :w00t::smooooth:

where can I rate it THE best answer ever?

thank you so much!!!! This is what I need!!! just got back from vacations and what a pleasant surprise 😎