asked on

MySQL Join, Left Join unnexpected result

Hey,

I seem to be doing something wrong in one of my queries. There are 3 tables:

-Documents
-Documents_Analyst_Mapping
-Documents_Analyst

Table structure simplified

Documents:
id | id_doc | title

Documents_Analyst_Mapping:
id_doc | analyst_id

Documents_Analyst
analyst_id | analyst_name

I am trying to display a result that looks like this:
id | id_doc | title | analyst_name

My query is bellow and it gives me more rows than expected (I guess I am making up rows as I go along...) unfortunatly I am not so great with left joins, any help is much appreciated!

SELECT docs.ida, docs.Id_Document, docs.Title, analyst.name AS Analyst_Name
FROM Documents AS docs

LEFT JOIN Documents_Analyst_Mapping AS analyst_map ON docs.Id_Document = analyst_map.Id_Document

LEFT JOIN Documents_Analyst AS analyst ON analyst_map.Analyst_Id = analyst.Analyst_Id

WHERE analyst_map.Analyst_Id = analyst.Analyst_Id
AND docs.Language = 'en'

Open in new window

Kalpan

please modify your query

SELECT docs.ida, docs.Id_Document, docs.Title, analyst.name AS Analyst_Name
FROM Documents AS docs

LEFT JOIN Documents_Analyst_Mapping AS analyst_map ON docs.Id_Document = analyst_map.Id_Document

LEFT JOIN Documents_Analyst AS analyst ON analyst_map.Analyst_Id = analyst.Analyst_Id

WHERE docs.Language = 'en'

McNetic

In which way do you get more results than expected? You should get one row for each row in document_analyst_mapping. Btw, the "WHERE" clause is unnecessary and should anyways not be used to restrict what is joined.

ASKER CERTIFIED SOLUTION

mkobrin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cdaeppen

ASKER

@ kalmax: I've had this and it returns a lot more records than:
SELECT count(*) FROM Documents WHERE Language = "en" <- which I am using as my control

@McNetic: "You should get one row for each row in document_analyst_mapping" -> I am trying to get one row for each row in Documents, that's the point, I want to have the author name in my result next to the document title

rmonsen

Remember that this query will create one row for each valid document <-> analyst combination, so if a document has more than one analyst, you will get more than one row for each document, which means that row count will and should be larger than your "control" query.
As McNetic correctly stated, you should expect one row for each row in document_analyst_mapping.

If you only want one row for each documents, you need to explain how you wish documents with more than one analyst to be handled.

cdaeppen

ASKER

Thanks mkobrin and rmonsen! I think mkobrin has the right idea and rmonsen made me think that maybe there are multible analysts per document giving me the funny result (though only one should be possible the tables are very old and have well over 20k records....)

cdaeppen

ASKER

So it turns out there can be more than 1 analyst per document, I guess group_concat is what I am looking for here?

SOLUTION

Justin Pilditch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cdaeppen

ASKER

I added GROUP_CONCAT(analyst.name) and GROUP BY docs.Id and I am getting a result that I was expecting :) Thanks everyone for your help.