2

I need to check if a list of people's names are in our MySQL database.

SELECT * FROM tbltrainerinfo WHERE lastname = "Arpay"       AND firstname = "Belly Joy"
SELECT * FROM tbltrainerinfo WHERE lastname = "Bebelyn"     AND firstname = "Abulog"
SELECT * FROM tbltrainerinfo WHERE lastname = "Cabrera"     AND firstname = "Virgie"
SELECT * FROM tbltrainerinfo WHERE lastname = "Christopher" AND firstname = "Sajonia"
SELECT * FROM tbltrainerinfo WHERE lastname = "Edilberto"   AND firstname = "Galaura"
SELECT * FROM tbltrainerinfo WHERE lastname = "Embanecido"  AND firstname = "Efren"
SELECT * FROM tbltrainerinfo WHERE lastname = "Gertrulyn"   AND firstname = "Natonton"
SELECT * FROM tbltrainerinfo WHERE lastname = "Gina"        AND firstname = "Gindap"
SELECT * FROM tbltrainerinfo WHERE lastname = "Gindap"      AND firstname = "Gina"
SELECT * FROM tbltrainerinfo WHERE lastname = "Jeffrey"     AND firstname = "Ladiero"
SELECT * FROM tbltrainerinfo WHERE lastname = "JIMENEZ"     AND firstname = "JUNPORT"
SELECT * FROM tbltrainerinfo WHERE lastname = "Jocelyn"     AND firstname = "Paloma"
SELECT * FROM tbltrainerinfo WHERE lastname = "Ladera"      AND firstname = "Chared"
SELECT * FROM tbltrainerinfo WHERE lastname = "Lago"        AND firstname = "Michelle Joy"
SELECT * FROM tbltrainerinfo WHERE lastname = "Maratima"    AND firstname = "Untal"
SELECT * FROM tbltrainerinfo WHERE lastname = "Marciano"    AND firstname = "Iloreta"
SELECT * FROM tbltrainerinfo WHERE lastname = "Marianita"   AND firstname = "Gemina"
SELECT * FROM tbltrainerinfo WHERE lastname = "Pala"        AND firstname = "Sittie Norjanna"
SELECT * FROM tbltrainerinfo WHERE lastname = "Untal"       AND firstname = "Maratima"

How can I do one SELECT statement and get people's names if they are in the list?

  • Why is running the select statements you provided not enough? For example; are you asking for a single query that will return all rows that match the names in all those SELECT statements provided? Are you asking for criteria such as "Arpay" to be considered in both lastname and firstname? – Kent Chenery Nov 28 '17 at 6:31
  • @KentChenery I am trying to find if Arpay firstname and Belly Joy lastname is in the list. But I don't want to search just one. I needed to search multiple firstname and lastname in one Select statement. – Sandy Pabilonia Nov 29 '17 at 10:13
4

