使用场景
如图有三个表,根据这三个表查出如图四的数据结构。可以理解为查询一家主人的食物消耗记录,消耗者分为主人和客人,表food存储了消耗的食物名称,消耗者id和消耗数量。表user和admin分别是客人表和主人表。现在就要根据这三个表查到如图四的消耗记录,sql该怎么写呢?
下图效果是我们最终要的结果
sql语句
SELECT food.id,food.food,food.num,user.user_name as u_name from food LEFT JOIN user on user.id = food.uid where food.uid in (SELECT id from `user`)
UNION
SELECT food.id,food.food,food.num,admin.admin_name as u_name from food LEFT JOIN admin on admin.id = food.aid where food.aid in (SELECT id from `admin`)