Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    54

    Unanswered: items in list A that don't appear in list B (was "Simple Query...I think")

    Ok, I want to write a stored procedure / query that says the following:
    Code:
    If any of the items in list 'A' also appear in list 'B' --return false
    If none of the items in list 'A' appear in list 'B' --return true


    In pseudo-SQL, I want to write a clause like this

    Code:

    IF
    (SELECT values FROM tableA) IN(SELECT values FROM tableB)
    Return False
    ELSE
    Return True


    Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.

    I may have to achieve this with some kind of logical loop but I don't know how to do that.

    Can anyone help?

  2. #2
    Join Date
    Apr 2003
    Posts
    54
    OK so it wasnt' so simple...at least MY solution isn't:

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO


    ALTER PROC sp_get_trolley_inconsistencies
    @grower CHAR(2),
    @load_id INT
    AS

    DECLARE @ID int,
    @TrolleyList varchar(300),
    @Date DATETIME
    --Get the relevant date from the DB Table
    SELECT @Date = (SELECT CONVERT(DATETIME, rl_eta, 102) FROM requi_load WHERE rl_id = @load_id)

    SET @TrolleyList = ''
    --Get the list of values into a comma delimited string
    DECLARE crs_Trolleys CURSOR
    FOR SELECT DISTINCT ldl_trolley
    FROM load_detail_lines
    WHERE ldl_requi_load_id = @load_id

    OPEN crs_Trolleys
    FETCH NEXT FROM crs_Trolleys INTO @ID

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SELECT @TrolleyList = @TrolleyList+CAST(@ID AS varchar(5))+ ', '
    FETCH NEXT FROM crs_Trolleys INTO @ID
    END

    SET @TrolleyList = SUBSTRING(@TrolleyList,1,DATALENGTH(@TrolleyList)-2)

    CLOSE crs_Trolleys
    DEALLOCATE crs_Trolleys

    --Parse the string and run the 'IN' statement on each of the Parsed Values

    DECLARE @parsingList VARCHAR(300)
    DECLARE @find_comma INT
    DECLARE @trolleytocheck VARCHAR(4)

    SELECT @parsingList = @TrolleyList

    WHILE @parsingList IS NOT NULL

    BEGIN
    SELECT @find_comma = PATINDEX('%,%',@parsingList)
    IF @find_comma <> 0
    BEGIN
    SELECT @trolleytocheck = SUBSTRING(@parsingList,1,(@find_comma-1))
    SELECT @parsingList = LTRIM(SUBSTRING(@parsingList,(@find_comma+1),300))
    PRINT @trolleytocheck
    IF @trolleytocheck IN(SELECT distinct ldl_trolley
    FROM load_detail_lines, requi_load
    WHERE ldl_requi_load_id = rl_id
    AND ldl_requi_load_id NOT LIKE @load_id
    AND rl_status = 1
    AND DAY(rl_eta) = DAY(@Date)
    AND MONTH(rl_eta) = MONTH(@Date)
    AND YEAR(rl_eta) = YEAR(@Date))
    BEGIN
    RAISERROR 50001 'Error! You screwed up, trolley '
    END
    CONTINUE
    END
    ELSE
    BEGIN
    --this block finds the last value in the trolley list
    SELECT @trolleytocheck = @parsingList
    SELECT @parsingList = null
    PRINT @trolleytocheck
    BREAK
    END
    END

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    If anyone can suggest anything more elegant...please let me know.

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    Why not join the tables and count the rows that match?

  4. #4
    Join Date
    Apr 2003
    Posts
    54
    Doh!

    *Commits ritual suicide*

Posting Permissions

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