Skip site navigation (1) Skip section navigation (2)

Re: SQL "OR" Problem

From: Philip Hallstrom <postgresql(at)philip(dot)pjkh(dot)com>
To: operationsengineer1(at)yahoo(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: SQL "OR" Problem
Date: 2005-08-26 20:10:02
Message-ID: 20050826130802.W18981@wolf.pjkh.com (view raw or flat)
Thread:
Lists: pgsql-novice
> i'm trying to populate a list box with the names of
> employees linked to certain positions (each name
> listed once)...
>
> SELECT DISTINCT t_emp.emp_id, t_emp.first_name || ' '
> || t_emp.last_name, t_pos.pos
>
> FROM t_inspect, t_emp, t_pos
> WHERE t_emp.pos_id = t_pos.pos_id
> AND t_inspect.inspect_emp_id = t_emp.emp_id
> AND t_pos.pos = 'Assembler'
> OR t_pos.pos = 'Quality Inspector'
> OR t_pos.pos = 'Test Technician'

Try:

SELECT DISTINCT t_emp.emp_id,
        t_emp.first_name || ' ' || t_emp.last_name,
        t_pos.pos
FROM t_inspect, t_emp, t_pos
WHERE
      t_emp.pos_id = t_pos.pos_id
      AND t_inspect.inspect_emp_id = t_emp.emp_id
      AND (
 	    t_pos.pos = 'Assembler'
             OR t_pos.pos = 'Quality Inspector'
             OR t_pos.pos = 'Test Technician'
          )


>
> i get the following results:
>
> 1. if an employee is an Assembler then s/he is listed
> 3 times - once with each position "pos".
>
> 2. if an employee is a Qaulity Inspector then s/he is
> listed 2 times - once with each position "pos"
> exlcuding "Assembler".
>
> 1. if an employee is a Test Technician then s/he will
> likely be listed once with "Test Technician".  i don't
> have a Test Technician in my dev db.
>
> i'm not sure if the OR keyword is supported or if i
> just made it up.  again, i'm just trying to list each
> name once in each of these three positions (a name can
> only be in one position category).
>
> i think i'm either abusing "OR", missing something in
> my where clause or, most probably, both.
>
> any help would be appreciated.
>
> tia...
>
>
>
> ____________________________________________________
> Start your day with Yahoo! - make it your home page
> http://www.yahoo.com/r/hs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

In response to

pgsql-novice by date

Next:From: Frank BaxDate: 2005-08-26 20:18:20
Subject: Re: SQL "OR" Problem
Previous:From: operationsengineer1Date: 2005-08-26 19:55:26
Subject: SQL "OR" Problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group