SOLVED - Distinct w/Inner Join and other fields? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

SOLVED - Distinct w/Inner Join and other fields?

Author

Topic

bobV
Starting Member

8 Posts

Posted - 2012-09-22 : 22:32:33

How do I get just the unique rows (distinct), and more than 1 column/field from table, and do an inner join too?

EX: I'd like to know what Rooms have something in them and what the first item in the room is. So the results would be:

RoomNum, Room, ItemID, Item
1 Kitchen 1 Fridge
2 Mast Bedroom 2 King Bed
3 Liv Room 3 HDTV
5 Bathroom 5 Towels
7 Garage 7 Boxes

The query below provides the correct list but when I try to add another field the distinct list gets expanded.
SELECT Distinct Items.RoomNum
FROM Items
INNER JOIN Rooms ON Items.RoomNum = Rooms.RoomID
ORDER BY Items.RoomNum;

Table - ITEMS
ItemID, Item, RoomNum
1 Fridge 1
2 Stove 1
3 King Bed 2
4 Bureau 2
5 HDTV 3
6 Towels 5
7 Boxes 7

Table - Rooms
RoomID, Room
1 Kitchen
2 Mast Bedroom
3 Liv Room
4 Attic
5 Bathroom
6 Basement
7 Garage

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-22 : 22:59:36

DISTINCT looks for distinctness on ALL the columns in the select list. So if even one column is different between two rows, they will be considered distinct.

Instead of using distinct there are a few other ways in which you can get the result you are looking for - using row_number function, using an aggregate function to pick one of the Items, using cross apply etc. If you use cross apply (which works on SQL 2005 and later), the query would be like this:

SELECT r.RoomID,
       r.Room,
       i.ItemId,
       i.Item
FROM   Rooms r
       CROSS APPLY
       (
       		SELECT TOP (1)
       			i2.ItemId, i2.Item
       		FROM
       			Items i2
       		WHERE
       			i2.RoomNum = r.RoomId
       		ORDER BY
       			i2.ItemId
       ) i
ORDER BY
       r.RoomID;

bobV
Starting Member

8 Posts

Posted - 2012-09-22 : 23:13:34

Sunitabeck,
Thanks for your reply. I hear you re distinct...makes sense. We have MS SQL 2008 in work and I'm playing around w/Access at home. I tried your suggestion but Access doesn't recognize Cross Ref. I will try and figure out how to use row_number or aggregate.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-22 : 23:29:04

quote:
Originally posted by bobV

Sunitabeck,
Thanks for your reply. I hear you re distinct...makes sense. We have MS SQL 2008 in work and I'm playing around w/Access at home. I tried your suggestion but Access doesn't recognize Cross Ref. I will try and figure out how to use row_number or aggregate.

Access doesnt have CROSS APPLY neither does it have ROW_NUMBER()

I think you may be better off installing and trying using SQL 2008 Express edition at home which is a free download

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

bobV
Starting Member

8 Posts

Posted - 2012-09-25 : 09:59:09

Thanks to all for the info I install MS SQL 2012 Express and wil ltry this out.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-25 : 11:07:00

Maybe this will help:

SELECT 
    r.RoomID,
    r.Room,
    i.ItemId,
    i.Item
FROM   
    Rooms AS r
INNER JOIN
    -- Get the "first" Item for each Room.
    (
        SELECT
       	    MIN(ItemId) AS ItemID,
            RoomNum
        FROM
       	    Items
        GROUP BY
            RoomNum
            
    ) t
    ON r.RoomID = t.RoomNum
INNER JOIN  
    Items AS i
    ON r.RoomID = i.RoomNum
    AND t.ItemID = i.ItemID
    AND t.RoomNum = i.RoomNum

bobV
Starting Member

8 Posts

Posted - 2012-09-25 : 21:03:14

Lamprey thanks for the suggestion. I removed the "-- Get the "first" Item for each Room.
" and tried this w/Access and get "syntax error in from clause". I tried a few different things but keep getting various errors. Should I be trying this w/MS SQL?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-26 : 12:12:07

Yes, this is an MS SQL forum, so the solutions you get will be greared that way. I haven't messed with Access in a long, long time so I can't help you there.

bobV
Starting Member

8 Posts

Posted - 2012-09-26 : 13:03:52

Understood, thanks!

bobV
Starting Member

8 Posts

Posted - 2012-10-05 : 22:31:26

AWESOME!! Installed SQL 2012 Express and tried both solutions and they both did exactly what I wanted. Now I need to try and uderstand how/why they work.

Thanks to all for taking the time to help out a newbie.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-07 : 13:04:45

quote:
Originally posted by bobV

AWESOME!! Installed SQL 2012 Express and tried both solutions and they both did exactly what I wanted. Now I need to try and uderstand how/why they work.

Thanks to all for taking the time to help out a newbie.

see this to understand the apply operator

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources