Syzorr Syzorr - 2 years ago 101
SQL Question

SQL Select Statement Where Clause

List first name, last name and course id for all students that are in
a course controlled by either Tony Richardson, Dennis Viehland or Kay
Fielden. Order your output in ascending order of course ID.

I am preparing for an SQL test by doing a practice set of questions of which the above is one. I have an answer for the following using a where clause like this for the names:

where SF_F_Name in ('Tony','Dennis','Kay') and SF_L_Name in ('Richardson','Viehland','Fielden')

I've been trying to find a way to look for the specific combinations of the first and the last name as this kind of where clause could, potentially, return a "Kay Richardson". How could this be achieved? I haven't been able to find any examples of how to do it.

Answer Source

You pointed out the flaw in your WHERE clause yourself. Just check for full names individually to avoid this problem:

WHERE (SF_F_Name = 'Tony'   AND SF_L_Name = 'Richardson') OR
      (SF_F_Name = 'Dennis' AND SF_L_Name = 'Viehland') OR
      (SF_F_Name = 'Kay'    AND SF_L_Name = 'Fielden')

You could also concatenate the first and last name columns together and then check for the full name using a single WHERE IN:

WHERE SF_F_NAME || ' ' || SF_L_Name IN
    ('Tony Richardson', 'Dennis Viehland', 'Kay Fielden')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download