I’ve got some problem with my request :
This request work :
x
SELECT o.nom, a.url, a.apikey
FROM Organisation o, API a, Acheter ac
WHERE o.id_organisation = a.id_organisation
AND ac.id_organisation = o.id_organisation
AND ac.id_device = :idd ;
This request work :
SELECT o.nom, a.url, a.apikey
FROM Organisation o, API a, Louer l
WHERE o.id_organisation = a.id_organisation
AND l.id_organisation = o.id_organisation
AND l.id_device = :idd
AND l.dateLocation + l.dureeLocation * interval '1 day' < CURRENT_DATE;
But when I put an “OR” between the both requests, it doesn’t work :
SELECT o.nom, a.url, a.apikey
FROM Organisation o, API a, Acheter ac, Louer l
WHERE o.id_organisation = a.id_organisation
AND (ac.id_organisation = o.id_organisation
AND ac.id_device = :idd)
OR (l.id_organisation = o.id_organisation
AND l.id_device = :idd
AND l.dateLocation + l.dureeLocation * interval '1 day' < CURRENT_DATE);
I’ve tried to put parentheses, but the request still doesn’t work.
Do you have any idea ?
Advertisement
Answer
Becomes easier to read when you use joins:
SELECT o.nom, a.url, a.apikey
FROM Organisation AS o
LEFT JOIN API AS a ON o.id_organisation = a.id_organisation
LEFT JOIN Acheter AS ac ON ac.id_organisation = o.id_organisation
WHERE ac.id_device = :idd ;
SELECT o.nom, a.url, a.apikey
FROM Organisation o
LEFT JOIN API AS a ON o.id_organisation = a.id_organisation
LEFT JOIN Louer AS l ON l.id_organisation = o.id_organisation
WHERE l.id_device = :idd
AND l.dateLocation + l.dureeLocation * interval '1 day' < CURRENT_DATE;
And now combine them:
SELECT o.nom, a.url, a.apikey
FROM Organisation o
LEFT JOIN API AS a ON o.id_organisation = a.id_organisation
LEFT JOIN Louer AS l ON l.id_organisation = o.id_organisation
LEFT JOIN Acheter AS ac ON ac.id_organisation = o.id_organisation
WHERE ac.id_device = :idd
OR (
l.id_device = :idd AND
l.dateLocation + l.dureeLocation * interval '1 day' < CURRENT_DATE
)
Now where is the difference? You had a mistake in your parentheses. The o.id_organisation = a.id_organisation
was canceled out by the or-condition.