SQL subquery WHERE IN clauses - SQL Server Forums

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

SQL subquery WHERE IN clauses

Author

Topic

giosermao
Starting Member

2 Posts

Posted - 2010-08-07 : 09:35:54

Hi,

I have this problem: I need to get a group of records where a StatusID is included on a field from another table. Let' say: StatusID='ASC' from StatusTable table and I have another table StatusFilterTable with Filter field which have this value: "MNR,AQC,TVR,ASC,MVR"

I tried with this SP:

@Status varchar(50)
AS
SELECT A.StatusID + ' - ' + A.StatusDescripcion AS StatusDescription
FROM StatusTable A
WHERE A.Activo=1 AND A.StatusID IN (SELECT B.Filter FROM StatusFilter B WHERE B.StatusID=@Status)
ORDER BY A.StatusID
RETURN

But it returns zero records. Any help will be really appreciated.

My best regards,

Kristen
Test

22859 Posts

Posted - 2010-08-07 : 09:43:13

"I have another table StatusFilterTable with Filter field which have this value: "MNR,AQC,TVR,ASC,MVR""

Correct answer is that the design needs changing, I'm afraid. The StatusFilter table needs separate rows (or a child table with multiple rows) for "MNR", "AQC" ... etc

If I've understood correctly here's a bodge workaround - but I don't recommend this approach:


SELECT A.StatusID + ' - ' + A.StatusDescripcion AS StatusDescription
FROM StatusTable A
    JOIN StatusFilter AS B
        ON ',' + B.Filter + ',' LIKE '%,' + A.StatusID +',%'
       AND B.StatusID=@Status

WHERE A.Activo=1
ORDER BY A.StatusID

(Not sure that StatusFilter.StatusID is the right column is used BOTH to contains "MNR,AQC,TVR,ASC,MVR" AND to match @Status - but if it is Fine! if not you canprobably sort out wherever the typo was.

giosermao
Starting Member

2 Posts

Posted - 2010-08-07 : 10:13:13

Kristen:

Thanks your your prompt answer. I did a test with your answer and it solves the issue with a little tweaks, but unfortunately I can not change the structure of the tables. I was expecting there was a different clause to use similar to 'WHERE IN', which I am not aware of.

Thank you again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources