Solved

MySQL Join, Left Join unnexpected result

Posted on 2011-03-01
9
512 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:cdaeppen
9 Comments
 
LVL 14

Expert Comment

by:Kalpan
ID: 35006150
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'
0
 
LVL 8

Expert Comment

by:McNetic
ID: 35006166
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.
0
 
LVL 7

Accepted Solution

by:
mkobrin earned 400 total points
ID: 35006209
it looks to me that you should actually be using an inner join here, because the ID fields will never be null, so I would do it as follows:


SELECT docs.id, docs.Id_Doc, docs.Title, analyst.Analyst_Name
FROM Documents AS docs

INNER JOIN Documents_Analyst_Mapping AS analyst_map ON analyst_map.Id_Doc = docs.Id_Doc

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

WHERE docs.Language = 'en'

Open in new window

0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:cdaeppen
ID: 35006581
@ 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
0
 
LVL 3

Expert Comment

by:rmonsen
ID: 35006649
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.
0
 

Author Comment

by:cdaeppen
ID: 35006710
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....)
0
 

Author Comment

by:cdaeppen
ID: 35006977
So it turns out there can be more than 1 analyst per document, I guess group_concat is what I am looking for here?
0
 
LVL 11

Assisted Solution

by:Murfur
Murfur earned 100 total points
ID: 35007036
There are basically, two types of join - INNER and OUTER.
LEFT and RIGHT joins merely dictate which way to process the results of an OUTER join.

An INNER join will select all rows from the joined tables ONLY where there is a match between the columns specified.

An OUTER join will select all rows from the joined tables REGARDLESS of whether there is a match between the columns specified.

A LEFT join will select ALL records from the left half of the join and only return data from the right where there is a match.

A RIGHT join is the opposite.

So in your example, a left join would list all book, show who has read them and if some books are not read, there would be no match in the mapping table, so the analyst name result would be null.

In these situations, I find it easiest to write out all of the fields in the query and only when it works do I start removing the columns I don't need. This way, any errors like ambiguous columns are reported.

I did this:
select d.*, dam.*, da.*
from Documents d
left join Documents_Analyst_Mapping dam on d.id_doc = dam.id_doc
left join Documents_Analyst da on dam.id_analyst = da.id_analyst 

Open in new window


and in my data, no-one had read book #3 so I got this result:

 id_doc     title     language     id      id_doc     id_analyst     id_analyst     analyst_name    
 ---------  --------  -----------  ------  ---------  -------------  -------------  --------------- 
 1          Book 1    en           1       1          1              1              John            
 1          Book 1    en           2       1          2              2              Steve           
 1          Book 1    en           3       1          3              3              Mary            
 1          Book 1    en           4       1          4              4              Simon           
 2          Book 2    en           6       2          2              2              Steve           
 2          Book 2    en           7       2          3              3              Mary            
 3          book 3    en           (null)  (null)     (null)         (null)         (null)          
 4          Book 4    en           11      4          2              2              Steve           
 4          Book 4    en           12      4          4              4              Simon  

Open in new window


Then I filtered the output columns required:

select d.id_doc, d.title, da.analyst_name
from Documents d
left join Documents_Analyst_Mapping dam on d.id_doc = dam.id_doc
left join Documents_Analyst da on dam.id_analyst = da.id_analyst 

Open in new window


and I got this:

 
id_doc     title     analyst_name    
 ---------  --------  --------------- 
 1          Book 1    John            
 1          Book 1    Steve           
 1          Book 1    Mary            
 1          Book 1    Simon           
 2          Book 2    Steve           
 2          Book 2    Mary            
 3          book 3    (null)          
 4          Book 4    Steve           
 4          Book 4    Simon 

Open in new window

0
 

Author Comment

by:cdaeppen
ID: 35007045
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question