Please help to create a query.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help to create a query.

Author

Topic

Huong
Starting Member

5 Posts

Posted - 2012-06-06 : 22:59:04

Hi All, please help me to solve my problem.
I have a table filled in below
id|columnA|columnB
1|A|P1
2|A|P2
3|A|P3
4|B|P4
5|B|P2
6|C|P1
7|C|P2
I do not know how to make a query to return the value of the columnA when give the combination of columnB. For example, the result should be A if the input combination is (P1,P2,P3), or should be C if (P1,P2) is given.
Thank in advance.

CJackson
Starting Member

2 Posts

Posted - 2012-06-07 : 01:10:41

That depends a bit on how you want to run the query...eg. how do you provide the combination?

The code below provides a possible solution, where @YourTable refers to the one you've given above, and @Combination is a table where you put in your combination. This should work provided you only have unique combinations (e.g. not (P1,P1,P2)). It's not a straightforward question though, hence why the code is quite long!

DECLARE @YourTable TABLE
(
id int,
columnA varchar(5),
columnB varchar(5)
)

INSERT INTO @YourTable
VALUES(1,'A','P1'),(2,'A','P2'),(3,'A','P3'),(4,'B','P4'),(5,'B','P2'),(6,'C','P1'),(7,'C','P2')

DECLARE @Combination TABLE
(
columnB varchar(5)
)

INSERT INTO @Combination
VALUES ('P1'),('P2'),('P3') --Put whatever you want in here

;WITH RequiredHits AS
(
SELECT
columnA,
COUNT(*) AS NumRequired
FROM @YourTable
GROUP BY
columnA
),
ActualHits AS
(
SELECT
columnA,
COUNT(*) AS ActualNum
FROM @YourTable YT
INNER JOIN @Combination C ON
YT.columnB=C.columnB
GROUP BY
columnA
)
SELECT
RequiredHits.columnA
FROM RequiredHits
INNER JOIN ActualHits ON
RequiredHits.columnA=ActualHits.columnA
AND RequiredHits.NumRequired=ActualHits.ActualNum
AND RequiredHits.NumRequired=(SELECT COUNT(*) FROM @Combination)
---------------------------------------------------------------------------

www.shortcutsql.com

Huong
Starting Member

5 Posts

Posted - 2012-06-07 : 05:19:24

It's a perfect answer.
Thank you CJackSon!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources