Tough select/join - Part Deux - 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 - Part Deux

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-04 : 08:47:44

Allright, solution to "Tough select/join" is now well placed within the procedures they need to, but now I have a slight abbrevation that I can't figure out (and I feel totally stupid because I belive it should be quite easy given the solution I allready have). There is an additional table "Single" that I need to join in a certain way.

Single:
--------------------------------------------------------------
SingleID  | UserID  | DescID  | PlayerID  | Price  | Volume  |
--------------------------------------------------------------
    1     |    1    |   15    |    256    |   2    |   150   |
    2     |    2    |   27    |    141    |   3.2  |   650   |
    3     |    1    |   17    |    322    |   3    |   500   |
    4     |    2    |   36    |    832    |   1.7  |   120   |
--------------------------------------------------------------

Multi:                                    MultiDetail:
---------------------------------------   ----------------------------------
|MultiID  | UserID  | Price  | Volume |   | MultiID  | DescID  | PlayerID  |
---------------------------------------   ----------------------------------
    1     |  11     |   5.1  |  300   |   |     1    |  15     |   256     |
    2     |  12     |   2    |  500   |   |     1    |  17     |   322     |
    3     |  13     |   4.7  |  900   |   |     2    |  36     |   956     |
    4     |  14     |   2.1  |  650   |   |     2    |  58     |   156     |
---------------------------------------   |     2    |  58     |   156     |
                                          |     3    |  15     |   256     |
                                          |     3    |  17     |   322     |
                                          |     4    |  36     |   956     |
                                          |     4    |  58     |   156     |
                                          ----------------------------------

As you may notice after looking a bit, UserID 1 in Single has 2 rows that together match the same combination of DescID/PlayerID as MultiID # 1 in MultiDetail. What I need to find is all MultiIDs, single-prices multiplied together and single-volumes added together where a Single.UserID can match *all* of the DescID/PlayerID-pairs in MultiDetail. Single.UserID is given, together with *one* pair of Desc/PlayerID. To explain better I have created this pseudo-code:

SELECT MultiID, EXP(SUM(LOG(Single.Price))), SUM(Single.Volume)
FROM Single join Multi join MultiDetail
WHERE Single.DescID = 15
AND Single.PlayerID = 156
AND Single.UserID = 1
AND MultiDetail.DescID = Single.DescID
AND MultiDetail.PlayerID = Single.PlayerID
AND EXP(LOG(SUM(Single.Price))) >= Multi.Price

DDL:

DECLARE @UserID int, @DescID int, @PlayerID int 
--> This is input, relative to the first row in Single
SET @UserID = 1
SET @DescID = 15
SET @PlayerID = 256

DECLARE @Single table 
  (SingleID int, UserID int, DescID int, PlayerID int, Price decimal(4, 2), Volume int)
DECLARE @Multi table (MultiID int, UserID int, Price int, Volume int)
DECLARE @MultiDetail table (MultiID int, DescID int, PlayerID int)

INSERT INTO @Single 
SELECT 1, 1, 15, 256, 2, 150 UNION ALL SELECT 2, 2, 27, 141, 3.2, 650 UNION ALL
SELECT 3, 1, 17, 322, 3, 500 UNION ALL SELECT 4, 2, 36, 832, 1.7, 120

INSERT INTO @Multi
SELECT 1, 11, 5.1, 300 UNION ALL SELECT 2, 12, 2, 500 UNION ALL 
SELECT 3, 13, 4.7, 900 UNION ALL SELECT 4, 14, 2.1, 650 

INSERT INTO @MultiDetail
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

SELECT * FROM @Single
SELECT * FROM @Multi
SELECT * FROM @MultiDetail

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

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-04 : 09:26:29

Does the following help?


SELECT md.MultiID, EXP(SUM(LOG(s.Price))) MultiPrice, SUM(s.Volume) MultiVolume
FROM @single s
JOIN @multidetail md 
	ON md.multiid = s.userid 
	and md.descid = s.descid 
	and md.playerid = s.playerid
WHERE s.userid = @UserID
GROUP BY md.MultiID

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-04 : 09:32:06

Well, I got the correct result but I belive this to be a chance of "luck" maybe...? Your join "md.multiid = s.userid" doesn't really make any sense...MultiID doesn't have anything to do with the userid in Single.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-04 : 09:48:29

OK, So the only relationship is between descid and playerid. Can you remove the join condition ( md.multiid = s.userid ) and get the correct result??


SELECT md.MultiID, EXP(SUM(LOG(s.Price))) MultiPrice, SUM(s.Volume) MultiVolume
FROM @single s
JOIN @multidetail md 
	ON md.descid = s.descid 
	and md.playerid = s.playerid
WHERE s.userid = @UserID
GROUP BY md.MultiID

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-04 : 10:07:16

If I remove the condition I will get all of the MultiIDs that have either of the DescID/PlayerID-combinations from Single, but I only need the MultiIDs where *both* DescID/PlayerID-combinations are matched in Single for a UserID. I hope my explanations are understandable...

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-04 : 10:11:16

I need to find this exact match:



Single:                     MultiDetail:
------------------          ----------------------------
DescID  | PlayerID  =>      MultiID | DescID  | PlayerID
------------------          ----------------------------
15      |   256     =>         1    |  15     |  256
17      |   322     =>         1    |  17     |  322

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-04 : 10:12:01

OK, I think I get it now.



SELECT md.MultiID, EXP(SUM(LOG(s.Price))) MultiPrice, SUM(s.Volume) MultiVolume
FROM @single s
JOIN @multidetail md 
	ON md.DescID = s.DescID 
	and md.playerid = s.playerid
WHERE s.userid = @UserID
GROUP BY md.MultiID
HAVING COUNT(md.DescID) = (SELECT COUNT(DescID) FROM @single WHERE UserID = @UserID)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-04 : 10:28:45

I'm afraid it's not that simple...Single has alot of different combinations for DescID/PlayerID for the same UserID as offcourse do MultiDetail. And if you run your query with the DDL I provided you will see that you will also get a match for MultiID 3 which has the same count and one matching pair, but it does not match both pairs.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-12 : 14:06:40

Well, since we have been in such an anti-cursor mode today down in the corral I thaught I'd bump this post and maybe get some help. I've struggeled with it for some time now but I have come to the point where I have deleted all my trials and errors to start off fresh again. I'm all ears when it comes to suggestions...

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources