February 13, 2012 at 5:20 am
Suppose I have a table called employee and the data's are as given below
Code Name City
1 A India
1 A USA
1 A Singapore
2 B India
2 B USA
3 C Singapore
4 D USA
I need code and names where they are in cities 'India' and 'USA' only that is output should be
1 A India
1 A USA
2 B India
2 B USA
can you help me to get the output like this using SQL Query . Please reply as soon as possible.
February 13, 2012 at 5:28 am
Dude your table is missing a unique column, but you could try a distinct fucntion. that should help you
select distinct(*)
from employees
or you can use
the 'IN' function
February 13, 2012 at 5:28 am
Seems you have got a class -assignment , either case I guess ,you have not understood the SQL concepts well or have not studied them. Please go through and I bet you can answer yourself.
Hint:- Use "IN" operator.
February 13, 2012 at 6:04 am
Try using OR in the WHERE clause. See what happens then.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2012 at 9:46 am
February 13, 2012 at 9:43 pm
Thanks for all for your reply.... actually i have tried OR and IN conditions but they are not giving the output as needed. I will explain the problem once more it is like this as specified below.
Suppose I have a table called employee and the data's are as given below
Code Name City
1 A India
1 A USA
1 A Singapore
2 B India
2 B USA
3 C Singapore
4 D USA
I need code and names where they are in cities 'India' and 'USA' only that is output should be
1 A India
1 A USA
2 B India
2 B USA
can you help me to get the output like this using SQL Query . Please reply as soon as possible.
on using OR and IN clauses in queries am getting
name which is in USA but not in India also. that is output coming as
1 A India
1 A USA
2 B India
2 B USA
4 D USA ( i do not need this row because D is not in INDIA )
I need the query to satify that names must be in india as well as it should be in USA also.
February 13, 2012 at 10:15 pm
Sankar,
In the future, help us help you. Please post your data as I've done below. See the first link in my signature line below for more details.
CREATE TABLE #MyHead
(
Code INT,
Name CHAR(1),
City VARCHAR(10)
)
;
INSERT INTO #MyHead
(Code,Name,City)
SELECT '1','A','India' UNION ALL
SELECT '1','A','USA' UNION ALL
SELECT '1','A','Singapore' UNION ALL
SELECT '2','B','India' UNION ALL
SELECT '2','B','USA' UNION ALL
SELECT '3','C','Singapore' UNION ALL
SELECT '4','D','USA'
;
Here's one of the faster solutions to the problem (don't make me prove it... I've tested this before ;-)).
WITH
cteFindPairs AS
(
SELECT Code, Name
FROM #MyHead
WHERE City IN ('India','USA')
GROUP BY Code, Name
HAVING COUNT(DISTINCT City) = 2
)
SELECT mh.*
FROM #MyHead mh
INNER JOIN cteFindPairs pair
ON mh.Code = pair.Code
AND mh.Name = pair.Name
AND mh.City IN ('India','USA')
;
Here's the output...
Code Name City
----------- ---- ----------
1 A India
1 A USA
2 B India
2 B USA
If you don't really need to see the City (Trust the CODE, Luke!), then the code gets a whole lot easier...
SELECT Code, Name
FROM #MyHead
WHERE City IN ('India','USA')
GROUP BY Code, Name
HAVING COUNT(DISTINCT City) = 2
;
That returns the following...
Code Name
----------- ----
1 A
2 B
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2012 at 10:26 pm
Another option:
DECLARE @data TABLE
(
Code integer NULL,
Name character(1) NULL,
Country nvarchar(50) NULL
);
INSERT @data
(Code, Name, Country)
VALUES
(1, 'A', 'India'),
(1, 'A', 'USA'),
(1, 'A', 'Singapore'),
(2, 'B', 'India'),
(2, 'B', 'USA'),
(3, 'C', 'Singapore'),
(4, 'D', 'USA');
SELECT y.Code, y.Name, y.Country FROM
(
SELECT *, mdr = MAX(x.dr) OVER (PARTITION BY x.Code) FROM
(
SELECT *, dr = DENSE_RANK() OVER (PARTITION BY Code ORDER BY Country)
FROM @data AS d
WHERE d.Country IN ('India', 'USA')
) AS x
) AS y
WHERE y.mdr = 2;
February 13, 2012 at 10:41 pm
And another:
DECLARE @data TABLE
(
Code integer NULL,
Name character(1) NULL,
Country nvarchar(50) NULL
);
INSERT @data
(Code, Name, Country)
VALUES
(1, 'A', 'India'),
(1, 'A', 'USA'),
(1, 'A', 'Singapore'),
(2, 'B', 'India'),
(2, 'B', 'USA'),
(3, 'C', 'Singapore'),
(4, 'D', 'USA');
SELECT *
FROM
(
SELECT *
FROM @data AS d
PIVOT (MAX(Name) FOR Country IN (India, USA)) AS p
WHERE p.India IS NOT NULL AND p.USA IS NOT NULL
) AS x
UNPIVOT (Name FOR Country IN (India, USA)) AS u;
February 14, 2012 at 2:04 am
Thanks all for the reply...I got a solution for this its working fine as I have expected the query is similar to your answers
SELECT * FROM Cities WHERE Code IN(select Code from cities where city IN ('India', 'USA', 'ThirdState') GROUP BY Code HAVING COUNT(DISTINCT city) = 3)AND city IN ('India', 'USA', 'ThirdState')
February 14, 2012 at 3:00 am
Am I missing something?
SELECT Code, Name, City
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY City ORDER BY Code) AS rn
FROM #MyHead
WHERE City IN ('India','USA')) a
WHERE a.rn <= 2
ORDER BY Code;
Yes, the OP had a bit of scope creep further down the thread. Ignore the above.
SELECT a.Code, a.Name, a.City
FROM (SELECT Code, Name, City
FROM #MyHead
WHERE City IN ('India','USA')) a
INNER JOIN (SELECT Code, Name, City
FROM #MyHead
WHERE City IN ('India','USA')) b ON a.Code = b.Code AND a.City <> b.City
February 14, 2012 at 6:19 am
Cadavre (2/14/2012)
Am I missing something?
SELECT Code, Name, CityFROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY City ORDER BY Code) AS rn
FROM #MyHead
WHERE City IN ('India','USA')) a
WHERE a.rn <= 2
ORDER BY Code;
--edit--Yes, the OP had a bit of scope creep further down the thread. Ignore the above.
SELECT a.Code, a.Name, a.CityFROM (SELECT Code, Name, City
FROM #MyHead
WHERE City IN ('India','USA')) a
INNER JOIN (SELECT Code, Name, City
FROM #MyHead
WHERE City IN ('India','USA')) b ON a.Code = b.Code AND a.City <> b.City
Although the "groups" are quite small in the given example, consider that that's a "double Triangular Join" and that it will have a relatively severe impact on tables that have larger groups.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2012 at 8:07 am
Jeff Moden (2/14/2012)
Cadavre (2/14/2012)
Am I missing something?
SELECT Code, Name, CityFROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY City ORDER BY Code) AS rn
FROM #MyHead
WHERE City IN ('India','USA')) a
WHERE a.rn <= 2
ORDER BY Code;
--edit--Yes, the OP had a bit of scope creep further down the thread. Ignore the above.
SELECT a.Code, a.Name, a.CityFROM (SELECT Code, Name, City
FROM #MyHead
WHERE City IN ('India','USA')) a
INNER JOIN (SELECT Code, Name, City
FROM #MyHead
WHERE City IN ('India','USA')) b ON a.Code = b.Code AND a.City <> b.City
Although the "groups" are quite small in the given example, consider that that's a "double Triangular Join" and that it will have a relatively severe impact on tables that have larger groups.
Hmm, is it really so much worse than the code you suggested?
Jeff Moden (2/13/2012)
WITHcteFindPairs AS
(
SELECT Code, Name
FROM #MyHead
WHERE City IN ('India','USA')
GROUP BY Code, Name
HAVING COUNT(DISTINCT City) = 2
)
SELECT mh.*
FROM #MyHead mh
INNER JOIN cteFindPairs pair
ON mh.Code = pair.Code
AND mh.Name = pair.Name
AND mh.City IN ('India','USA')
;
(I'm not claiming it isn't worse than yours, just that I don't really see how - will have to do a nice big test set-up when I have time later on).
February 14, 2012 at 8:29 am
Cadavre (2/14/2012)
(I'm not claiming it isn't worse than yours, just that I don't really see how - will have to do a nice big test set-up when I have time later on).
And I answered my own question with a resounding "YES!!" 😛
BEGIN TRAN
SET NOCOUNT ON;
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
--1,000,000 Random rows of data
SELECT IDENTITY(INT,1,1) AS ID, alphaSeed AS Code, alpha AS Name, country AS City
INTO #testEnvironment
FROM (SELECT TOP 1000000
(ABS(CHECKSUM(NEWID())) % 4) + 1 AS countrySeed,
(ABS(CHECKSUM(NEWID())) % 26) + 1 AS alphaSeed
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3) a
CROSS APPLY (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',a.alphaSeed,1) AS alpha) b
CROSS APPLY (SELECT CASE WHEN countrySeed = 1 THEN 'UK'
WHEN countrySeed = 2 THEN 'USA'
WHEN countrySeed = 3 THEN 'India'
ELSE 'Singapore' END AS country) c;
PRINT '========== BASELINE ==========';
SET STATISTICS TIME ON;
SELECT COUNT(*) FROM #testEnvironment;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT '========== JEFF ==========';
SET STATISTICS TIME ON;
WITH cteFindPairs
AS (SELECT Code, NAME
FROM #testEnvironment
WHERE City IN ('India', 'USA')
GROUP BY Code, NAME
HAVING COUNT(DISTINCT City) = 2)
SELECT COUNT(*) --GET RID OF DISPLAY TIME
FROM (
SELECT mh.*
FROM #testEnvironment mh
INNER JOIN cteFindPairs pair ON mh.Code = pair.Code AND mh.NAME = pair.NAME AND mh.City IN ('India', 'USA')
) A;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT '========== PAUL ==========';
SET STATISTICS TIME ON;
SELECT COUNT(*) --GET RID OF DISPLAY TIME
FROM (
SELECT y.Code, y.Name, y.City FROM
(
SELECT *, mdr = MAX(x.dr) OVER (PARTITION BY x.Code) FROM
(
SELECT *, dr = DENSE_RANK() OVER (PARTITION BY Code ORDER BY City)
FROM #testEnvironment AS d
WHERE d.City IN ('India', 'USA')
) AS x
) AS y
WHERE y.mdr = 2
) A;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT '========== CAD ==========';
SET STATISTICS TIME ON;
SELECT COUNT_BIG(*) --GET RID OF DISPLAY TIME
FROM (
SELECT a.Code, a.Name, a.City
FROM (SELECT Code, Name, City
FROM #testEnvironment
WHERE City IN ('India','USA')) a
INNER JOIN (SELECT Code, Name, City
FROM #testEnvironment
WHERE City IN ('India','USA')) b ON a.Code = b.Code AND a.City <> b.City
) A;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
ROLLBACK
========== BASELINE ==========
-----------
1000000
SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 122 ms.
================================================================================
========== JEFF ==========
-----------
499475
SQL Server Execution Times:
CPU time = 1623 ms, elapsed time = 479 ms.
================================================================================
========== PAUL ==========
-----------
499475
SQL Server Execution Times:
CPU time = 5037 ms, elapsed time = 3072 ms.
================================================================================
========== CAD ==========
--------------------
4797469872
SQL Server Execution Times:
CPU time = 1732 ms, elapsed time = 545 ms.
================================================================================
All things considered, it was actually pretty fast. But produced an extra 4,796,970,397 rows of data in the result-set compared to yours and Paul's solutions.
February 15, 2012 at 9:20 pm
🙂
I did say that I've tested this before. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply