Re: Newbie SQL Question

I'm not sure i understand what you want, here's different queries:

1. mechanics who work in more than one garage: select mechanic.mid, mname from works_in, mechanic where works_in.mid = mechanic.mid group by mechanic.mid, mname having count(*) > 1 /
2. mechanics who work in more than one city: select a.mid, a.mname from (select distinct mechanic.mid, mname, garage.city from works_in, mechanic, garage where works_in.mid = mechanic.mid and garage.gid = works_in.gid) a group by a.mid, a.mname having count(*) > 1 /
3. mechanics who work in more than one garage in a city: select distinct mechanic.mid, mname from works_in, mechanic, garage where works_in.mid = mechanic.mid and garage.gid = works_in.gid group by mechanic.mid, mname, garage.city having count(*) > 1 /

--
Have a nice day
Michel

Vinh Duong <vduong_at_aei.ca> a écrit dans le message : 86givq$3nl$1_at_news.aei.ca...
> I have three tables:
>
> Mechanic(mid, mname, job_title, city) mid is the primary key
> Works_in(mid, gid, hours_per_week) where mid & gid are the primary keys
> Garage(gid, manager, city) where gid is the primary key
>
> How to write the SQL queries to:
> - List the mechanics who work in more than one garage in different cities.
>
> When reply, please copy me at vduong_at_aei.ca
>
> Gracias,
>
> Vinh
>
>
Received on Mon Jan 24 2000 - 11:14:30 CST