Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: Select records that do not duplicate on a certain field.

    I am looking for some help in pulling certain people out of a table

    This is the basic setup of my table


    PK ID NAME MEETING
    1 11111 Joe CLASS98
    2 22222 Jane CLASS98
    3 33333 Bob CLASS98
    4 11111 Joe CLASS04
    5 22222 Jane CLASS04
    6 44444 Sally CLASS04

    What I am wanting to do is Select only the people who attended CLASS98 but didn't attend CLASS04

    I could just remove the CLASS04 people by sorting if I could just return the rows that do not duplicate the ID and meeting is either CLASS04 or CLASS98

    Either way would get me to my goal.

    Any help is GREATLY appreciated.

    Thanks
    Jimmyjoe

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT * FROM yourTable o
    WHERE Meeting = 'CLASS98'
    AND NOT EXISTS (
    SELECT * FROM yourTable i WHERE i/PK = o.PK AND Meeting = 'CLASS04')
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Posts
    6
    Thanks Brett this looks like what I am needing to do.

    But in looking a little deeper I see that I need to join tables to get the info sorted that way and I just need a little help on the syntax. I don't know how to add the join information to the query.

    Come to find out this is the actual order. What I was working with was a recordset that was created. Sorry for the confusion

    Order_Meet
    PK ORDER_NUMBER MEETING
    1 999999 CLASS98
    2 888888 CLASS98
    3 777777 CLASS98
    4 666666 CLASS04
    5 555555 CLASS04
    6 444444 CLASS04

    Orders
    PK ID NAME ORDER_NUMBER
    1 11111 Joe 999999
    2 22222 Jane 888888
    3 33333 Bob 777777
    4 11111 Joe 666666
    5 22222 Jane 555555
    6 44444 Sally 444444

    So this is what I have and I am getting errors.

    SELECT * FROM { oj SDP.dbo.Orders Orders INNER JOIN SDP.dbo.Order_Meet Order_Meet ON
    Orders.ORDER_NUMBER = Order_Meet.ORDER_NUMBER} o
    WHERE Meeting = 'CLASS98'
    AND NOT EXISTS (
    SELECT * FROM { oj Orders Orders INNER JOIN Order_Meet Order_Meet ON
    Orders.ORDER_NUMBER = Order_Meet.ORDER_NUMBER} i WHERE i.bt_id = o.bt_id AND Meeting = 'CLASS04')

    This is the error.

    Error: Line 2: Incorrect syntax near 'o'.
    More exceptions ... Line 6: Incorrect syntax near 'i'.

    What am I missing?

    Thanks SO much for your help...


    Jimmyjoe
    Last edited by Jimmyjoe; 03-16-04 at 15:24.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Gotta run...but try AS o

    and AS i
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    because, hey, there a margarita with my name on it...

    You've made a derived table...so you need AS i...

    WHY you're doing it that way, I don't know....I'll check it out in the am...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Mar 2004
    Posts
    6
    Thanks for your help. Still no dice

    Error: Incorrect syntax near the keyword 'as'.
    More exceptions ... Incorrect syntax near the keyword 'as'.

    How else should I do the query? Anyone else have any ideas?

    Thanks,
    Jimmyjoe

  7. #7
    Join Date
    Mar 2004
    Posts
    45
    SELECT [Name]
    FROM Order_Meet m JOIN Orders o ON m.Order_Number = o.Order_Number
    WHERE o.Meeting = 'CLASS98' AND NOT EXISTS
    (SELECT *
    FROM Order_Meet e JOIN Orders r ON e.Order_Number = r.Order_Number
    WHERE e.id = m.id AND r.Meeting = 'CLASS04')

    Hans.

  8. #8
    Join Date
    Mar 2004
    Posts
    6
    Thanks for your help HansVE Unfortunately it still doesnt work.

    I get this error message
    Error: Invalid column name 'Meeting'.

    the Meeting column is in the Order_Meet Table
    and the name and Id is in the Orders Table

    How do i incorporate those into what you wrote??

    Thanks again for all your help Hans and Brett!!!


    Jimmyjoe

  9. #9
    Join Date
    Mar 2004
    Posts
    6
    I GOT IT!!!!

    All credit going to Hans and Brett of course..

    SELECT *

    FROM Order_Meet m JOIN Orders o ON m.Order_Number = o.Order_Number

    WHERE
    m.MEETING = 'CLASS98' AND NOT EXISTS
    (SELECT *
    FROM Order_Meet e JOIN Orders r ON e.Order_Number = r.Order_Number
    WHERE r.bt_id = o.bt_id AND e.Meeting = 'CLASS04')

    Hans you had some of the prefixes swaped around.


    Thank you so much guys...!!!!


    Jimmyjoe

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It should be just Bob, right?

    I was trying this....ans I'm not sure why it's not working...

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (PK int, ORDER_NUMBER int, MEETING varchar(10))
    GO
    
    INSERT INTO myTable99 (PK, ORDER_NUMBER, MEETING)
    SELECT 1, 999999, 'CLASS98' UNION ALL
    SELECT 2, 888888, 'CLASS98' UNION ALL
    SELECT 3, 777777, 'CLASS98' UNION ALL
    SELECT 4, 666666, 'CLASS04' UNION ALL
    SELECT 5, 555555, 'CLASS04' UNION ALL
    SELECT 6, 444444, 'CLASS04'
    GO
    
    CREATE TABLE myTable00 (PK int, [ID] int, [NAME] varchar(10), ORDER_NUMBER int)
    GO
    
    INSERT INTO myTable00 (PK, [ID], [NAME], ORDER_NUMBER)
    SELECT 1, 11111, 'Joe',   999999 UNION ALL
    SELECT 2, 22222, 'Jane',  888888 UNION ALL
    SELECT 3, 33333, 'Bob',   777777 UNION ALL
    SELECT 4, 11111, 'Joe',   666666 UNION ALL
    SELECT 5, 22222, 'Jane',  555555 UNION ALL
    SELECT 6, 44444, 'Sally', 444444
    GO
    
        SELECT * 
          FROM myTable00 a 
    INNER JOIN myTable99 b 
    	ON a.PK = b.PK
         WHERE EXISTS (    SELECT * 
    		         FROM myTable99 c 
    		        WHERE MEETING = 'CLASS98' 
    		          AND b.ORDER_NUMBER = c.ORDER_NUMBER)   
         AND NOT EXISTS (  SELECT * 
    		         FROM myTable99 c 
    		        WHERE MEETING = 'CLASS04' 
    		         AND b.ORDER_NUMBER = c.ORDER_NUMBER) 
    GO
    
    DROP TABLE myTable99
    DROP TABLE myTable00
    GO
    I don't know why I'm getting Jane and Joe....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do me a favor and post what you got...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Mar 2004
    Posts
    6
    I did.

    It is a couple of posts above.


    The Id field is different. it should be id instead of bt_id.

    That is the link between the tables not the PK

    Jimmyjoe
    Last edited by Jimmyjoe; 03-16-04 at 17:35.

Posting Permissions

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