Tough select/join - 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 2000 Forums

SQL Server Development (2000)

Tough select/join

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-02 : 12:42:28

I got sort of exited about all the cursors I removed after one of my recent posts here, so I thaught I'd give it another shot allthough I belive this one to be slightly harder.

I have a table-structure like this:

Multi:
---------------------------------------
|MultiID  | UserID  | Price  | Volume |
---------------------------------------
    1     |  11     |   5.1  |  300   |
    2     |  12     |   2    |  500   |
    3     |  13     |   4.7  |  900   |
    4     |  14     |   2.1  |  650   |
---------------------------------------

MultiDetail:
--------------------------------
MultiID  | DescID  | PlayerID  |
--------------------------------
    1    |  15     |   256     |
    1    |  17     |   322     |
    2    |  36     |   956     |
    2    |  58     |   156     |
    2    |  15     |   256     |
    3    |  15     |   256     |
    3    |  17     |   322     |
    4    |  36     |   956     |
    4    |  58     |   156     |
--------------------------------

As you might notice MultiID number 1 and 3 in MultiDetail have the same pair of DescID/PlayerID and this is the key part of my problem. I need in some way to do a select where the only input is MultiID = 1, and then find all other MultiIDs that have the identical MultiDetail-records. It doesn't matter what it says in the table Multi. Also notice that MultiID 2 and 4 is NOT a match even though the first two records are identical.

I'm not even close to knowing where to start here and I have cursors doing this for me now...I'd appreciate any pointers in any direction really... :) And please let me know if anything is unclear.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-02 : 13:10:56

For the record I have thought about denormalizing and adding two varchar-fields to the table Multi which will hold the DescID and PlayerID from the MultiDetail as a commaseparated string, and then just join on those but I thought that would be a really bad design.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-02 : 13:12:45

It's easier to help if you post some DDL and sample data..like:


USE Northwind
GO

CREATE TABLE MultiDetail (MultiID int, DescID int, PlayerID int)
GO

INSERT INTO MultiDetail(MultiID, DescID, PlayerID)
SELECT    1, 15, 256 UNION ALL
SELECT    1, 17, 322 UNION ALL
SELECT    2, 36, 956 UNION ALL
SELECT    2, 58, 156 UNION ALL
SELECT    2, 15, 256 UNION ALL
SELECT    3, 15, 256 UNION ALL
SELECT    3, 17, 322 UNION ALL
SELECT    4, 36, 956 UNION ALL
SELECT    4, 58, 156
GO

SELECT MultiId FROM (
SELECT a.MultiId, a.DescID, a.PlayerID
  FROM MultiDetail a
INNER JOIN (
		SELECT DescID, PlayerID
		  FROM MultiDetail
		 WHERE MultiId = 1
	   ) as b
	ON  a.DescID = b.DescId
       AND a.PlayerId = b.PlayerId
WHERE MultiId <> 1) AS c
GROUP BY MultiId
HAVING COUNT(*) > 1
GO

DROP TABLE MultiDetail
GO

But it screams out to me that it could be mopre than 2 rows....right?

Brett

8-)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-02 : 13:20:05

