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 3And this table "Countries"
CountryID Country 1 MX 2 USA 3 CAN 4 ENGLANDAs 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 USAWhich 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
Last activity: Sep 22, 2012, 03:06 PM