select field with 0 if there is no 3

[mrbrdo]

I need this:
product_id | domain_id
1 | 0
1 | 3
2 | 0
2 | 3
3 | 0

I want to select all product_ids that have the domain_id 3 (this is "my domain id", can be X, but it is not necessarily the highest) domain_id here, but if there is no row that has domain_id 3 and that product_id, i want to select the row that has the product_id and domain_id 0.. so from the table i have above i would select product_ids, domain_ids where domain_id is 3 or 0 if 3 doesn't exist for that product_id:

product_id | domain_id
1 | 3
2 | 3
3 | 0

thanks

 

[hos2]

select product_id, max(domain_id) from table group by product_id

something like this ?

 

[mrbrdo]

as i said:
(this is "my domain id", can be X, but it is not necessarily the highest)

 

[hos2]

sorry, read over that. but is your domainid always the same ???

 

[mrbrdo]

no, obviously, or i wouldn't need this i use php to query, and you should imagine a variable instead of 3.. it's simple: select one for each product_id but choose the one (row) that has domain 3 if possible, else choose the one with 0.

 

[hos2]

I can't test it right here but perhaps you can work something out with the if statement

select productid,sum(if(domain_id='mydomainid' ,1,0) as total, if(total=1,'mydomainid',domain_id) from table group by product_id


(I have never used this constructions yet but I will look at it if they really work this way)

see

http://www.mysql.com/doc/en/Control_flow_functions.html

 

[hos2]

I think it can even simpler

select productid, if(domain_id='mydomainid','mydomainid',domain_id) from table group by product_id

 

[mrbrdo]

no, that only checks the first domain, so for example if i have:
products_id | domain
5 | 0
5 | 3

it would take 0 for if(domain=3,3,domain)
i've tried this and it almost works, but it doesn't work as it should (it only displays the first one that has domain 0, then no more):

SELECT d.products_id, d.domain FROM products_description d WHERE d.domain = (SELECT 3*(3 IN (domain)) FROM products_description WHERE products_id = d.products_id ORDER BY domain DESC LIMIT 1);

if anyone knows.. thx