SQL Query help please

[VS 2008 / VB.Net / MS SQL 2005]

Hello, I have to create a query at work tomorrow, but I can't see a way to do it.

I want to return a list of customers from...

tblCUS
AccID - AccNo - AccName

...where there's an entry pair of HeadID-ValueID in the Analysis table...

tblANALYSYS
AccID - HeadID - ValueID (all integers)

...subject to up to 8 search criteria...

sHeadID_1 - sValueID_1
sHeadID_2 - sValueID_2
...
sHeadID_8 - sValueID_8

If any of sValuID_n are zero, I want the customer, subject to the other search criteria matching. (I can handle this in code when building the query - either 'ValueID=sValueID' or 'ValuID>0').

Any suggestions as to how I should approach this?

Maybe something like this:

(not tested code)

sql Code:

SELECT tblCus.*,  tblAnalysis.*
FROM tblCus
INNER JOIN tblAnalysis ON tblCus.AccID = tblAnalysis.AccID
WHERE tblAnalysis.HeadID IN (sValueID_1, sValueID_2, sValueID_3, sValueID_4, sValueID_5, sValueID_6, sValueID_7, sValueID_8)

NB: Replace sValueID_1, sValueID_2... in above code with actual values you are looking for.

Thank you for your reply. I'm afraid I got into a muddle trying to do it with the IN statement. I read that you can't compare more than 1 value, and I need to compare both the HeadID and ValueID.

But this led me to the EXISTS statement, and I came up with this (using constants for sHeadID and sValueID while testing) ...

Code:

---

SELECT A.AccID, A.AccNo, A.AccName
FROM tblCus As A
WHERE EXISTS ( SELECT B.AccID FROM tblAnalysis AS B
WHERE A.AccID = B.AccID AND B.HeadID = 1091 AND B.ValueID = 1108
AND EXISTS  ( SELECT B.AccID FROM tblAnalysis AS B
WHERE A.AccID = B.AccID AND B.HeadID = 1176 AND B.ValueID = 1210
AND EXISTS  ( SELECT B.AccID FROM tblAnalysis AS B
WHERE A.AccID = B.AccID AND B.HeadID = 3000 AND B.ValueID = 3001
AND EXISTS  ( SELECT B.AccID FROM tblAnalysis AS B
WHERE A.AccID = B.AccID AND B.HeadID = 4000 AND B.ValueID = 4001
AND EXISTS  ( SELECT B.AccID FROM tblAnalysis AS B
WHERE A.AccID = B.AccID AND B.HeadID = 5000 AND B.ValueID = 5001
AND EXISTS  ( SELECT B.AccID FROM tblAnalysis AS B
WHERE A.AccID = B.AccID AND B.HeadID = 6000 AND B.ValueID = 6001
AND EXISTS  ( SELECT B.AccID FROM tblAnalysis AS B
WHERE A.AccID = B.AccID AND B.HeadID = 7000 AND B.ValueID = 7001
AND EXISTS  ( SELECT B.AccID FROM tblAnalysis AS B
WHERE A.AccID = B.AccID AND B.HeadID = 8000 AND B.ValueID = 8001
))))))))
ORDER BY AccNo

---

This appears to work, but I've only tested it with the first 2 search criteria pairs so far. I think it explains my requirements better than my original post!

Using IN is the equivalent of using OR, eg:

Code:

---

WHERE fieldA IN('A', 'B')  AND something_else

---

is the same as:

Code:

---

WHERE (fieldA = 'A' OR fieldA = 'B') AND something_else

---

By using the expanded version, you can add the extra criteria, eg:

Code:

---

WHERE ( (B.HeadID = 1091 AND B.ValueID = 1108) OR (B.HeadID = 1176 AND B.ValueID = 1210) ... )

---

This isn't quite the same as your Exists version tho, as yours will only return records when all of the conditions match, whereas this will return records if any of them match - however you can get the same behaviour as the Exists by simply changing the Or's to And's

sql Code:

SELECT tblCus.*,  tblAnalysis.*
FROM tblCus
INNER JOIN tblAnalysis ON tblCus.AccID = tblAnalysis.AccID
WHERE (tblAnalysis.HeadID = 1091 AND tblAnalysis.ValueID = 1108)
   OR (tblAnalysis.HeadID = 1176 AND tblAnalysis.ValueID = 1210)
   OR (tblAnalysis.HeadID = 3000 AND tblAnalysis.ValueID = 3001)
   OR (tblAnalysis.HeadID = 4000 AND tblAnalysis.ValueID = 4001)
   OR (tblAnalysis.HeadID = 5000 AND tblAnalysis.ValueID = 5001)
...

The problem I found using INNER JOIN was that I needed all search criteria to be true. Using AND instead of OR didn't work because an Analysis record can never satisfy more than one condition. At least I think that is what was happening!

Due to severe time pressures I'm having to go with the EXISTS solution, which is working perfectly, and pretty quick too.

Thank you both for taking the time to look at this for me.