How to join to a one to many relationship table?

I have three tables, lets say for example table 1 is locations , then table 2 is location_items then table 3 is items

location_items is the link between locations and items

each locations has one location_items and each location_items has either one or more items

Column names:

locations table: location_id primary key

location_items table: location_items_id primary key, location_id foreign key, item_id foreign key(one is to many, 1 location, many items).

items table: item_id primary_key, name(name of the item that i want to get)

Now what I want is select the locations , then join to location_items , then join to items to get the lets say the first items.name that is not null. Here is my sample query:

SELECT l.location_id,COALESCE(i.name) 
            FROM locations l
            LEFT JOIN location_items li USING(location_id)
            LEFT JOIN items i USING(item_id)
            WHERE l.location_id LIKE '%P021%'
        GROUP BY l.location_id

But I only get all the location_ids with null name

链接地址: http://www.djcxy.com/p/63804.html

上一篇: MySQL删除值如果存在另一行

下一篇: 如何加入一对多的关系表？