where clause using and or

[Kal]

Hi guys,

i have the following select query:

SELECT ct.transaction_id, ct.customer_id, ct.package_id, ct.package_status, ct.level_1, ct.level_2, ct.level_3, ct.level_4, ct.level_5, ct.level_6, ct.level_7, ct.level_8, ct.level_9, ct.level_10, ct.level_1_commission, ct.level_2_commission, ct.level_3_commission, ct.level_4_commission, ct.level_5_commission, ct.level_6_commission, ct.level_7_commission, ct.level_8_commission, ct.level_9_commission, ct.level_10_commission, ct.date, ct.week_number, c.customer_id, c.customer_type, c.business_name, c.home_contact_name, c.deal_date, p.id, p.customer_id, p.cli FROM the_thecommission.commission_transactions ct left join themobi_thecommission.customers c ON ct.customer_id=c.customer_id left join themobi_thecommission.packages p ON ct.package_id=p.id WHERE (ct.level_1 OR ct.level_2 OR ct.level_3 OR ct.level_4 OR ct.level_5 OR ct.level_6 OR ct.level_7 OR ct.level_8 OR ct.level_9 OR ct.level_10 = '$sales_code') and ct.week_number = '$prev_week_number' and ct.package_status = 'Live'

however it needs to be chage slightly because a 'sales_code' can appear in more than one level. how would i re-write the where clause in order for me to do this.

running this at the moment displays all the records, in some cases it's fine because the same sales code exists in all the records but in some the same sales_code only appears in a couple of the records.

thanks in advance

[guelphdad]

your where clause won't work as you currently have it.
Everything in your where clause is ignored except this part of it:
ct.level_10 = '$sales_code'

you have to write it like this
[code]ct.level_9 = '$sales_code' OR ct.level_10 = '$sales_code' etc. etc. for each of those conditions to be evaluated.

you should also look into database normalization, you most likely should rearrange all of those individual level and commission columns into separate tables or two columns with one holding the value it does now and the other denoting if it is level1 or level 2 etc. etc.

[Kal]

thanks for your reply.

is there no way of getting it to work with the way it's currently layed out?

i agree i need to re-design my database structure at some stage.

[guelphdad]

so you are saying the sales code can appear in level 5 in one row and level 10 in another and you only want one of those rows returned?

If so how do you decide which of those two rows to return or does it matter?

[Kal]

yeah the sales_code can appear in level_1, level_4 and level_6 for example on one row and on another row it may only appear in level_2, i need it to return both rows.

[guelphdad]

then fix your where clause as I've explained and both rows will be returned.

[Kal]

all soreted and working thanks for your help. lokking into re-strucutring the DB now.