Wilf Wilf - 3 months ago 13
MySQL Question

SQL result not become as expect when using inner join

I have an unanswerable sql query with 'inner join'. The result is not come as I expect. Even, I remove the 'inner join' - the result is also include everything - like the 'inner join' does.

What to do if I only wants the result comes from these conditions?


  1. prop_type='5'

  2. not prop_price='0'

  3. prop_price2='0'

  4. prop_price3='0'



Mysql:

select prop_lang, prop_type, prop_for,
ppix_cover,prop_price,prop_price2,prop_price3
from prop_db
inner join prop_pix on prop_db.prop_sid=prop_pix.prop_sid
where prop_db.prop_type='5'
and prop_db.prop_lang='1'
and prop_pix.ppix_cover='1'
and prop_db.prop_for='1' or prop_db.prop_for='0'
and prop_price3='0'
and prop_price2='0'
and not prop_price='0'
group by prop_db.prop_sid
order by prop_db.prop_id
LIMIT 0, 9


This is the result from the query above.

enter image description here

As you see, the column 'prop_type' comes up with other value which I don't expected. And also the first row comes up with price2 and price3 that not ='0'.

What should I do to make the result show as the conditions above?

Answer

A wild guess but it is usually spot on: and and or need parentheses.

Half-way down:

select prop_lang, prop_type, prop_for, 
ppix_cover,prop_price,prop_price2,prop_price3 
from prop_db 
inner join prop_pix on prop_db.prop_sid=prop_pix.prop_sid 
where prop_db.prop_type='5' 
and prop_db.prop_lang='1' 
and prop_pix.ppix_cover='1' 
and (prop_db.prop_for='1' or prop_db.prop_for='0') 
and prop_price3='0' 
and prop_price2='0' 
and not prop_price='0' 
group by prop_db.prop_sid 
order by prop_db.prop_id 
LIMIT 0, 9