locked
List from one table not in another RRS feed

  • Question

  • I am trying to write a query that will give me items from a column in 1 table that are not in the column of another. i.e.  I only care about the 1 column from each of the tables below.  It doesn't matter what is in the other columns of either table.

    Table 1  - ColumnTbl1
    A
    B
    C
    D

    Table 2 - ColumnTbl2
    A
    C
    E

    So I would want B and D returned from table 1


    *** Please allow me to mark threads as answered and I will, Thank you ***
    Wednesday, May 13, 2009 8:43 PM

Answers

  • The predicates in the WHERE clause turn the outer join to inner join,
    maybe you mean this:

    SELECT item
    FROM item
    LEFT OUTER JOIN tpackage
    ON item.item = tpackage.pkg_id
    AND tpackage.product_line = 'PRESSING'
    WHERE tpackage.pkg_id IS NULL;

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Thursday, May 14, 2009 12:50 PM

All replies

  • try this.

    select a.ColumnTbl1
    FROM Table1 a
    LEFT OUTER JOIN Table2 b
    on a.ColumnTbl1 = b.ColumnTbl2
    WHERE b.ColumnTbl2 IS NULL

    • Proposed as answer by sqlmania Wednesday, May 13, 2009 8:58 PM
    Wednesday, May 13, 2009 8:47 PM
  • You can also use:

    select c1
    from t1
    where not exists (select * from t2 where t2.c1 = t1.c1);

    -- or

    (select c1 from t1)
    except
    (select c1 from t2);
    go

    The second  statement will consider NULL values. You will need to re-write the first one if you want to consider NULL values also.


    AMB

    Wednesday, May 13, 2009 11:50 PM
  • Here is the actual query I am working with (thanks to your help) but it is still returning rows I don't want

    SELECT item
    FROM item
    LEFT OUTER JOIN tpackage
    	ON item.item = tpackage.pkg_id 	
    WHERE tpackage.pkg_id IS not NULL 
    AND tpackage.product_line = 'PRESSING'
    I checked one of the results I got back
    PF_PKG_ID_100

    and it was listed in both tables.
    *** Please allow me to mark threads as answered and I will, Thank you ***
    Thursday, May 14, 2009 12:41 PM
  • The predicates in the WHERE clause turn the outer join to inner join,
    maybe you mean this:

    SELECT item
    FROM item
    LEFT OUTER JOIN tpackage
    ON item.item = tpackage.pkg_id
    AND tpackage.product_line = 'PRESSING'
    WHERE tpackage.pkg_id IS NULL;

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Thursday, May 14, 2009 12:50 PM
  • I am ONLY getting null values for Product_line now
    *** Please allow me to mark threads as answered and I will, Thank you ***
    Thursday, May 14, 2009 1:00 PM
  • ski_freak,

    Are you having problems marking your questions as answered?

    There should be a button next to each post 'Mark As Answer' or something like that...?

    Paul
    Thursday, May 14, 2009 1:01 PM
  • After looking at the data more in the tpackage table there could be multiple rows with the same pkg_id. I don't know if that makes a difference in the query
    *** Please allow me to mark threads as answered and I will, Thank you ***
    Thursday, May 14, 2009 1:05 PM
  • Paul,

    No I am not.  Thanks for asking.
    *** Please allow me to mark threads as answered and I will, Thank you ***
    Thursday, May 14, 2009 1:14 PM
  • Can you post CREATE TABLE statements, sample data and expected results?
    Your request was for items in one table that are not in the other. Why
    do you expect the Product_line to be anything other than NULL, those are
    the non-matching rows so it is supposed to be NULL.

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Thursday, May 14, 2009 1:14 PM
  • Paul,

    No I am not.  Thanks for asking.
    *** Please allow me to mark threads as answered and I will, Thank you ***
    Cool.  It's just it says so in your signature ^^^^

    :c)
    Thursday, May 14, 2009 1:18 PM
  • SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[item](
    	[item] [dbo].[ItemType] NOT NULL
    	
     CONSTRAINT [PK_item] PRIMARY KEY CLUSTERED 
    (
    	[item] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY],
     CONSTRAINT [IX_item_item_lowdate] UNIQUE NONCLUSTERED 
    (
    	[item] ASC,
    	[lowdate] ASC,
    	[rcpt_rqmt] ASC,
    	[RowPointer] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY],
     CONSTRAINT [IX_item_level] UNIQUE NONCLUSTERED 
    (
    	[low_level] ASC,
    	[item] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY],
     CONSTRAINT [IX_item_plan] UNIQUE NONCLUSTERED 
    (
    	[plan_code] ASC,
    	[item] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY],
     CONSTRAINT [IX_item_product] UNIQUE NONCLUSTERED 
    (
    	[product_code] ASC,
    	[item] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY],
     CONSTRAINT [IX_item_RowPointer] UNIQUE NONCLUSTERED 
    (
    	[RowPointer] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY],
     CONSTRAINT [IX_item_serial] UNIQUE NONCLUSTERED 
    (
    	[serial_tracked] ASC,
    	[item] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY],
     CONSTRAINT [IX_item_use_reorder_point] UNIQUE NONCLUSTERED 
    (
    	[use_reorder_point] ASC,
    	[item] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING ON
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tpackage](
    	[pkg_id] [dbo].[ItemType] NOT NULL,	
    	[product_line] [char](20) NOT NULL,
    	[active] [char](1) NULL,
    	[container_type] [nvarchar](30) NULL
    	
     CONSTRAINT [PK_tpackage] PRIMARY KEY CLUSTERED 
    (
    	[pkg_id] ASC,
    	[pkg_rev] ASC,
    	[product_line] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 25) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING ON
    Item column in item table
    PF_PKG_ID_100
    PF_PKG_ID_1000
    PF_PKG_ID_1001
    PF_PKG_ID_1002
    PF_PKG_ID_1003
    PF_PKG_ID_1004
    PF_PKG_ID_1005
    PF_PKG_ID_1007
    PF_PKG_ID_1008
    PF_PKG_ID_1009

    pkg_id, product line columns in tpackage table
    PF_PKG_ID_100     PRESSING                     
    PF_PKG_ID_1002   PRESSING           
    PF_PKG_ID_1003   PRESSING           
    PF_PKG_ID_1003   PRESSING           
    PF_PKG_ID_1004   PRESSING           
    PF_PKG_ID_1005   PRESSING           
    PF_PKG_ID_1007   PRESSING           
    PF_PKG_ID_1007   PRESSING           

    In this scenario I would only want
    PF_PKG_ID_1000
    PF_PKG_ID_1001
     returned as they are in the item table but not in the tpackage table

    I hope this helps.
    *** Please allow me to mark threads as answered and I will, Thank you ***
    Thursday, May 14, 2009 1:30 PM
  • Well now I'm confused.

    Given that data, what output do you get from Plamen's query:

    SELECT item
    FROM item
    LEFT OUTER JOIN tpackage
    ON item.item = tpackage.pkg_id 
    AND tpackage.product_line = 'PRESSING'
    WHERE tpackage.pkg_id IS NULL;
    ???
    Thursday, May 14, 2009 1:46 PM
  • The query that I posted returns these rows:

    item
    --------------------
    PF_PKG_ID_1000
    PF_PKG_ID_1001
    PF_PKG_ID_1008
    PF_PKG_ID_1009

    It is two more rows that you expect, but the last two are really correct
    because they do no exist in the second table.

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Thursday, May 14, 2009 1:46 PM
  • Yes I missed that 1008 and 1009 are supposed to be returned as well. 

    Ok after playing some more myself.  When I run both of these queries

    SELECT DISTINCT item FROM item WHERE item LIKE 'PF_PKG_ID_%'
    
    SELECT DISTINCT pkg_id FROM tpackage WHERE pkg_id LIKE 'PF_PKG_ID_%' 

    I get 10 more rows from the item table than the tproduct table.  (Those are the rows I am trying to return)

    This query gives me 2 of those.  I think were close (the like statement is the same as product_line = 'PRESSING' as PF stands for PRESSING)

    SELECT item , product_line
    FROM item
    LEFT OUTER JOIN tpackage
    ON item.item = tpackage.pkg_id 
    WHERE tpackage.pkg_id IS NULL
    AND item LIKE 'PF_PKG_ID_%'

    *** Please allow me to mark threads as answered and I will, Thank you ***
    Thursday, May 14, 2009 2:26 PM
  • OK I just compared the data side by side and 2 rows is all I am supposed to get.  Thanks for all the help! 
    *** Please allow me to mark threads as answered and I will, Thank you ***
    Thursday, May 14, 2009 2:42 PM
  • Try this:

    SELECT item
    FROM item
    WHERE item LIKE 'PF_PKG_ID_%'
    EXCEPT
    SELECT pkg_id
    FROM tpackage
    WHERE pkg_id LIKE 'PF_PKG_ID_%';

    or this:

    SELECT DISTINCT item
    FROM item
    WHERE item LIKE 'PF_PKG_ID_%'
    AND NOT EXISTS(SELECT *
    FROM tpackage
    WHERE item = pkg_id);

    The column product_line does not make sense in the SELECT list as it
    will be always NULL (it is from the table your are excluding).

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Thursday, May 14, 2009 2:42 PM