How to group boolean expressions in a WHERE clause

lyealain

Expand|Select|Wrap|Line Numbers

 
select * from cbomnew 

where PartNumber not in 

  (select PartNumber from cbom_filtered)

and comCode= 'PCEC' || comCode='LOX' || comCode='CCEC'

i wan to select those partnumber which not in cbom_filtered.. but why the partnumber which exist in cbom_filtered still appear...?? my code correct???

Atli

Because you need to group your boolean expressions together, or else it will evaluate them seperateley and return any row that matches any of them.

This is how your query should look:

Expand|Select|Wrap|Line Numbers

 
select * from cbomnew 

where PartNumber not in 

  (select PartNumber from cbom_filtered)

and  

(    comCode= 'PCEC'

  OR comCode='LOX'

  OR comCode='CCEC' 

)

Atli

I have edited the thread's title to better describe it's contents.
Please read the Posting Guidlines before posting

Moderator

lyealain

Pls advice me...

i have PartNumber and SupplierName as my primary key..

[PHP]"select * from cbomnew where PartNumber not in (select PartNumber from cbom_filtered) and (comCode= 'PCEC'or comCode='LOX'or comCode='CCEC' )"[/PHP]

how do i put like

select PartNumber and SupplierName not in( select PartNumber from cbom_filtered) ?????

i wan to compare 2 data which are partnumber and suppliername.. how do i do that...

pbmods

Heya, lyealain.

Quote:

Originally Posted by lyealain

select PartNumber and SupplierName not in( select PartNumber from cbom_filtered) ?????

You'll want to use WHERE NOT EXISTS

lyealain

[PHP] SELECT * FROM cbomnew WHERE NOT EXISTS (
SELECT * FROM cbom_filtered
WHERE cbomnew.partnumber = cbom_filtered.partnumber
AND[/b] cbomnew.suppliername = cbom_filtered.suppliername)
where (comCode= 'PCEC' or comCode='LOX' or comCode='CCEC' );[/PHP]

the above code give me error!!!!

i wan to select * where (comCode= 'PCEC' or comCode='LOX' or comCode='CCEC' ) and with these 2 constraints

[PHP]WHERE cbomnew.partnumber = cbom_filtered.partnumber
AND cbomnew.suppliername = cbom_filtered.suppliername)[/PHP]

pbmods

Heya, lyealain.

You have an errant '[/b]' in your SQL query. Is this intentional?

lyealain

ohh.. sorry.. ignore that [/b]..

pbmods

Heya, lyealain.

What error does MySQL give you when you run that query?

lyealain

hi.. very sorry for late reply...

i have updated my code to be like this...
[PHP]
dim qryFilter, rsReport
qryFilter= "select * from cbomnew where PartNumber + SupplierName not in (select PartNumber + SupplierName from cbom_filtered)and (RFQ='"&rfq&"')and (DateUploaded='"&lastup&"') and comCode IN (Select Distinct ComCode From comcode_table) "
[/PHP]
my idea is if the above not eof... then insert into cbom_filtered

but the above sentence keep inserting the same data into my cbom_filtered..

i have select something where not in the other table.. i wan to compare both PartNumber + SupplierName with cbom_filtered

cbomnew table: contains
partnumber: 123 SupplierName = angel
partnumber: 456 SupplierName = holy

in cbom_filtered table: contains
partnumber: 123 SupplierName = devil
partnumber: 456 SupplierName = ghost

if in cbom_filtered table: contains
partnumber: 123 SupplierName = angel
then don insert..
else .. insert..

lyealain

help help... experts pls help...thanks thanks thanks..hahahhaha

pbmods

Try this:

Expand|Select|Wrap|Line Numbers

 SELECT * FROM `cbomnew` WHERE NOT EXISTS(SELECT * FROM `cbom_filtered ` WHERE (`cbom_filtered`. `PartNumber ` = `cbomnew `.`PartNumber`) OR (`cbom_filtered`. `SupplierName ` = `cbomnew `.`SupplierName`)) AND (`RFQ` = '" & rfq & "') AND (`DateUploaded` = '" & lastup & "') AND EXISTS(SELECT * FROM `comcode_table` WHERE `comcode_table`.`ComCode` = `cbomnew`.`ComCode`)
 

Make sure your `cbomnew`, `cbom_filtered` and `comcode_table` tables are indexed on `PartNumber`, `SupplierName`, `RFQ`, `DateUploaded` and `ComCode`.

http://dev.mysql.com/doc/refman/5.0/...ubqueries.html

lyealain

[PHP]WHERE (`cbom_filtered`. `PartNumber ` = `cbomnew `.`PartNumber`) OR (`cbom_filtered`. `SupplierName ` = `cbomnew `.`SupplierName`)
[/PHP]
thanks.. should i put 'or' / 'and' for the above statement that u specified..

i think is AND??? because i wan to include both partnumber and suppliername to compare with other table at the same time

pbmods

Heya, lyealain.

Quote:

Originally Posted by lyealain

i think is AND??? because i wan to include both partnumber and suppliername to compare with other table at the same time

The query I posted will return a result if either one exists. If you only want to return results that have both values, then you are correct; you'll want to change that to AND.