CODEWITHSUNDEEP
mysql
Laurent974
Laurent974

Reputation: 11

What does this inner join query exactly mean?

I'm often on this site to find answers and I usualy find what I look for but this time it's really particular so I post.

I'm working on a code I didn't write and I'd like somebody to explain me in detail what does the following query mean :

SELECT count(*) 
FROM ( SELECT Id_dormir 
       FROM (dormir INNER JOIN dormir_photo AS DP 
             ON Id_dormir = Id_dormir_photo) INNER JOIN communes as C ON 
             Commune_dormir = Id_commune  
       WHERE  Type_dormir=3 
       GROUP BY Id_dormir, Commune_dormir ) AS T

I'l not a boss in mysql and all those inner join and as are not very clear (it's a query from a search engine.

If anyone can help, many thanks, Laurent

Upvotes: 1

Views: 75

Answers (1)

BaBL86
BaBL86

Reputation: 2622

The outer query count a number of rows, returned by inner query:

SELECT Id_dormir FROM dormir
    INNER JOIN dormir_photo AS DP ON Id_dormir = Id_dormir_photo
    INNER JOIN communes as C ON Commune_dormir = Id_commune 
WHERE  Type_dormir=3 GROUP BY Id_dormir, Commune_dormir

In this query author get information from 3 tables: domir, domir_photo, but only rows, where Id_dormir_photo = Id_dormir and from communes rows, where Commune_dormir = Id_commune. Than he filter rows with Type_dormir=3 and groups this with Id_dormir, Commune_dormir to have only UNIQUE rows with Id_dormir, Commune_dormir.

Your query count rows, where unique pair of Id_dormir, Commune_dormir and isset records in dormir_photo and communes.

I recommend you to read about different types of JOIN operation, especially about INNER JOIN (standard JOIN) and LEFT JOIN, this types are most applicable.

Upvotes: 1

Related Questions