Sample Header Ad - 728x90

Alternative query to this (avoid DISTINCT)

6 votes
3 answers
44614 views
Note: I work with MSSQL 2008, but I guess it's valid for many others DB engines I have this table "Users":
UserID	User	CountryID
1     user 1	1
2     user 2	2
3     user 3	3
4     user 4	4
5     user 5	4
6     user 6	3
And this table "Countries"
CountryID	Country
1	        MX
2	        USA
3	        CAN
4	        ENGLAND
As you can see, every user belongs to a country. If I want to know, all the different countries where I have at least, one user on the Users table, right now I do this query: select distinct country from Users inner join countries on users.CountryID=countries.CountryID And achieve the next result set:
CAN
ENGLAND
MX
USA 
Which is indeed, all the different countries, where I have at least one user on muy table Users. My doubt is, is possible to achieve the above result set, with out using "DISTINCT", I mean only using JOINS and conditions ? Here it's de DDL scripts:
USE [TEST]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 09/21/2012 16:21:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
	[UserID] [int] NULL,
	[User] [nvarchar](50) NULL,
	[CountryID] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (1, N'user 1', 1)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (2, N'user 2', 2)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (3, N'user 3', 3)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (4, N'user 4', 4)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (5, N'user 5', 4)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (6, N'user 6', 3)
/****** Object:  Table [dbo].[Countries]    Script Date: 09/21/2012 16:21:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Countries](
	[CountryID] [int] NULL,
	[Country] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (1, N'MX')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (2, N'USA')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (3, N'CAN')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (4, N'ENGLAND')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (5, N'BRAZIL')

Asked by Allende (247 rep)
Sep 21, 2012, 09:23 PM
Last activity: Sep 22, 2012, 03:06 PM