Search code examples
mysqlarraysnestedwhere-clauserecordset

MySQL Select WHERE IN recordset

I'll try to explain my problem. I have two tables. In the first one each record is identified by a unique INT code (counter). In the second the code from the first table is one of the fields (and may be repeated in various records).

I want to make a SELECT CODE in the second table, based on WHERE parameters, knowing I will get as result a recordset with possibly repeated CODES, and use this recordset for another SELECT in the first table, WHERE CODE IN the above recordset (from the second table).

Is this possible ?? And if yes, how to do this ?

Usually, if I use the WHERE IN clause, the array can contain repeated values like WHERE Code IN "3,4,5,6,3,4,2" ... right ? The difference here is that I want to use a previously Selected recordset in place of the array.

Solution

  • Is this possible ?? Sure is.

    And if yes, how to do this ? Like most questions answers depends. There's more than one way to skin this cat; and depending on data (volume of records), and indexes answers can vary.

    You can use a distinct or group by to limit the table A records because the join from A--> b is a 1--> many thus we need to distinct or group by the values from A as they would be repeated. But if you need values from B as well, this is the way to do it.

    Select A.Code, max(count B.A_CODE) countOfBRecords
from B
LEFT JOIN A
 on A.Code = B.A_Code
WHERE B.Paramater = 'value'
 and B.Paramater2 = 'Value2'
group by A.Code)

    Or using your approach (works if you ONLY need values/columns from table A.)

    Select A.Code 
from A
Where code in (Select B.A_CODE
               From B WHERE B.Paramater = 'value'
                and B.Paramater2 = 'Value2')

    But these can be slow depending on data/indexes.

    You don't need the distinct on the inner query as A.Code only exists once and thus wouldn't be repeated. It's the JOIN which would cause the records to repeat not the where clause.

    -Correlated Subquery will return a single A.Code works if you ONLY need values from table A.

    Select A.Code
From A
Where exists (Select 1 
              from B 
              where b.paramter = value ... 
                AND A.Code = B.A_CODE)

    Since there's no join no A.records would be repeated. On larger data sets this generally performs better .

    This last approach works because it Correlates the outer table with sub select Note this can only go 1 level in a relationship. If you had multiple levels deep trying to join this way, it woudln't work.