1. Tables used
# 1. goods select * from goods; +----------+--------+------------+----------+ | goods_id | cat_id | goods_name | owner_id | +----------+--------+------------+----------+ | 1 | 1 | CDMA Phone | 1 | | 2 | 1 | GSM Phone | 1 | | 3 | 1 | 3G Phone | 2 | | 4 | 3 | TP Phone | 1 | +----------+--------+------------+----------+ # 2. cat select * from cat; +--------+---------------+ | cat_id | cat_name | +--------+---------------+ | 1 | Mobile Phone | | 2 | Settled Phone | +--------+---------------+ # 3. owner select * from owner; +----------+------------+ | owner_id | owner_name | +----------+------------+ | 1 | Davy | | 2 | Caly | | 3 | Jack | | 4 | Rose | +----------+------------+
2. How to join those three tables together?
#Possible attempt 1 select temp.*, owner.* from (select goods.*, cat.* from goods left join cat on goods.cat_id = cat.cat_id) as temp left join owner on temp.owner_id = owner.owner_id; ERROR 1060 : Duplicate column name 'cat_id' #Possible attempt 2 select temp.*, owner.* from (select goods.cat_id, goods.goods_name, goods.goods_id, cat.cat_name from goods left join cat on goods.cat_id = cat.cat_id) as temp left join owner on temp.owner_id = owner.owner_id; ERROR 1054 : Unknown column 'temp.owner_id' in 'on clause' #Possible attempt 3 select goods.cat_id, cat.cat_name, goods.goods_name, owner.owner_id, owner.owner_name from goods left join cat on goods.cat_id = cat.cat_id left join owner on goods.owner_id = owner.owner_id; +--------+--------------+------------+----------+------------+ | cat_id | cat_name | goods_name | owner_id | owner_name | +--------+--------------+------------+----------+------------+ | 1 | Mobile Phone | CDMA Phone | 1 | Davy | | 1 | Mobile Phone | GSM Phone | 1 | Davy | | 1 | Mobile Phone | 3G Phone | 2 | Caly | | 3 | NULL | TP Phone | 1 | Davy | +--------+--------------+------------+----------+------------+ #Bingo!
相关推荐
完整报错信息: ...2.进入mysqlId.cnf文件中在[mysqld]下添加skip-grant-tables vim conf/mysqId.cnf 3.进入mysql容器 docker exec -it mysql.5.7 /bin/bash 4.使用mysql -u root -p命令进入mys
NULL 博文链接:https://ylzhj02.iteye.com/blog/2039674
MySQL LEFT JOIN 语法 SQL(MySQL) LEFT JOIN 会取得左表(table1)全部记录,即使右表(table2)并无对应匹配记录。LEFT JOIN 基本语法如下: ... FROM table1 LEFT JOIN table2 ON condition ... MySQL LEFT ...
Mysql之innerjoin,leftjoin,rightjoin详解.pdf
关于 “A LEFT JOIN B ON 条件表达式” 的一点提醒 ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。 如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 ...
mysql数据库报ERROR 1045 (28000): Access denied for user ‘ODBC’@’localhost’ (using password: NO) 看到网上说要配置my.ini 我的是win10系统,用的是mysql-5.7.18-winx64mysql 文件里没有my.ini,在网上看到...
Learn how to design schemas, indexes, queries and advanced MySQL features for maximum performance, and get detailed guidance for tuning your MySQL server, operating system, and hardware to their ...
PHP & MySQL: Novice to Ninja, 6th Edition is a hands-on guide to learning all the tools, principles, and techniques needed to build a professional web application using ... ...Master database design ...
主要介绍了mysql数据库mysql: [ERROR] unknown option '--skip-grant-tables',需要的朋友可以参考下
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。 数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。 ...
用途:帮助读者了解MySQL中的JOIN操作,掌握LEFT JOIN、ON条件过滤和笛卡尔积的使用方法,从而在实际开发中灵活运用JOIN操作实现表关联查询。本文还提供了扩展内容,如排名等应用场景,帮助读者进一步理解JOIN操作的...
Kromann draws upon more than 20 years of experience working with these technologies to pack this book with practical examples and insight into the real-world challenges faced by developers....
NOT IN、JOIN、IS NULL、NOT EXISTS效率对比 语句一:select count(*) from A where A.a not in (select a from B) 语句二:select count(*) from A left join B on A.a = B.a where B.a is null 语句三:select ...
我们有时会有这样的应用,需要在sql的left join时,需要使值为NULL的列不返回NULL而时某个特定的值,比如0。这个时候,用is_null(field,0)是行不通的,会报错的,可以用ifnull实现,但是COALESE似乎更符合标准
mysql left join 语句格式 A LEFT JOIN B ON 条件表达式 left join 是以A表为基础,A表即左表,B表即右表。 左表(A)的记录会全部显示,而右表(B)只会显示符合条件表达式的记录,如果在右表(B)中没有符合条件的记录,...
主要介绍了MySQL的LEFT JOIN表连接的进阶学习教程,包括对左连接的查询效率分析以及相关建议,需要的朋友可以参考下
主要介绍了mysql多个left join连接查询用法,结合实例形式对比分析了mysql使用left join进行连接查询相关操作技巧与注意事项,需要的朋友可以参考下