开发者

MySQL SQL Query Question from tutorial

I am reading a tutorial, and I just wanted to make sure it was being written correct for my own understanding.

SELECT Division.id FROM items AS Item
LEFT JOIN categories AS Category ON Category.id = Item.category_id
LEFT JOIN sections AS Section ON Section.id = Category.section_id
LEFT JOIN divisions AS Division ON Division.id = Section.divison_id
WHERE Item.id = $id

For some reason, the first line Division.id does not make much sense because the items table is not the divisions table, so how could the Divisions.id be FROM items开发者_如何学Go?

My guess is this line: LEFT JOIN divisions AS Division ON Division.id = Section.divison_id sets Division.id (in the first line of the query), so this would look for all Item.id that equal the Division.id????

I know this is probably dumb simple, but any ideas would be great. I have the idea I am not understanding something here. If anyone can set me straight would be much appreciated.


It from the set of tuples that represent

items AS Item
LEFT JOIN categories AS Category ON Category.id = Item.category_id
LEFT JOIN sections AS Section ON Section.id = Category.section_id
LEFT JOIN divisions AS Division ON Division.id = Section.divison_id

Not just from Items

The above is outer join of

   item     -> Category
   category -> Sections
   sections -> divisions

and you get columns form all in the set. Then the where clause is applied and only the items where the item id matches $id are selected. From this set of joined columns (tuples) you take only the id from the division table.


You will get to select any columns from all the tables that participate in the joins.

Understand that in this query, you are not selecting columns from items, but selecting columns from the result produced by joins from all tables. Hence you can access any column from all tables that participate in joins.


i will try to explain...

you want to get the devisionID of an item.

i guess that items table doesn't have the division id. so you need to get it...

how you get it?

you make a link to category table trow items table, then you link to section table trow category table.

and then trow category table you get the sectionid in division table.


Just think of the whole FROM block in parens, and picture the combined table that temporarily results from it. It will have all fiwksa id itens categirues sections and divisions

SELECT Division.id 
(  
   FROM items AS Item 
   LEFT JOIN categories AS Category ON Category.id = Item.category_id 
   LEFT JOIN sections AS Section ON Section.id = Category.section_id 
   LEFT JOIN divisions AS Division ON Division.id = Section.divison_id
)
WHERE Item.id = $id 

This will return at most one row with one value from the 'big' table --- the Division.id for the Item.id given by $id. Since LEFT JOINS are used The value may be null in the case that there us an item with id of $id but it lacks matches in one of the other tables satisfying your join conditions.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