select && ¦¦ table join help

[woompy]

I need help with this table join. I'm using mysql.
What i'm using at the moment is this select table join statement which works fine.

SELECT A.id,A.mem_id,C.category,B.subcat,D.country,A.state,A.city,A.acode,A.international,A.title,A.description,A.slocation,A.url,A.phone,DATE_FORMAT(A.date_added, '%D %b %Y'),A.d_date,A.site,A.graphic,A.trader,A.glob FROM ads as A, category as C, subcat as B, country as D WHERE A.category_id=C.cat_id && A.subcat_id=B.subcat_id && A.country_id=D.id && A.subcat_id='13' && A.country_id='1'

This is the results of explain select, it's using country_id as the index key for A. -
A ref country_id,category_id,subcat_id country_id 2 const 1 where used
C eq_ref PRIMARY PRIMARY 2 A.category_id 1
B eq_ref PRIMARY PRIMARY 2 A.subcat_id 1
D eq_ref PRIMARY PRIMARY 2 A.country_id 1

What i need to do is to add an || condition to the statement.

SELECT A.id,A.mem_id,C.category,B.subcat,D.country,A.state,A.city,A.acode,A.international,A.title,A.description,A.slocation,A.url,A.phone,DATE_FORMAT(A.date_added, '%D %b %Y'),A.d_date,A.site,A.graphic,A.trader,A.glob FROM ads as A, category as C, subcat as B, country as D WHERE A.category_id=C.cat_id && A.subcat_id=B.subcat_id && A.country_id=D.id && (A.subcat_id='13' && A.glob='1' || A.subcat_id='13' && A.country_id='2')

This works ok and pulls the correct records but it won't use an index for A.
This is explain select for that statement -
A ALL country_id,category_id,glob,subcat_id \N \N \N 7 where used
C eq_ref PRIMARY PRIMARY 2 A.category_id 1
B eq_ref PRIMARY PRIMARY 2 A.subcat_id 1
D eq_ref PRIMARY PRIMARY 2 A.country_id 1

Is there another way of doing this with the || condition or am I stuck with it.
If I remove the () from the where condition it crashes mysql.

thanks
Bob

http://totallyfreeads.com.au

 

[swampBoogie]

Do you mean

SELECT A.id,A.mem_id,C.category,B.subcat,D.country,A.state,A.city,A.acode,A.international,A.title,A.description,A.slocation,A.url,A.phone,DATE_FORMAT(A.date_added, '%D %b %Y'),A.d_date,A.site,A.graphic,A.trader,A.glob  FROM ads as A, category as C, subcat as B, country as D WHERE A.category_id=C.cat_id && A.subcat_id=B.subcat_id && A.country_id=D.id && A.subcat_id='13'&& ( A.glob='1' || A.country_id='2')

You should use and or instead of && and ||.

This has nothing to do with ANSI SQL. You should have posted in the Mysql forum.

 

[woompy]

Sorry I did start in the mysql forum but somehow I posted here. I will post in the correct forum.
Thanks for the reply but it dosn't make any difference to the select.

thanks
Bob

http://totallyfreeads.com.au