Re: Newbie SQL Question

From: GHouck <hksys_at_teleport.com>
Date: Mon, 24 Jan 2000 01:10:58 -0800
Message-ID: <388C1722.F15_at_teleport.com>

Vinh Duong wrote:
>
> 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

Something like this might work (if the count for mid and gid is greater than one, then select the columns you desire):

select

  m.mid, 
  m.mnam, 
  m.job_title, 
  m.city, 
  w.hours_per_week, 
  g.manager,g.city 

from
  mechanic m,
  works_in w,
  garage g
where
  m.mid = w.mid and
  w.gid = g.gid and
  exists
  (
    select
      m.mid, g.gid
    from
      mechanic m, garage g, works_in w
    where

      m.mid = w.mid and 
      w.gid = g.gid 
    group by 
      m.mid, g.gid 
    having 
      count(*) > 1

  )

I don't have any way to verify it, but it should be close.

Yours,

Geoff Houck
systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys Received on Mon Jan 24 2000 - 10:10:58 CET