Here is the FROM (VALUES... version, with an EXISTS thrown in to get some speedy search shortcuts happening:

SELECT *
FROM tbltrainerinfo
WHERE EXISTS(
    SELECT *
    FROM (VALUES
        ('Arpay','Belly Joy')
        ,('Bebelyn','Abulog')
        ,('Cabrera','Virgie')
        ,('Christopher','Sajonia')
        ,('Edilberto','Galaura')
        ,('Embanecido','Efren')
        ,('Gertrulyn','Natonton')
        ,('Gina','Gindap')
        ,('Gindap','Gina')
        ,('Jeffrey','Ladiero')
        ,('JIMENEZ','JUNPORT')
        ,('Jocelyn','Paloma')
        ,('Ladera','Chared')
        ,('Lago','Michelle Joy')
        ,('Maratima','Untal')
        ,('Marciano','Iloreta')
        ,('Marianita','Gemina')
        ,('Pala','Sittie Norjanna')
        ,('Untal','Maratima')
        ) AS List(LName, FName)
    WHERE LName = lastName
      AND FName = firstname
    )
3

One option is to store the list of names in a table of its own (a temporary table or the equivalent, if your DBMS allows it). Then, simply join the temp table to your actual table.

The following uses MS SQL Server syntax:

CREATE TABLE #tmplist (lastname nvarchar(100), firstname nvarchar(100));

INSERT INTO #tmplist
VALUES ('Arpay','Belly Joy')
       ('Bebelyn','Abulog')
       ('Cabrera','Virgie')
       ('Christopher','Sajonia')
       ('Edilberto','Galaura')
       ('Embanecido','Efren')
       ('Gertrulyn','Natonton')
       ('Gina','Gindap')
       ('Gindap','Gina')
       ('Jeffrey','Ladiero')
       ('JIMENEZ','JUNPORT')
       ('Jocelyn','Paloma')
       ('Ladera','Chared')
       ('Lago','Michelle Joy')
       ('Maratima','Untal')
       ('Marciano','Iloreta')
       ('Marianita','Gemina')
       ('Pala','Sittie Norjanna')
       ('Untal','Maratima')
;

SELECT tti.*
  FROM tbltrainerinfo tti
         INNER JOIN #tmplist t ON (    tti.lastname = t.lastname
                                   AND tti.firstname = t.firstname
                                  )
;

If the actual data will trivially fit in memory, then a table variable or a hard-coded subquery ((SELECT X as lastname, Y as firstname UNION ALL...), or (VALUES (X, Y),...)) probably make more sense than a temp table, since you can avoid actually writing the data to disk.

  • 1
    You could also use a table variable or even the FROM VALUES syntax and avoid the need for a temp table. – Jonathan Fite Nov 28 '17 at 21:16
1
SELECT * 
FROM tbltrainerinfo 
where    ( lastname = "Arpay"       AND firstname = "Belly Joy" ) 
      OR ( lastname = "Bebelyn"     AND firstname = "Abulog" ) 

...

  • 1
    If this is the answer you used then you can click the check mark to indicate the answer. – paparazzo Nov 29 '17 at 13:38
0

PostgreSQL

If your database supports row constructors, you can do something like this,

SELECT true WHERE ROW(1,2) IN ( ROW(1,2) );

PostgreSQL doesn't require ROW() (it's implicit). So with PostgreSQL, it looks like,

SELECT *
FROM trainerinfo AS t1
WHERE (lastname,firstname) IN ( 
    ( 'Arpay'      , 'Belly Joy' ),
    ( 'Bebelyn'    , 'Abulog' ),
    ( 'Cabrera'    , 'Virgie' ),
    ( 'Christopher', 'Sajonia' ),
    ( 'Edilberto'  , 'Galaura' ),
    ( 'Embanecido' , 'Efren' ),
    ( 'Gertrulyn'  , 'Natonton' ),
    ( 'Gina'       , 'Gindap' ),
    ( 'Gindap'     , 'Gina' ),
    ( 'Jeffrey'    , 'Ladiero' ),
    ( 'JIMENEZ'    , 'JUNPORT' ),
    ( 'Jocelyn'    , 'Paloma' ),
    ( 'Ladera'     , 'Chared' ),
    ( 'Lago'       , 'Michelle Joy' ),
    ( 'Maratima'   , 'Untal' ),
    ( 'Marciano'   , 'Iloreta' ),
    ( 'Marianita'  , 'Gemina' ),
    ( 'Pala'       , 'Sittie Norjanna' ),
    ( 'Untal'      , 'Maratima' )
);

More information than the quick syntax provided by a a_horse_with_no_name in the comments.

At some point you probably want to create a temp table, and use a multicolumn index though.

BEGIN;
  CREATE TEMP TABLE f(lastname,firstname)
    ON COMMIT DROP
    AS VALUES
      ('foo','bar')
    ;
  CREATE INDEX ON f (lastname,firstname);
  SELECT *
  FROM tbltrainerinfo
    JOIN f USING (lastname, firstname);
COMMIT;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.