I have query
SELECT DISTINCT employer.id, employer.name
FROM employers
LEFT JOIN positions ON positions.id = employers.id
LEFT JOIN statuses ON statuses.position = positions.some
WHERE statuses.number = 2
And the thing is some i need unique records from employers but i am getting duplicates because statuses.number do repeat like 222 6 777 etc i need to grab them only once. I dont want to use DISTINCT is there other way?
use the GROUP BY statment
SELECT employer.id, employer.name
FROM employers
LEFT JOIN positions ON positions.id = employers.id
LEFT JOIN statuses ON statuses.position = positions.some
WHERE statuses.number = 2 GROUP BY employer.id
Using GROUP BY on employer id, you will only get one employer record for each row returned:
SELECT employer.id, employer.name
FROM employers
LEFT JOIN positions ON positions.id = employers.id
LEFT JOIN statuses ON statuses.position = positions.some
WHERE statuses.number = 2
GROUP BY employer.id
First, any time you reference a LEFT JOINed column in your WHERE clause, like statuses.number =2, you negate the LEFT and force it to behave like an INNER.
Second, try this version:
SELECT e.id, e.name
FROM employers e
WHERE EXISTS(SELECT 1
FROM positions p
INNER JOIN statuses s
ON p.some = s.position
WHERE p.id = e.id
AND s.number = 2)
