Tsql: Could you please help me write TSQL query?

Question

suppose that we have a table called table1 in Sql server with te following structure and data:

TableName: Parts

PartCode   PartName
-------------------
10         Dashboard
11         Mirror
12         Seat
13         Cord

TableName: CarParts

CarCode    PartCode
--------------------
01          10
01          11
01          12
02          11
02          12
03          10
03          11
03          13

How can I get CarCode(s) which contain ALL PartCodes I have listed in a data structure like a datagridview in a winform (C#)? I should say that I have written a UDF in Sql server that takes 1 argument (CarCode) and returns a CSV of all PartCodes that are related to it.

EDIT: for example my PartCode list has 2 PartCodes: 10, 12 The query should return CarCode(s) that contain both 10 and 12 and it's only CarCode "01" not the others. I hope this clarifies what I mean.

score 1 · Answer 1 · edited Jan 27 '11 at 12:12

1

You can compare counts of

number of search codes
number of found codes

If they match, you have the right CarCode

This assumes an input table

SELECT
    CP.CarCode
FROM
    CarParts CP
    JOIN
    @MyParts mp ON CP.PartCode = mp.PartCode
GROUP BY
    CP.CarCode
HAVING
    COUNT(CP.*) = COUNT(DISTINCT mp.PartCode)

This assumes discete values

SELECT
    CP.CarCode
FROM
    CarParts CP
WHERE
    CP.PartCode IN (10, 12)
GROUP BY
    CP.CarCode
HAVING
    COUNT(CP.*) = 2 --numbr of IN conditions

You should be able to work your CSV stuff into these

edited Jan 27 '11 at 12:12

Lieven Keersmaekers

57,207
13
112
146

answered Jan 27 '11 at 12:05

gbn

422,506
82
585
676

+1 but given OP's input, I thought your initial query worked to and was much simpler. What did you consider wrong with it? – Lieven Keersmaekers Jan 27 '11 at 12:10
@Lieven: It gaves rows where all Parts were present in CarParts. On 2nd reading of question, it's only where it matches input rows. And no need for the Parts table. A big bun fight here about the same "match multiple child rows" problem http://stackoverflow.com/questions/4763143 – gbn Jan 27 '11 at 12:13

score 0 · Answer 2 · answered Jan 27 '11 at 13:31

0

SELECT cp.CarCode
  FROM CarParts cp

EXCEPT

SELECT cp.CarCode
  FROM CarCode cp
    LEFT JOIN @MyParts mp ON cp.PartCode = mp.PartCode
  WHERE mp.PartCode IS NULL

answered Jan 27 '11 at 13:31

Andriy M

76,112
17
94
154

Tsql: Could you please help me write TSQL query?

2 Answers2