Reputation: 1

How to use WHERE (SELECT ....)

I'm doing a pretty complicated reporting function with many conditions, many panels,

I have a record in tbl_my_report

id    param_filter

101   FIND_IN_SET(t.owner_department,'0620510200,0621510200,0623510200')

Query:

SELECT * 
FROM tbl_abc t WHERE t.id = '1' AND 
      (SELECT mr.param_filter 
       FROM tbl_my_report mr WHERE mr.id = '101'
      )

How to use it as a valid condition string?

Upvotes: 0

Answers (2)

Reputation: 184

Use this:

SELECT * FROM tbl_abc t WHERE  t.id = '1' IN (SELECT mr.param_filter
FROM tbl_my_report mr WHERE mr.id = '101' );

Upvotes: 0

Reputation: 520878

First, you should normalize your data, and get rid of FIND_IN_SET. So, there should be some department table looking like this:

id    | owner
'101' | '0620510200'
'101' | '0621510200'
'101' | '0623510200'

With this table in place, you may refactor your query to:

SELECT * 
FROM tbl_abc t
WHERE
    t.id = '1' AND
    EXISTS (SELECT 1 FROM department WHERE id = '101' AND owner = t.owner_department);

In general, you should avoid storing CSV or other unnormalized data in your database tables, for the very reason that it can make querying difficult.

Upvotes: 2