CODEWITHSUNDEEP
mysqlsql
Steven Wagner
Steven Wagner

Reputation: 3

How do I select rows that contain an exact match to a set of values

I have data like this: [{parentId: 1, childId: 2}, {parentId: 1, childId: 3}, {parentId:2, childId: 2}]

I need to query a table that has some columns like this:

parentId | childId | value
1        | 2       | .25
1        | 3       | .5
2        | 2       | .1
2        | 3       | 1

I need to be able to select the value of each object. For example {parentId: 1, childId: 2} would be .25.

I could do this with a loop (I'm using MYSQL2 and javascript):

  const values= []
  parentsAndChildren.forEach(parentChild => {
  const [rows, fields] = await db.query(
    `select value
    from table
    where parentId = ?
    AND childId = ?,
    [parentChild[0].parentId, parentChild[0].childId]
  );
  values.push(rows[0].value)
  })

But this is ugly and inefficient for all sorts of reasons. But hopefully gives you an example of the outcome I'm looking for. I can't use a WHERE IN clause like this:

select value
    from table
    where parentId in(1, 2)
    AND where childId in(2,3)

Because it will return row 5, which I don't want.

What is the best way to write this query to get back the values only if the row exactly matches the values of each object in the array?

Upvotes: 0

Views: 635

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

You can express this using in with tuples:

where (parentId, childId) in ( (1, 2), (1, 3), (2, 2) )

MySQL doesn't support arrays, so you have to do the transformation in the application language.

Upvotes: 1

Related Questions