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 : 86gj1r$3nq$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

>

>