Yeah thanx alot Brett, I'll do that the next time. And I'm afraid your right on the money...there can be anything from 2 to 20 rows for each MultiID...and also an "unlimited" amount of matches, allthough probably not more than 10 (doesn't really matter does it).

Also if you change the MultiID you search for to 4 you will find a match on 2 even though MultiID 2 has 3 records and 4 only has 2 :(

ChrisFretwell
Starting Member

43 Posts

Posted - 2004-03-02 : 13:33:09

Not overly elegent, but it does what you want it to do

create table #temp1
(multiid int,
descid int,
playerid int)

insert #temp1
values ( 1 , 15 , 256 )
insert #temp1
values ( 1 , 17 , 322 )
insert #temp1
values ( 2 , 36 , 956 )
insert #temp1
values ( 2 , 58 , 156 )
insert #temp1
values ( 2 , 15 , 256 )
insert #temp1
values ( 3 , 15 , 256 )
insert #temp1
values ( 3 , 17 , 322 )
insert #temp1
values ( 4 , 36 , 956 )
insert #temp1
values ( 4 , 58 , 156 )

select distinct m1.multiid as Startingid, m2.multiid as IsDuplicatedin
from #temp1 m1 join
#temp1 m2 on m1.descid = m2.descid and m1.playerid = m2.playerid
join (select multiid, count(*) as m1count from #temp1 group by multiid) as m1tot on m1.multiid = m1tot.multiid
join (select multiid, count(*) as m2count from #temp1 group by multiid) as m2tot on m2.multiid = m2tot.multiid and m1tot.m1count = m2tot.m2count
where m1.multiid <> m2.multiid

Chris

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-02 : 13:34:27

How about:


USE Northwind
GO

CREATE TABLE MultiDetail (MultiID int, DescID int, PlayerID int)
GO

INSERT INTO MultiDetail(MultiID, DescID, PlayerID)
SELECT    1, 15, 256 UNION ALL
SELECT    1, 17, 322 UNION ALL
SELECT    2, 36, 956 UNION ALL
SELECT    2, 58, 156 UNION ALL
SELECT    2, 15, 256 UNION ALL
SELECT    3, 15, 256 UNION ALL
SELECT    3, 17, 322 UNION ALL
SELECT    4, 36, 956 UNION ALL
SELECT    4, 58, 156 UNION ALL
SELECT    5, 36, 956 UNION ALL
SELECT    5, 58, 156
GO

SELECT MultiId FROM MultiDetail WHERE MultiId IN (
SELECT MultiId FROM (
SELECT a.MultiId, a.DescID, a.PlayerID
  FROM MultiDetail a
INNER JOIN (
		SELECT DescID, PlayerID
		  FROM MultiDetail
		 WHERE MultiId = 4
	   ) as b
	ON  a.DescID = b.DescId
       AND a.PlayerId = b.PlayerId
WHERE MultiId <> 4) AS c
GROUP BY MultiId
HAVING COUNT(*) > 1)
GROUP BY MultiId
HAVING COUNT(*) = (SELECT COUNT(*) FROM MultiDetail WHERE MultiId=4)
GO

DROP TABLE MultiDetail
GO

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-02 : 13:41:47

How about this one:



CREATE TABLE MultiDetail (MultiID int, DescID int, PlayerID int)
GO

INSERT INTO MultiDetail(MultiID, DescID, PlayerID)
SELECT    1, 15, 256 UNION ALL
SELECT    1, 17, 322 UNION ALL
SELECT    2, 36, 956 UNION ALL
SELECT    2, 58, 156 UNION ALL
SELECT    2, 15, 256 UNION ALL
SELECT    3, 15, 256 UNION ALL
SELECT    3, 17, 322 UNION ALL
SELECT    4, 36, 956 UNION ALL
SELECT    4, 58, 156
GO


-- this is the MultiID to find matches for:
declare @Multi int;


set @multi = 1;

select b.MultiID
from
  MultiDetail a
inner join 
  MultiDetail b
on
  a.DescID = b.DescID and
  a.PlayerID = b.PlayerID and
  a.MultiID <> b.MultiID
where
  a.MultiID = @Multi
group by b.MultiID
having count(*) = (select count(*) from multidetail where MultiID = @Multi)


drop table MultiDetail
GO

Thanks, Brett for the DDL and DML !!

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-02 : 13:47:53

Jeff try it with the second post with INSERTS...you'll see that yours returns 2 and 5...he would only want 5

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-02 : 13:49:13

And just curious...what about single rows?

Brett

8-)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-02 : 13:54:00

I BOW IN AWE! I am not worthy...both (EDIT: almost all!) your solutions worked perfectly and allthough I'll probably spend the entire evening understanding how the he** they work I'm stunned at the pace you have done this. I have alot to learn...

And Brett, there are no single rows in this table so that is nothing to worry about. Not totally sure which of these I'll use but that choice will be a real treat

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-02 : 14:00:55

Did you cut and paste them in to QA and test them?

That's why we want the DDL and DML so we can figure it out quicker..and then you can test it yourself...

I tested Jeff's and Chris's code (not sure what that is doing though...)

Brett

8-)

ChrisFretwell
Starting Member

43 Posts

Posted - 2004-03-02 : 14:17:04

My code. I'd like to say I'm not sure what it does either, but I built it ground up in this way
find single row matches for the description and player then makes sure that the count of total number of rows for both multi-ids is identical.
I also dont know how it would be on performance on a massive database, but if indexed to match should be okay.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-02 : 14:21:33

I think you need to alter my HAVING clause to:


having count(*) = (select count(*) from multidetail where MultiID = @Multi) and
       count(*) = (select count(*) from multidetail c where c.multiID = b.MultiID)

which I forgot -- this should ensure only complete matches for both ways. thanks to Byrmol for pointing out this technique to me while back, which I had completely forgotten about.

Brett -- i think altering the HAVING clause to be the one above should take care of any issues?

- Jeff

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-02 : 14:28:35

I'll be sure to post the DDL next time I need help like this, promise! I have checked everything, and I will have to go through it all to decide which solution will work. Jeffs solution *looked* to be the smoothest and I was just writing about the HAVING-statement that wasn't 100% that you just pointed out yourself. Works perfectly after the fix :) I will have to investigate execution-plans and such for the most effective solution but I must say that I'm stunned that 3 such different approaches came up in so little time, to a problem that to me was totally impossible.

Thanx alot guys, you have REALLY made this a good week

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-02 : 16:34:32

I have now modified the queries to fit my needs, meaning that I have joined in another table and selected a few extra fields. It all works great but unfortunately I got quite a few extra rows in the select when using Chris' examplet on my test-environment. Seems to be a join missing or something and I really didn't feel qualified to mess all that much with it.

Now when it comes to execution plans I really can't tell which one is better, so I uploaded them for you to see:
http://uk.f2.pg.photos.yahoo.com/ph/henningfrettem/detail?.dir=/7a43&.dnm=6c61.jpg
http://uk.f2.pg.photos.yahoo.com/ph/henningfrettem/detail?.dir=/7a43&.dnm=f9e1.jpg

What do you think?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-02 : 16:37:22

Put both queries in the same Query Analyer window. Hit the execution plan button. What does it say for the query cost for each?

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-02 : 16:56:34

if you have 2 possibilities, and one sometimes doesn't work, i think the execution plan of the one that always works is usually the best regardless of what the analyzer tells you !

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-02 : 16:59:58

Jeff, he was saying that Chris' didn't work. So he wants us to look at the execution plans of yours and Brett's.

Tara

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-02 : 17:07:32

Exactly Tara... sorry for not beeing clear on that ;) When I run them both in the same window I for some reason get Cuery cost (relative to the batch): 0,00% for both but I haven't put them in a procedure or anything...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-02 : 17:27:16

i am confused... do they return the same results?

- Jeff

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-02 : 17:28:25

Yup, exactly the same result...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources