[SOLVED] 2 left join is messing up

[asmith]

Hi,

 

In some table I have some data about members which their status is 1 or 0.

I want to get members + how many accepted and non accepted document they have:

 

SELECT

m.ID_MEMBER, m.name,

IFNULL(count(d1.ID_DOC), 0) as accepted,

IFNULL(count(d2.ID_DOC), 0) as notAccepted

FROM members AS m

LEFT JOIN documents AS d1 ON d1.ID_MEMBER = m.ID_MEMBER AND d1.isAccepted = 1

LEFT JOIN documents AS d2 ON d2.ID_MEMBER = m.ID_MEMBER AND d2.isAccepted = 0

 

 

It is viewing wrong result to me.

I narrowed it down to 1 member (put where clause), and see it was viewing '6' for both accepted and nonAccepted while I had 3 accepted and 2 non accepted for that member.

 

Any idea how to fix it?

[kickstart]

Hi

 

I think what your JOIN has returned is every combination of accepted and non accepted docs, so 3 accepted x 2 non accepted docs and 6 rows returned.

 

Try this instead:-

 

SELECT m.ID_MEMBER, m.name,
IFNULL(acceptedDocs.DocCount, 0) as accepted,
IFNULL(nonAcceptedDocs.DocCount, 0) as notAccepted
FROM members AS m
LEFT JOIN (SELECT m.ID_MEMBER, COUNT(*) AS DocCount FROM documents WHERE d1.isAccepted = 1) acceptedDocs ON m.ID_MEMBER = a.ID_MEMBER
LEFT JOIN (SELECT m.ID_MEMBER, COUNT(*) AS DocCount FROM documents WHERE d1.isAccepted = 0) nonAcceptedDocs ON m.ID_MEMBER = b.ID_MEMBER

 

All the best

 

Keith

[asmith]

Thanks mate for the info.

 

I was trying not to go to nested queries. But sure your query will do the job.

btw in the query for left join parts, You forgot to add group by clause.

 

Thanks for the idea

Cheers

[kickstart]

I was trying not to go to nested queries. But sure your query will do the job.

 

Not sure there is really a choice I am afraid.

 

btw in the query for left join parts, You forgot to add group by clause.

 

. Sorry.

 

All the best

 

Keith