[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
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.
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
Re: trying to find if a item does not exist in another table doesn't work!!
Originally Posted by TysonLPrice
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
Re: trying to find if a item does not exist in another table doesn't work!!
Originally Posted by salsa31
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.
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...
Re: trying to find if a item does not exist in another table doesn't work!!
Originally Posted by ColinE66
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.
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.
Re: [RESOLVED] trying to find if a item does not exist in another table doesn't work!
Originally Posted by ChrisE
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...
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.
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