none
Retrieve info based on a list

    Question

  • I have a list of names and a separate address book. The address book contains the fields Name, Location and Title.The address book is a superset of the list of names.

    I need to display the Location and Title fields from the address book for only the names that are in the list of names. I am befuddled how to do this. Can someone point me in the right direction?

    Friday, January 17, 2014 11:45 PM

Answers

  • SELECT Location, Title
    FROM   AddressBook AB
    WHERE  EXISTS (SELECT *
                   FROM   ListOfNames LN
                   WHERE  AB.Name = LN.Name)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, January 18, 2014 11:10 AM

All replies

  • As an example, I have considered two table variables instead of actual tables. Replace the table variables with your actual table names and use.
    DECLARE @Contacts TABLE
    (
    ContactID INT,
    ContactName VARCHAR(25)
    )

    DECLARE @AddressBook TABLE
    (
    AddressID INT,
    ContactID INT,
    Location VARCHAR(10),
    Title VARCHAR(10)
    )

    INSERT INTO @Contacts(ContactID, ContactName) VALUES(1, 'John')
    INSERT INTO @Contacts(ContactID, ContactName) VALUES(2, 'Mark')
    INSERT INTO @Contacts(ContactID, ContactName) VALUES(3, 'Ryan')

    INSERT INTO @AddressBook(AddressID, ContactID, Location, Title) VALUES(1, 1,'Dallas', 'CEO')
    INSERT INTO @AddressBook(AddressID, ContactID, Location, Title) VALUES(2, 2,'Atlanta', 'Manager')
    INSERT INTO @AddressBook(AddressID, ContactID, Location, Title) VALUES(3, 3,'Seattle', 'Director')
    INSERT INTO @AddressBook(AddressID, ContactID, Location, Title) VALUES(4, 4,'Austin', 'Clerk')

    SELECT b.ContactName, a.Location, a.Title
    FROM @AddressBook a
    INNER JOIN @Contacts b ON a.ContactID = b.ContactID

    This query will pull the Location and Title fields based on the list of names present in the contacts table.

    More Sql related posts @ http://www.sqlsaga.com

    • Proposed as answer by sqlsaga Saturday, January 18, 2014 1:07 AM
    Saturday, January 18, 2014 1:07 AM
  • SELECT Location, Title
    FROM   AddressBook AB
    WHERE  EXISTS (SELECT *
                   FROM   ListOfNames LN
                   WHERE  AB.Name = LN.Name)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, January 18, 2014 11:10 AM
  • >> I have a list of names and a separate address book. The address book contains the fields [sic] Name, Location and Title. The address book is a superset of the list of names. <<

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Columns are nothing like fields! In SQL a field is part of a temporal value (year, month, day, hour, minute, second). The data elements are not named properly. There is no generic “name” in RDBMS; it has to be “<something in particular>_name” by ISO-11179 rules and common sense. Likewise, “<something in particular>_location” and “<something in particular>_title” are needed. 

    >> I need to display the Location and Title fields [sic] from the address book for only the names that are in the list of names. I am befuddled how to do this. Can someone point me in the right direction? <<

    No table names, no DDL, a bad narrative, and no specs! What can we use to help you? Here is a guess: 

    SELECT foobar_location, foobar_title
      FROM Address_Book AS A
     WHERE EXISTS
          (SELECT * 
             FROM Known_Prostitutes AS P
            WHERE P.foobar_location = A.foobar_location
              AND P.foobar_title = A.foobar_title);

    Since you did not bother with table names, I decided that “Known_Prostitutes” would be more fun than a vague “list of names” that you had. 

    You clearly need to read a book on basic RDBMS; you are missing fundamentals and a forum is not the place to lean them. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, January 18, 2014 3:54 PM
  • You are clearly overthinking this, but thank you for berating me publicly. I truly appreciate it.
    Saturday, January 18, 2014 11:21 PM