Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Is there a way to JOIN without duplicating the data? 1

Status
Not open for further replies.
bmacbmac

bmacbmac

IS-IT--Management
Jan 26, 2006
392
US
Hello. I have two tables. One table has 3 rows, the other has 6 rows. Is it possible to join these two in a select statement so I only get 6 rows (each item listed one time) instead of 18 rows where each item from Tbl1 ties with each item in TBL2?

For example:
Code: 
create table #party (recordid int, name varchar(200))
insert into #party values(7, 'KEMP MAUDE')
insert into #party values(7, 'KEMP E R')
insert into #party values(7, 'HAYDEN JOHN F')


create table #quarter (recordid int, quarter varchar(200))
insert into #quarter values (7, 'NW')
insert into #quarter values (7, 'SW')
insert into #quarter values (7, 'SE')
insert into #quarter values (7, 'NE')
insert into #quarter values (7, 'SWSW')
insert into #quarter values (7, 'SENW')

If I run:
Code: 
select #party.recordid, name, quarter
from #party
join #quarter on #quarter.recordid = #party.recordid

I get 16 rows of data.

Is it possible to just get 6 rows where everything is represented once?

Like this:
recordid, name, quarter
7, KEMP MAUDE, NW
7, KEMP E R, SW
7, HAYDEN JOHN F, SE
7, NULL, NE
7, NULL, SWSW
7, NULL, SENW
 
Sort by date Sort by votes
I should also mention this is SQL 2000.
 
Upvote 0 Downvote
No. How does it know to match up Kemp Maude with NW? Why isn't Kemp Maude SE? There is nothing in your tables that matches the one value to another. This could be solved with a new column in each table. In the #quarter table you would have a 1 up value for each row, then 'tie' that into the other table. For example:
Code: 
create table #party (recordid int, name varchar(200),quarter int)
insert into #party values(7, 'KEMP MAUDE', 1)
insert into #party values(7, 'KEMP E R', 2)
insert into #party values(7, 'HAYDEN JOHN F', 3)

create table #quarter (quarterid, recordid int, quarter varchar(200))
insert into #quarter values (1, 7, 'NW')
insert into #quarter values (2, 7, 'SW')
insert into #quarter values (3, 7, 'SE')
insert into #quarter values (4, 7, 'NE')
insert into #quarter values (5, 7, 'SWSW')
insert into #quarter values (6, 7, 'SENW')



Then query it with:
Code: 
SELECT p.recordid, p.name, q.quarter
FROM #party p
 RIGHT OUTER JOIN #quarter q
   ON p.recordid = q.recordid
     AND p.quarter = q.quarterid

I haven't tested any of that so you might have to tweak it.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875
 
Upvote 0 Downvote
Thanks SQLBill. I see what you mean about SQL not knowing which quarter to tie the name to.

The script looked to be almost what I needed. Except it left off the recordid from some of the results. It only gave the recordid when there was data in all three fields (recordid, name, quarter). This is the results I received:

Recordid Name Quarter
7 KEMP MAUDE NW
7 KEMP E R SW
7 KEMP JOHN F SE
NULL NULL NE
NULL NULL SWSW
NULL NULL SENW


This is a small sample of my larger data set. I noticed another problem when I added in additional records:

Code: 
create table #party (recordid int, name varchar(200), quarter int)
insert into #party values(7, 'KEMP MAUDE', 1)
insert into #party values(7, 'KEMP E R', 2)
insert into #party values(7, 'KEMP JOHN F', 3)
insert into #party values(8, 'KIMMEL LISA', 4)
insert into #party values(8, 'MATTHEWS QUINCY S', 5)
insert into #party values(9, 'CHATFIELD ALAN', 6)
insert into #party values(9, 'PRAISE JULIE', 7)
insert into #party values(9, 'CRAWFORD ERIN L', 8)
insert into #party values(9, 'THE MONEY STORE', 9)
insert into #party values(9, 'LILAC BRUCE L', 10)


create table #quarter (quarterid int, recordid int, quarter varchar(200))
insert into #quarter values (1, 7, 'NW')
insert into #quarter values (2, 7, 'SW')
insert into #quarter values (3, 7, 'SE')
insert into #quarter values (4, 7, 'NE')
insert into #quarter values (5, 7, 'SWSW')
insert into #quarter values (6, 7, 'SENW')
insert into #quarter values (7, 8, 'SE')
insert into #quarter values (8, 8, 'NW')
insert into #quarter values (9, 8, 'SENW')
insert into #quarter values (10, 8, 'NWNW')
insert into #quarter values (11, 9, 'SE')
insert into #quarter values (12, 9, 'SW')

The results from your script look like this. The names and recordid missing from part of recordid 7, all of 8 and 9.

