SQL where everything else

Question

Consider two database tables, Reports and Reports_has_properties

Reports
------
id_report
1
2
3

Reports_has_properties
----------------------
id_report property
1         red
1         big
2         orange
3         blue
3         tiny

Problem: I only get only the reports with the property red, for example, but the query returns all the id_report that match with id_report

SELECT * FROM reports
INNER JOIN reports_has_properties
    ON reports_has_properties.id_report = reports.id_report
WHERE reports_has_properties.property = 'red'

The logical explanion is: IF reports_has_properties HAS AN id_report with another property, dont' select it! The id_report 1 hast the property red and big, so isn't good. But if I search for the property orange, the id_report 2 only have one property, so it's ok.

I tried a lot of things but I have no more ideas. Thanks a lot.

Answer 1

SELECT *
FROM reports
INNER JOIN reports_has_properties
    ON reports_has_properties.id_report = reports.id_report
WHERE reports_has_properties.property = 'red'

is the same as:

SELECT *
FROM reports
INNER JOIN reports_has_properties
    ON reports_has_properties.id_report = reports.id_report
    AND reports_has_properties.property = 'red'

which is not the same as

SELECT *
FROM reports
LEFT JOIN reports_has_properties
    ON reports_has_properties.id_report = reports.id_report
    AND reports_has_properties.property = 'red'

I'm not sure what results you are aiming for.

I think you might be aiming for:

SELECT *
FROM reports
INNER JOIN reports_has_properties
    ON reports_has_properties.id_report = reports.id_report
WHERE reports.id_report IN (
    SELECT reports_has_properties.id_report
    FROM reports_has_properties
    WHERE reports_has_properties.property = 'red'
)

i.e. get ALL the report properties for any reports that have the red property.

Answer 2

It's difficult to tell the results that you're after.

If you want all the reds:

SELECT * FROM reports R
INNER JOIN reports_has_properties P
    ON P.id_report = R.id_report
WHERE P.property  = 'red';

If you want all the 3's:

SELECT * FROM reports R
INNER JOIN reports_has_properties P
    ON P.id_report = R.id_report
WHERE P.id_report = 3;

Answer 3

Hi Sanbor,

I'm not to clear what you are looking for ...

This query will return all reports with the "red" property and all other properties for that report_id.

SELECT R.*,P.property
  FROM reports R
     , reports_has_properties P
 WHERE R.id_report = P.id_report
   AND R.id_report in (select distinct id_report 
                         from reports_has_properties 
                        where property='red'
                      )