[RESOLVED] trying to find if a item does not exist in another table doesn't work!!-VBForums
Results 1 to 23 of 23

Thread: [RESOLVED] trying to find if a item does not exist in another table doesn't work!!

  1. #1

    Thread Starter
    baila como nunca
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,400

    Resolved [RESOLVED] trying to find if a item does not exist in another table doesn't work!!

    hey,
    i have 2 tables
    EmployeSales
    PriceList
    now,im trying to see if an item from EmployeSales
    exist in PriceList
    but it dosnt give me the right query why?
    what i am missing?
    this is my code
    Code:
    SELECT EmployeSales.EmpItem, PriceList.PriceName
    FROM EmployeSales INNER JOIN PriceList ON EmployeSales.EmpItem = PriceList.PriceName
    Where EmpItem not in (Select EmpItem From PriceList)
    GROUP BY EmployeSales.EmpItem, PriceList.PriceName
    tnx for any help
    salsa

  2. #2
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    739

    Re: trying to find if a item does not exist in another table doesn't work!!

    Hi Salsa,

    well if you sell something it should be in your Pricelist ?
    now,im trying to see if an item from EmployeSales
    exist in PriceList
    An Employee can't sell somthing that isn't there. or can they just
    enter some Product with a price ?

    you will have to explain a bit more

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,003

    Re: trying to find if a item does not exist in another table doesn't work!!

    Logical considerations aside I think a join might do it for you. MS SQL

    Code:
    create table #EmployeSales(EmpItem int)
    insert into #EmployeSales(EmpItem) values(1)
    insert into #EmployeSales(EmpItem) values(2)
    insert into #EmployeSales(EmpItem) values(3)
    insert into #EmployeSales(EmpItem) values(4)
    insert into #EmployeSales(EmpItem) values(5)
    
    create table #PriceList(EmpItem int, PriceName Varchar(10))
    insert into #PriceList(EmpItem,PriceName) values(1,'one')
    insert into #PriceList(EmpItem,PriceName) values(4,'four')
    
    SELECT EmployeSales.EmpItem, PriceList.PriceName
    FROM #EmployeSales EmployeSales
    join #PriceList PriceList on EmployeSales.EmpItem= PriceList.EmpItem
    GROUP BY EmployeSales.EmpItem, PriceList.PriceName
    In order to understand recursion, one must first understand recursion….

  4. #4

    Thread Starter
    baila como nunca
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,400

    Re: trying to find if a item does not exist in another table doesn't work!!

    Quote Originally Posted by ChrisE View Post
    Hi Salsa,

    well if you sell something it should be in your Pricelist ?

    An Employee can't sell somthing that isn't there. or can they just
    enter some Product with a price ?

    you will have to explain a bit more

    regards
    Chris
    hey chris
    yes
    they can enter an item that dosnt belong in the pricelist

  5. #5

    Thread Starter
    baila como nunca
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,400

    Re: trying to find if a item does not exist in another table doesn't work!!

    Quote Originally Posted by TysonLPrice View Post
    Logical considerations aside I think a join might do it for you. MS SQL

    Code:
    create table #EmployeSales(EmpItem int)
    insert into #EmployeSales(EmpItem) values(1)
    insert into #EmployeSales(EmpItem) values(2)
    insert into #EmployeSales(EmpItem) values(3)
    insert into #EmployeSales(EmpItem) values(4)
    insert into #EmployeSales(EmpItem) values(5)
    
    create table #PriceList(EmpItem int, PriceName Varchar(10))
    insert into #PriceList(EmpItem,PriceName) values(1,'one')
    insert into #PriceList(EmpItem,PriceName) values(4,'four')
    
    SELECT EmployeSales.EmpItem, PriceList.PriceName
    FROM #EmployeSales EmployeSales
    join #PriceList PriceList on EmployeSales.EmpItem= PriceList.EmpItem
    GROUP BY EmployeSales.EmpItem, PriceList.PriceName
    no inner join?

  6. #6
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,003

    Re: trying to find if a item does not exist in another table doesn't work!!

    Quote Originally Posted by salsa31 View Post
    no inner join?
    That is an inner join. Join = Inner Join.
    In order to understand recursion, one must first understand recursion….

  7. #7
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    739

    Re: trying to find if a item does not exist in another table doesn't work!!

    Quote Originally Posted by salsa31 View Post
    hey chris
    yes
    they can enter an item that dosnt belong in the pricelist
    you should think about that ! things will get out of hand

    regards
    chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8

    Thread Starter
    baila como nunca
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,400

    Re: trying to find if a item does not exist in another table doesn't work!!

    Quote Originally Posted by TysonLPrice View Post
    That is an inner join. Join = Inner Join.
    i will try that
    tnx T

  9. #9

    Thread Starter
    baila como nunca
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,400

    Re: trying to find if a item does not exist in another table doesn't work!!

    Quote Originally Posted by ChrisE View Post
    you should think about that ! things will get out of hand

    regards
    chris
    i know but this now exsits
    what can i do?

  10. #10
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,357

    Re: trying to find if a item does not exist in another table doesn't work!!

    Manually fix your tables...get them matching up as they should. ChrisE has a very valid point.

    Sam

  11. #11
    Fanatic Member
    Join Date
    Apr 2012
    Posts
    906

    Re: trying to find if a item does not exist in another table doesn't work!!

    I'm confused, looking at your SQL, Salsa...

    You are joing EmployeSales to PriceList where EmpItem=PriceName and yet, later on (in your sub-query), you are looking for EmpItem in the PriceList table!

    So, if EmpItem is in the PriceList table, why are you not joining on that?

    I think you need to explain how the two tables relate to one another, if you want help; I suspect that you can achieve what you need with a simple 'NOT IN' type query.
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  12. #12
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,003

    Re: trying to find if a item does not exist in another table doesn't work!!

    Quote Originally Posted by ColinE66 View Post
    I'm confused, looking at your SQL, Salsa...

    You are joing EmployeSales to PriceList where EmpItem=PriceName and yet, later on (in your sub-query), you are looking for EmpItem in the PriceList table!

    So, if EmpItem is in the PriceList table, why are you not joining on that?

    I think you need to explain how the two tables relate to one another, if you want help; I suspect that you can achieve what you need with a simple 'NOT IN' type query.
    I believe I posted the solution...the question is the logical use of the table - IMHO.
    In order to understand recursion, one must first understand recursion….

  13. #13

    Thread Starter
    baila como nunca
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,400

    Re: trying to find if a item does not exist in another table doesn't work!!

    tnx all for your help
    tyson gave me the idea

  14. #14
    Fanatic Member
    Join Date
    Apr 2012
    Posts
    906

    Re: trying to find if a item does not exist in another table doesn't work!!

    Quote Originally Posted by TysonLPrice View Post
    I believe I posted the solution...the question is the logical use of the table - IMHO.
    Hmmm, OK. Assuming yor understanding of Salsa's data is correct, he could also use

    SELECT EmpItem, PriceName FROM PriceList WHERE EmpItem IN (SELECT EmpItem FROM EmployeSales) GROUP BY EmpItem, PriceName

    Should be faster...
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  15. #15
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    739

    Re: [RESOLVED] trying to find if a item does not exist in another table doesn't work!

    Hi Colin,

    there cant be a result in the SQL as the Item with Price is not in the List(Table). The User
    entered the Data = Item and Price somhow to a Listview or FlexGrid or...

    Tyson put him on the right Track.

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  16. #16
    Fanatic Member
    Join Date
    Apr 2012
    Posts
    906

    Re: [RESOLVED] trying to find if a item does not exist in another table doesn't work!

    Quote Originally Posted by ChrisE View Post
    Hi Colin,

    there cant be a result in the SQL as the Item with Price is not in the List(Table). The User
    entered the Data = Item and Price somhow to a Listview or FlexGrid or...

    Tyson put him on the right Track.

    regards
    Chris
    I believe my SQL is functionally equivalent to Tyson's so I'm not sure I understand the point you are trying to make?
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  17. #17
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,003

    Re: [RESOLVED] trying to find if a item does not exist in another table doesn't work!

    I try and stay away from "Where In" and prefer "If Exists". The latter performs much better.
    In order to understand recursion, one must first understand recursion….

  18. #18
    Fanatic Member
    Join Date
    Apr 2012
    Posts
    906

    Re: [RESOLVED] trying to find if a item does not exist in another table doesn't work!

    Quote Originally Posted by TysonLPrice View Post
    I try and stay away from "Where In" and prefer "If Exists". The latter performs much better.
    Yes, me too. I always use EXISTS but was keeping is simple. Matter of fact there are gotchas with NULLS when using NOT IN versus NOT EXISTS

    http://weblogs.sqlteam.com/mladenp/a.../18/60210.aspx
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  19. #19
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    739

    Re: [RESOLVED] trying to find if a item does not exist in another table doesn't work!

    Hi Colin,

    you SQL will work also, the Point is in Post#3 Tyson showed how to setup the Tables.

    salsa in Post#4
    hey chris
    yes
    they can enter an item that dosnt belong in the pricelist
    somehow the user can enter Data that isn't in the Table yet.

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  20. #20
    Fanatic Member
    Join Date
    Apr 2012
    Posts
    906

    Re: [RESOLVED] trying to find if a item does not exist in another table doesn't work!

    Quote Originally Posted by ChrisE View Post

    you SQL will work also, the Point is in Post#3 Tyson showed how to setup the Tables.
    Aah, OK. I was just addressing the question, rather than how Salsa should re-work his data
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  21. #21
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,885

    Re: [RESOLVED] trying to find if a item does not exist in another table doesn't work!

    Not sure I understand the problem either. Why try to find a sold item that is missing in the PriceList and try to report its PriceList.PriceName???

    Here's what I did:

    Code:
    Option Explicit
    
    'NOTE:
    '
    '   If you run this in the IDE and get the error "Selected collating sequence..."
    '   just ignore it and run the program again.  This will not occur in the compiled
    '   program.  It is a long-standing IDE bug.
    '
    
    Private Const CONN_STRING As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='Data';" _
      & "Extended Properties='Text'"
    
    Private Sub Form_Load()
        Dim CN As ADODB.Connection
        Dim RS As ADODB.Recordset
    
        Set CN = New ADODB.Connection
        With CN
            .Open CONN_STRING
            Set RS = New ADODB.Recordset
            With RS
                .CursorLocation = adUseClient
                .Open "SELECT * FROM [EmployeeSales.txt] AS [E] " _
                    & "WHERE NOT EXISTS " _
                    & "(SELECT * FROM [PriceList.txt] AS [P] " _
                    & "WHERE [P].[PriceName] = [E].[EmpItem])", _
                      CN, _
                      adOpenStatic, _
                      adLockReadOnly, _
                      adCmdText
                With Flex
                    Set .DataSource = RS
                    .ColWidth(0) = 270
                    .ColWidth(1) = 600
                    .ColWidth(2) = 900
                    .ColWidth(3) = 2100
                End With
                .Close
            End With
            .Close
        End With
    End Sub
    
    Private Sub Form_Resize()
        If WindowState <> vbMinimized Then
            Flex.Move 0, 0, ScaleWidth, ScaleHeight
        End If
    End Sub
    schema.ini:
    Code:
    [PriceList.txt]
    CharacterSet=ANSI
    ColNameHeader=False
    Format=CSVDelimited
    Col1=ID Long
    Col2=PriceName Text
    Col3=Price Currency
    
    [EmployeeSales.txt]
    CharacterSet=ANSI
    ColNameHeader=False
    Format=CSVDelimited
    Col1=ID Long
    Col2=EmpNum Short
    Col3=EmpItem Text
    PriceList.txt:

    Code:
    1,Fibber's Oil,21.50
    2,Lou's 'Poo,15.95
    3,Cutter Comb,3.99
    4,Soft & Fluffy,10.00
    5,Winky,35.89
    EmployeeSales.txt ("**" to mark the sold items missing from PriceList):

    Code:
    101,23,Cutter Comb
    102,8,Winky
    103,7,Fuzzywig**
    104,15,Soft & Fluffy
    105,15,Cutter Comb
    106,7,LiceRid**
    107,8,Soft & Fluffy
    108,8,Pete's Pick**
    109,7,Soft & Fluffy
    110,23,Winky
    111,7,Fuzzywig**
    112,23,Cutter Comb


    Results:

    Name:  sshot.png
Views: 31
Size:  2.2 KB
    Attached Files Attached Files

  22. #22

    Thread Starter
    baila como nunca
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,400

    Re: [RESOLVED] trying to find if a item does not exist in another table doesn't work!

    wow dilettante !!!
    thank youuuuuuuuuuu!!

  23. #23
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,357

    Re: [RESOLVED] trying to find if a item does not exist in another table doesn't work!

    Gonna be a long ten years....

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.