7 KEMP MAUDE NW
7 KEMP E R SW
7 KEMP JOHN F SE
NULL NULL NE
NULL NULL SWSW
NULL NULL SENW
NULL NULL SE
NULL NULL NW
NULL NULL SENW
NULL NULL NWNW
NULL NULL SE
NULL NULL SW


 
Upvote 0 Downvote
The reason they are not matching as there is no match between RecordID and corresponding Quarter/QuarterID

Code: 
create table #party (recordid int, name varchar(200), quarter int)
insert into #party values(7, 'KEMP MAUDE', 1)
insert into #party values(7, 'KEMP E R', 2)
insert into #party values(7, 'KEMP JOHN F', 3)
insert into #party values(8, 'KIMMEL LISA', 4)
insert into #party values(8, 'MATTHEWS QUINCY S', 5)
insert into #party values(9, 'CHATFIELD ALAN', 6)
insert into #party values(9, 'PRAISE JULIE', 7)
insert into #party values(9, 'CRAWFORD ERIN L', 8)
insert into #party values(9, 'THE MONEY STORE', 9)
insert into #party values(9, 'LILAC BRUCE L', 10)


create table #quarter (quarterid int, recordid int, quarter varchar(200))
insert into #quarter values (1, 7, 'NW')
insert into #quarter values (2, 7, 'SW')
insert into #quarter values (3, 7, 'SE')
insert into #quarter values (4, 7, 'NE')
insert into #quarter values (5, 7, 'SWSW')
insert into #quarter values (6, 7, 'SENW')
insert into #quarter values (7, 8, 'SE')
insert into #quarter values (8, 8, 'NW')
insert into #quarter values (9, 8, 'SENW')
insert into #quarter values (10, 8, 'NWNW')
insert into #quarter values (11, 9, 'SE')
insert into #quarter values (12, 9, 'SW')


SELECT Q.*, P.NAME
FROM #quarter Q LEFT JOIN #party P
ON Q.quarterID = P.quarter AND 
Q.recordid = P.recordid

PluralSight Learning Library
 
Upvote 0 Downvote
yep I see what you mean. I think this may be a lost cause. The new select shows all of the recordids, but no names show for recordid 8 and 9.

I'm going to try putting all of the data into one table and see if I can get anywhere that way.
 
Upvote 0 Downvote
I think this may be a lost cause.
Click to expand...

Try this:

Code: 
create table #party (recordid int, name varchar(200), quarter int)
insert into #party values(7, 'KEMP MAUDE', 1)
insert into #party values(7, 'KEMP E R', 2)
insert into #party values(7, 'KEMP JOHN F', 3)
insert into #party values(8, 'KIMMEL LISA', 4)
insert into #party values(8, 'MATTHEWS QUINCY S', 5)
insert into #party values(9, 'CHATFIELD ALAN', 6)
insert into #party values(9, 'PRAISE JULIE', 7)
insert into #party values(9, 'CRAWFORD ERIN L', 8)
insert into #party values(9, 'THE MONEY STORE', 9)
insert into #party values(9, 'LILAC BRUCE L', 10)


create table #quarter (quarterid int, recordid int, quarter varchar(200))
insert into #quarter values (1, 7, 'NW')
insert into #quarter values (2, 7, 'SW')
insert into #quarter values (3, 7, 'SE')
insert into #quarter values (4, 7, 'NE')
insert into #quarter values (5, 7, 'SWSW')
insert into #quarter values (6, 7, 'SENW')
insert into #quarter values (7, 8, 'SE')
insert into #quarter values (8, 8, 'NW')
insert into #quarter values (9, 8, 'SENW')
insert into #quarter values (10, 8, 'NWNW')
insert into #quarter values (11, 9, 'SE')
insert into #quarter values (12, 9, 'SW')

Select Coalesce(P.RecordId, Q.RecordId) As RecordId,
       P.Name,
       Q.Quarter
From   (
       Select #Party.*, quarter-minQuarter + 1 As RowId
       From   #Party
              Inner Join (
                Select RecordId, Min(quarter) As MinQuarter
                From   #Party 
                Group By RecordId
                ) As A
                On #Party.RecordID = A.RecordId
       ) As P
       Full Join (
         Select  #Quarter.*, quarterid-minquarter + 1 As RowId
         From    #Quarter
                 Inner Join (
                   Select RecordId, Min(QuarterId) As MinQuarter
                   From    #Quarter
                   Group By RecordId
                   ) As A
                   On #Quarter.RecordId = A.RecordId
        ) As Q
        On P.RecordId = Q.RecordID
        And P.RowId = Q.RowId

I'm 99% sure this returns the data you want it to. If not, please let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
YES! That is brilliant! Thank you so much.
 
Upvote 0 Downvote
You're welcome.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Steve Yu
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
331
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
799
Chris Miller
Chris Miller
coorsman
  • Locked
  • Question
Column Data Type. 2
Replies
9
Views
652
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
594
Andrzejek
Andrzejek
Cpreston
  • Locked
  • Question
Seconds to minutes 2
Replies
5
Views
1K
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top