Question 259900 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Make a SQL select query for each item in the list?

Let’s say I have a student table like this:

ID Name Major Gender
1 Alice Math F
2 Bob Biology M
3 Candice Econ F

And I have a list of data like this:

names=["Alice,"Bob","Candice"] majors=["Math","Biology","Econ"] genders="["F","M","F"]

I want to return the first ID that matches all three columns for all entries in
the list
(e.g. SELECT id FROM students WHERE name='Alice' AND major = "Math" AND gender = "F").

Is there a way to get all of the data I need with just one SQL query instead of
having to make a separate query for each item in the list?

SELECT id FROM students WHERE name='Alice' AND major = "Math" AND gender = "F" ;
SELECT id FROM students WHERE name='Bob' AND major = "Biology" AND gender = "M" ;
SELECT id FROM students WHERE name='Candice' AND major = "Econ" AND gender = "F" ;

I can only get the IDs by making three separate queries. I want to combine these into one. I tried using IN, but that only seems to work when you only need 1 column to match. In this case, I need the name, major, and gender to match the entry in the list.

Tags:

2

Answers


  1. 0

    You can put multiple columns in a list that you test with IN.

    SELECT id
FROM students
WHERE (name, major, gender) IN (
    ('Alice', 'Math', 'F'), 
    ('Bob', 'Biology', 'M'), 
    ('Candice', 'Econ', 'F'));
    Login or Signup to reply.
  2. 0

    This can be done in Postgres using UNNEST and CTEs. If you dont want to use an CTE then you can use a subquery instead of a CTE.

    create table students (
  id serial,
  name varchar,
  major varchar,
  gender varchar
);

insert into students (name,major, gender) 
values ('Alice', 'Math', 'F'),
  ('Bob', 'Biology', 'M'),
  ('Candice', 'Econ', 'F');
  

WITH CTE0 AS (
  SELECT 
    UNNEST('{"Alice", "Bob", "Candice"}'::varchar[]) as name,
    UNNEST('{"Math","Biology","Econ"}'::varchar[]) as major,
    UNNEST('{"F","M","F"}'::varchar[]) as gender
) SELECT S.*
  FROM students S
  INNER JOIN CTE0 ON CTE0.name = S.name AND CTE0.major=S.major AND CTE0.gender = S.gender

    In the above I am using unnest to expand array into a set of rows, using multiple unnest will return a set of rows, each row is a records.

    Note: Make sure that all the arrays in Unnest contains the same length.

    Edit: I didn’t know UNNEST is a postgres only function and mysql doesn’t have this function.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search