Using distinct with queries that return more than one column

[EnemyGateIsDown]

Hi Guys,

I have a query that returns multiple columns and I am only interested in the distinct ones.

there is a single column that would indicate distinctivness but as the distict function is an aggregate one I am having problems.

Anyone know how I can acheive this and still return the columns i need?

Thanks as always for any help.

Cheers,

Chris

 

[PHV]

Can you please post your actual SQL code, some input values example and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[EnemyGateIsDown]

Hi

Thanks for your response.

Below is the code:

I am passing the query to an access db using ASP so I have removed the variable referencing and replaced with Query1 and Query2 in the final query for easier reading.

[sub]

[b][u]Query 1[/u][/b]

SELECT Rooms.Room_ID
 FROM TBL_Preferences
 INNER JOIN ((Rooms INNER JOIN Locations ON Rooms.Location_ID = Locations.Location_ID)
 INNER JOIN [Location Permissions] ON Locations.Location_ID = [Location Permissions].Location_ID)
 ON TBL_Preferences.Supplier_Attendee_Selected = Rooms.SuppDelID
 WHERE ((([Location Permissions].Visible)<>0)
 AND ((TBL_Preferences.Supplier_Attendee)=" & Request.Form ("hSuppDelID") & ")
 AND (([Location Permissions].UserGroup_ID)="& Session("Perm_UserGroup_ID") &"))
 ORDER BY TBL_Preferences.Rank;

[b][u] Query 2 [/u][/b]

SELECT Rooms.Room_ID
 FROM ((Rooms INNER JOIN Locations ON Rooms.Location_ID = Locations.Location_ID)
 INNER JOIN [Location Permissions] ON Locations.Location_ID = [Location Permissions].Location_ID)
 INNER JOIN (Rooms AS Rooms_1 INNER JOIN TBL_Preferences ON Rooms_1.SuppDelID = TBL_Preferences.Supplier_Attendee_Selected)
 ON Rooms.SuppDelID = TBL_Preferences.Supplier_Attendee
 WHERE ((([Location Permissions].Visible)<>0)
 AND ((Rooms_1.SuppDelID)=" & Request.Form ("hSuppDelID") & ")
 AND (([Location Permissions].UserGroup_ID)=" & Session("Perm_UserGroup_ID") & "))
 ORDER BY TBL_Preferences.Rank;

[b][u]Query 3 [/u][/b]

SELECT Rooms.Location_ID, Rooms.Room_ID, Rooms.[Meeting Room],
Locations.[Location Name], [Location Permissions].[Add Reservations],
[Location Permissions].Visible, Rooms.SuppDelID"
FROM (Rooms INNER JOIN Locations ON Rooms.Location_ID = Locations.Location_ID) 
INNER JOIN [Location Permissions] ON Locations.Location_ID = [Location Permissions].Location_ID
WHERE Rooms.Room_ID IN (Query1) AND Rooms.Room_ID IN (Query2)

[/sub]

So the final query (query 3) i only want the distinct room names (Rooms.[Meeting Room])

Cheers,

Chris

 

[EnemyGateIsDown]

Don't think i made my requirement clear reading it back .

The query returns rows but there are duplicates, I am only interested in the distinct rows, distinctivness is indicated by the room name Rooms.[Meeting Room] or Rooms.Room_ID, I still need all the other columns in the query however.

Cheers,

Chris

 

[PHV]

Does SELECT DISTINCT solve the problem ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[EnemyGateIsDown]

It would do if I were only retrieving a single column as its an aggregate function... I've got an idea actually.. Its amazing how talking it through gets the old cogs turning.. thanks PHV!!

Chris