Matching rows without filtering (SQL)

Question

I've got table with properties of some products:

table properties(
  prop_id int,
  product_id int
)

My question is: is it possible to select properties that match without filtering them from the result? For example:

select property_id from properties WHERE property_id IN(1,3,5);

You get only rows that match 1,3,5. I need all rows but with information of which rows matched my criteria. I don't want to use 'UNION'.

Thanks for help.

Answer 1

left outer join

select
    parent.name
        ,child.name
    from parent
        left outer join child on parent.familyid=child.familyid

will list all parents, regardless if they have children

Answer 2

I don't have experience with mySQL, but assuming it supports a case statement (or something analagous), you could do this:

select
    prop_id,
    product_id,
    (case when prop_id in (1, 3, 5) then 1 else 0 end) as matches_criteria

from properties

Answer 3

SELECT
    IF (property_id IN (1, 3, 5), 1, 0) AS matched
FROM
    properties;

Answer 4

You can use a case statement:

SELECT 
  prop_id, 
  product_id, 
  (CASE 
    WHEN property_id IN(1,3,5) THEN 1 
    ELSE 0 
  END)
FROM  properties;

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html