Linking Tables

• Sharon

  Grasshopper

  Points: 11

  More actions

  May 13, 2003 at 9:43 am

  #80221

  I find myself getting stuck on

  this same issue over and over again. I have two tables: 1)

  INVENTORY 2)COMPANIES.

  In the INVENTORY table there are three fields: Item Name,

  Manufacturer ID and Servicer ID. The actual company names

  (of the manufacturer and servicer) are in the COMPANIES

  table. The COMPANIES table has two fields: ID, Name. The

  INVENTORY table links to companies like this:

  INVENTORY.ManufacturerID = COMPANIES.ID and

  INVENTORY.ServicerID = COMPANIES.ID. So you see, the

  COMPANIES table is just a way to get company names.

  Instead of using just the INVENTORY table to return info

  that looks like this:

  Desk 321355 643223

  I want to connect the INVENTORY and COMPANIES table to get

  info to looks like this:

  Desk The Desk Company Mr. FixIt

  Now I think the theory of normalizing data would call to

  separate the COMPANIES table into two tables:

  Manufacturers and Servicers but this is not the way my

  company has structured their database.

  Can you help me with a query that connects the INVENTORY

  table with the COMPANIES table so that I can extract 2

  company names in one query?

  Basically, I want to do something like this (even though i

  know THIS will not work)

  SELECT Inventory.Item, select Name from Companies where

  Companies.ID = (select ManufacturerID from Inventory) AS

  Manufacturer, select Name from Companies where

  Companies.ID = (select ServicerID from Inventory) AS

  Servicer.

  Thanks for your help,

  Sharon

• dgermundson

  Hall of Fame

  Points: 3175

  More actions

  May 13, 2003 at 9:07 pm

  #457058

  Try this:

  SELECT Inventory.Item, c.CompName

  from Companies c ,Inventory i where

  (c.ID = i.ManufacturerID )

  union select Inventory.Item, s.SvcName

  from companies s, inventory i

  where (s.ServicerId = i.ServicerId)

  Darren

  
  Darren
  

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  May 14, 2003 at 3:46 am

  #457076

  Try

  SELECT i.ItemName,c1.Name AS 'ManuName',c1.Name AS 'ServName'

  FROM INVENTORY i

  INNER JOIN COMPANIES c1 ON c1.ID = ManufacturerID

  INNER JOIN COMPANIES c2 ON c2.ID = ServicerID

  Far away is close at hand in the images of elsewhere.
  Anon.