Re: SQL "OR" Problem

On Friday 26 August 2005 12:55 pm, operationsengineer1(at)yahoo(dot)com
wrote:
> hi all,
>
> 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'

1) Is the t_inspect in there for a reason? I don't see it referenced
in your output or elsewhere in the where clause. Perhaps you are
looking only for inspectors? If so, an alternate method of writing
this where the intent is easier to grok is:
AND exists (select 1 from t_inspect where inspect_emp_id =
t.emp.emp_id)

2) If an employee has several positions then you should see several
lines as you have included the position in the output. My assumed
picture of your schema indicates that an employee could hold multiple
positions and, completely independently, could be an inspector.

3) I would need to know more about your data and think about this
query for a couple minutes to know if the parens will actually change
your output but as a safeguard and to make the intent clear, you
might want to consider parens around the positions or use "IN", ie.:
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')

or alternately
...t_pos.pos IN ('Assembler', 'Quality Inspector', 'Test Technician')

> 2. if an employee is a Qaulity Inspector...
This line gave my best chuckle of the day :). Thanks.

Cheers,
Steve