SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SOLVED - Distinct w/Inner Join and other fields?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bobV
Starting Member

8 Posts

Posted - 09/22/2012 :  22:32:33  Show Profile  Reply with Quote
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

Edited by - bobV on 10/05/2012 22:35:59

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/22/2012 :  22:59:36  Show Profile  Reply with Quote
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;
Go to Top of Page

bobV
Starting Member

8 Posts

Posted - 09/22/2012 :  23:13:34  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/22/2012 :  23:29:04  Show Profile  Reply with Quote
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/

Go to Top of Page

bobV
Starting Member

8 Posts

Posted - 09/25/2012 :  09:59:09  Show Profile  Reply with Quote
Thanks to all for the info I install MS SQL 2012 Express and wil ltry this out.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 09/25/2012 :  11:07:00  Show Profile  Reply with Quote
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

Edited by - Lamprey on 09/25/2012 11:07:43
Go to Top of Page

bobV
Starting Member

8 Posts

Posted - 09/25/2012 :  21:03:14  Show Profile  Reply with Quote
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?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 09/26/2012 :  12:12:07  Show Profile  Reply with Quote
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.
Go to Top of Page

bobV
Starting Member

8 Posts

Posted - 09/26/2012 :  13:03:52  Show Profile  Reply with Quote
Understood, thanks!
Go to Top of Page

bobV
Starting Member

8 Posts

Posted - 10/05/2012 :  22:31:26  Show Profile  Reply with Quote
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.



Edited by - bobV on 10/06/2012 20:47:08
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/07/2012 :  13:04:45  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000