Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Question Unanswered: A T-SQL question (URGENT)...

    Hi,
    I have the following two tables:

    Table_1
    ITEM BAD_LIST
    ===============
    A 2,3,6
    B 1,4
    C 3
    D 2,6
    E 1,7

    Table_2
    LIST_NO
    ====
    2
    4

    I want to retrieve the items in Table_1 but each item MUST check the BAD_LIST which are not in Table_2.
    The expected result on the above selection should return: B, C, E only.

    How to write this statement /store procedure/ function to completed this task?
    *The LIST_NO in TABLE_2 is a result returned by a function now.

    Thanks!

  2. #2
    Join Date
    Apr 2007
    Posts
    183
    Why B?
    It has number 4 on it's list.
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Table_1.item
      FROM Table_1
    LEFT OUTER
      JOIN Table_2
        ON ',' + Table_1.bad_list + ',' 
      LIKE '%,' + Table_2.list_no + ',%'       
     WHERE Table_2.list_no IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2009
    Posts
    3
    Quote Originally Posted by Peso
    Why B?
    It has number 4 on it's list.
    Oops! You are right. Why B?

  5. #5
    Join Date
    Jul 2009
    Posts
    3
    Quote Originally Posted by r937
    Code:
    SELECT Table_1.item
      FROM Table_1
    LEFT OUTER
      JOIN Table_2
        ON ',' + Table_1.bad_list + ',' 
      LIKE '%,' + Table_2.list_no + ',%'       
     WHERE Table_2.list_no IS NULL
    You are so cool! Million thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •