SQL EXISTS / COUNT(*) Problem

[bacon333]

I'm working with over a million records and these records are grouped by a part number. I want to use an sql statement what will check if that part number exists but once it's found to not go through the rest of the records. Count (*) will count all million records even if the part number is already found. How would i use "WHERE EXISTS" or any other functions to accomplish this? thank you.

 

[MrChad]

I'm not sure I understand your question. Can you give an example of the table schema and the SQL query you're trying to run?

 

[Slave2TheGrind]

I'm assuming SQL Server 2000 T-SQL language here as it is what I am used to:
If Exists( Select 1 From <MyTable> Where <PartNumber> = <SomeValue> ) Begin
<...Processing statements for part number exists...>
End [Else Begin]
<...Processing statements for part number not existing...>
[End]
Hope this helps.

 

[bacon333]

I'm using VBA in Access

strLine = "SELECT partnum FROM datetesting WHERE EXISTS(select partnum FROM datetesting WHERE [partnum]=" &amp; _
Me.txtpartnum &amp; ")"

rstCurr.Open strLine, cnnLocal, adOpenStatic, adLockPessimistic

If CInt(rstCurr.GetString) = 0 Then
MsgBox "That part number does not exist!"
Exit Sub
End If

rstCurr.Close

that doesn't work.

 

[MrChad]

Why can't you just do this?

SELECT partnum FROM datetesting WHERE partnum = Me.txtpartnum

 

[bacon333]

i need to some how quantify the result so i can run a conditional check.

SELECT partnum FROM datetesting WHERE partnum = Me.txtpartnum

selects the actual row with that part number, but if that row doesn't exist, i don't think the result is a zero.

 

[Slave2TheGrind]

Ok, I see what you're doing now.
The only way I can see to speed this up is:
strLine = "If Exists( Select 1 From datetesting Where partnum = " &amp; Me.txtpartnum &amp; ") Then 1 Else 0"
I am not sure if the If Exists will work in Access the way that it does in SQL Server, but if it does then Access should table scan until it finds the part number you input and terminate as soon as it finds a match. However, if the part number does not exist, then you gain nothing as Access had to scan the entire table to determine that it did not exist anyway.
Can you perhaps add an index on partnum? This should speed up the query that MrChad posted above, and it's much simpler anyway.

** Edited to add "Then 1 Else 0" to end of query for conditional response **

 

[KLin]

It sounds like you're going through a little more trouble than you need to.

 

[MrChad]

Originally posted by: bacon333
i need to some how quantify the result so i can run a conditional check.

SELECT partnum FROM datetesting WHERE partnum = Me.txtpartnum

selects the actual row with that part number, but if that row doesn't exist, i don't think the result is a zero.

Then do:

SELECT COUNT(partnum) As PartNumExists FROM datetesting WHERE partnum = Me.txtpartnum

Assuming partnum is unique, your only possible return values are 1 or 0.

 

[bacon333]

Originally posted by: KLin
It sounds like you're going through a little more trouble than you need to.

Thanks for everyone's help. Klin, this worked perfect thanks!

ps. slave2grind, access can't use "if" sql statements