select statement for intersecting fields

[Anon]

What select statement should I use for the following if I were to get only the person who is both a "Clerk" and a "Salesman"?

sample table

employee

name job

---

Mike Clerk
John Salesman
Bob Clerk
John Clerk

Biff Salesman

if I use "select * from employee where job = 'Clerk' and job = 'Salesman'", i get an empty field. any ideas? thanks.

[Anon]

Try the following,

select name from employee where job='Clerk' intersect select name from employee where job='Salesman'

and it should return John.

Hope that gets what you want.

Tim Frank

[Anon]

hi Tim,
thanks for the advice but I've tried it and it still doesn't work. it says "ERROR 1064: You have an error in your SQL syntax near 'intersect select...' at line 1". apparently the database I'm using doesn't understand the intersect command. I'm using mySql with PHP.

any suggestions/workarounds for this?

[Anon]

For doing this you need a comon id. Say john can work as 'stuf1' and 'stuf2' but still have the same number or what else. Otherwise, doing this on the firstname has a risk to get all the person with the same firstname (ok?).

select name from table where job = 'stuf1' and comon_id in (select comon_id from table where job = 'stuf2')

That's it !

Regards.