locked
multiple lookup columns between tables RRS feed

  • Question

  • Hello.

    I am trying to develop a database in SQL to track computer inventory. To start off with, I have tables for locations, data jacks and patch panels. These tables are populated with some data. I can get a query to correctly display the list of data jacks and what locations they are in, or a list of which patch panels are in which locations. The issue that I am having comes from trying to return 2 different locations from 2 different fields in the same record.

    Here are the related tables/fields:

    Data jacks: ID, data jack, location_id (for the location of the data jack itself), closet_id (for the location of the closet that the data jack is wired to)

    Locations: ID, location (room name such as '001')

    If the data jack has a record with the following fields: ID=1, data jack=001, location_id=1, closet_id=2

    and the locations table has 2 records with the following fields: ID=1, location=001; ID=2, location=500

    how do I get the query to return datajack=001, closet=500?

    I've tried various combinations of derived tables, but they always seem to get disassociated (the data jacks will list correctly, but the closet info will be wrong).

    Thanks for any advice!

    Monday, January 12, 2015 7:19 PM

Answers

  • You'll want two left joins to accomplish this. Here is an example:

    DECLARE @Locations  as TABLE (ID int, Location nvarchar(50))
    INSERT INTO @Locations Values (1, '001')
    INSERT INTO @Locations Values (2, '500')
    INSERT INTO @Locations Values (3, '610')
    INSERT INTO @Locations Values (4, '700')
    
    DECLARE @DataJacks as TABLE (ID int, DataJack nvarchar(50), LocationId int, ClosetID int)
    INSERT INTO  @DataJacks VALUES (1, '001',1,2)
    INSERT INTO  @DataJacks VALUES (2, '002',3,4)
    
    SELECT lj.Location as DataJack, lc.Location as Closet
    FROM @DataJacks j LEFT JOIN
    @Locations lj ON j.LocationID =lj.ID LEFT JOIN
    @Locations lc ON j.ClosetID =lc.ID;


    Martina White

    • Marked as answer by lavinrc Tuesday, January 13, 2015 4:29 PM
    Monday, January 12, 2015 10:49 PM
  • Try this:

    DECLARE @jacks TABLE (jackID INT, closetID INT, locationID INT)
    DECLARE @closets TABLE (closetID INT, locationID INT)
    DECLARE @locations TABLE (locationID INT, locationDescription VARCHAR(100))
    
    INSERT INTO @jacks (jackID, closetID, locationID) VALUES (1,1,1),(2,1,1),(3,1,1),(4,1,1),(5,1,2),(6,1,2),(7,1,2),(8,1,2),(9,2,3),(10,2,3),(11,2,3),(12,2,3),(13,3,4),(14,3,4),(15,3,4),(16,3,4)
    INSERT INTO @closets (closetID, locationID) VALUES (1,1),(2,2),(3,3)
    INSERT INTO @locations (locationID, locationDescription) VALUES (1, 'Server Room'),(2, 'IT Dept'),(3, 'Accounting'),(4, 'Admin')
    
    SELECT *
      FROM @jacks j
        INNER JOIN @closets c
    	  ON j.closetID = c.closetID
    	INNER JOIN @locations jl
    	  ON j.locationID = jl.locationID
    	INNER JOIN @locations cl
    	  ON c.locationID = cl.locationID
    
    • Marked as answer by lavinrc Tuesday, January 13, 2015 4:28 PM
    Tuesday, January 13, 2015 2:18 PM

All replies

  • Please post DDL. It will be a lot easier to help you this way.

    Thanks

    Carl

    Monday, January 12, 2015 8:11 PM
  • This should do it:

    SELECT

    DataJacks.id,Locations.Location


    FROM

    DataJacks LEFTJOINLocations ONDataJacks.ClosetID =Locations.ID;

    Thanks


    Kind regards, Kev

    Monday, January 12, 2015 8:19 PM
  • You'll want two left joins to accomplish this. Here is an example:

    DECLARE @Locations  as TABLE (ID int, Location nvarchar(50))
    INSERT INTO @Locations Values (1, '001')
    INSERT INTO @Locations Values (2, '500')
    INSERT INTO @Locations Values (3, '610')
    INSERT INTO @Locations Values (4, '700')
    
    DECLARE @DataJacks as TABLE (ID int, DataJack nvarchar(50), LocationId int, ClosetID int)
    INSERT INTO  @DataJacks VALUES (1, '001',1,2)
    INSERT INTO  @DataJacks VALUES (2, '002',3,4)
    
    SELECT lj.Location as DataJack, lc.Location as Closet
    FROM @DataJacks j LEFT JOIN
    @Locations lj ON j.LocationID =lj.ID LEFT JOIN
    @Locations lc ON j.ClosetID =lc.ID;


    Martina White

    • Marked as answer by lavinrc Tuesday, January 13, 2015 4:29 PM
    Monday, January 12, 2015 10:49 PM
  • Try this:

    DECLARE @jacks TABLE (jackID INT, closetID INT, locationID INT)
    DECLARE @closets TABLE (closetID INT, locationID INT)
    DECLARE @locations TABLE (locationID INT, locationDescription VARCHAR(100))
    
    INSERT INTO @jacks (jackID, closetID, locationID) VALUES (1,1,1),(2,1,1),(3,1,1),(4,1,1),(5,1,2),(6,1,2),(7,1,2),(8,1,2),(9,2,3),(10,2,3),(11,2,3),(12,2,3),(13,3,4),(14,3,4),(15,3,4),(16,3,4)
    INSERT INTO @closets (closetID, locationID) VALUES (1,1),(2,2),(3,3)
    INSERT INTO @locations (locationID, locationDescription) VALUES (1, 'Server Room'),(2, 'IT Dept'),(3, 'Accounting'),(4, 'Admin')
    
    SELECT *
      FROM @jacks j
        INNER JOIN @closets c
    	  ON j.closetID = c.closetID
    	INNER JOIN @locations jl
    	  ON j.locationID = jl.locationID
    	INNER JOIN @locations cl
    	  ON c.locationID = cl.locationID
    
    • Marked as answer by lavinrc Tuesday, January 13, 2015 4:28 PM
    Tuesday, January 13, 2015 2:18 PM
  • Thanks Patrick! With a little tweaking I was able to figure out what you were doing and make it work for my application. Much appreciated!
    Tuesday, January 13, 2015 4:29 PM