Join tables without looping - 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 2005 Forums

Transact-SQL (2005)

Join tables without looping

Author

Topic

dsvick
Starting Member

6 Posts

Posted - 2011-11-18 : 10:50:17

Sorry for the poor subject, I wasn't sure how to word it.

I have two tables one that contains a listing of purchase types with a typeID column and a description column. Then I have another table that is list of people and their spending limit by purchase type, it has columns
userID int
purchaseTypeID int
purchaseLimit int

I need to be able to get a list of everyone's purchase limits for each type of purchase. The problem is that everyone does not have an entry for each typeID and I need to get a result for every type for every person. Another way to put it is that if a person has limit set for one or more types I need to that person to appear in the result set with a record for every type.

Here is short example:


purchaseTypeId           desc
1                        books
2                        movies
3                        music

userID          typeID         limit
1               1              100
1               2              300
1               3              500
2               1              100
2               2              300
3               1              50
3               2              400
3               3              800
4               1              25
4               2              200

I need to run a query that will give me results like:
purchType      userID          limit
books           1              100
movies          1              300
music           1              500
books           2              100
movies          2              300
music           2              null
books           3              50
movies          3              400
music           3              800
books           4              25
movies          4              200
music           4              null

IF I do this with a left join I only get a single row for music with both userID and limit being null. I know I could this in the code with multiple calls to the DB, but I wanted to see if there was a recommended way to do it using SQL first. I looked at CTEs but I don't think that they are the way to go. Is my best bet going to be to use cursors in the stored procedure?

thanks, if that is not enough info let me know, I appreciate any input or nudges in the right direction.

Dave

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 10:54:21

[code]
SELECT p.[desc] as purchType,s.userID,m.limit
FROM purchase_types p
CROSS JOIN (SELECT DISTINCT userID FROM SpendingTable) s
LEFT JOIN SpendingTable m
ON m.userID = s.userID
AND m.typeID = p.PurchaseTypeID
[/code]

I've assumed table names in above query, make sure you use actual names instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dsvick
Starting Member

6 Posts

Posted - 2011-11-18 : 11:34:59

WOW!!! Thanks visakh!! That is completely awesome and much easier than I thought it would be. I looked at cross joins for about 10 seconds and didn't think they'd work.

I appreciate the quick response!

Dave

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 11:36:27

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources