MySql内部连接查询7个表
我有以下表格。MySql内部连接查询7个表
cars
------------------------------------
¦ car_id ¦ color_id ¦ brand_id ¦
------------------------------------
¦ 1 ¦ 1 ¦ 3 ¦
¦ 2 ¦ 2 ¦ 3 ¦
¦ 3 ¦ 1 ¦ 4 ¦
¦ 4 ¦ 3 ¦ 4 ¦
------------------------------------
colors
-----------------------
¦ color_id¦ color ¦
-----------------------
¦ 1 ¦ red ¦
¦ 2 ¦ blue ¦
¦ 3 ¦ green ¦
¦ 4 ¦ white ¦
-----------------------
brands
-----------------------------------
¦ brand_id¦ brand ¦ parent_id ¦
-----------------------------------
¦ 1 ¦ Toyota ¦ 0 ¦
¦ 2 ¦ Nissan ¦ 0 ¦
¦ 3 ¦ Sunny ¦ 2 ¦
¦ 4 ¦ Corrola ¦ 1 ¦
-----------------------------------
companies
---------------------------------
¦ company_id¦ name ¦ location ¦
---------------------------------
¦ 1 ¦ ABC ¦ New York ¦
¦ 2 ¦ XYZ ¦ Florida ¦
¦ 3 ¦ ASD ¦ Texas ¦
¦ 4 ¦ 3MM ¦ Florida ¦
---------------------------------
contacts
-----------------------------------
¦ contact_id¦ name ¦ company_id ¦
-----------------------------------
¦ 1 ¦ James ¦ 1 ¦
¦ 2 ¦ Kevin ¦ 1 ¦
¦ 3 ¦ Mic ¦ 2 ¦
¦ 4 ¦ Nadia ¦ 3 ¦
-----------------------------------
orders
---------------------------------------
¦ order_id ¦ company_id ¦ contact_id ¦
---------------------------------------
¦ 1 ¦ 1 ¦ 1 ¦
¦ 2 ¦ 1 ¦ 2 ¦
¦ 3 ¦ 2 ¦ 3 ¦
¦ 4 ¦ 3 ¦ 4 ¦
---------------------------------------
order_items
---------------------------------------
¦ sn ¦ order_id ¦ car_id ¦ price ¦
---------------------------------------
¦ 1 ¦ 1 ¦ 1 ¦ 100 ¦
¦ 2 ¦ 2 ¦ 2 ¦ 200 ¦
¦ 3 ¦ 3 ¦ 3 ¦ 100 ¦
¦ 4 ¦ 3 ¦ 4 ¦ 150 ¦
---------------------------------------
我正在一个web应用程序中,我有这7个互相连接的表。 我需要一个PHP MYSQL连接查询将列出已经以表格格式已经下令所有汽车行驶下列:
--------------------------------------------------------------------------
¦ Order ID ¦ Company Name ¦ Contact Name ¦ Car Ordered ¦ price ¦
--------------------------------------------------------------------------
¦ 1 ¦ ABC ¦ James ¦ Nissan Sunny, Green ¦ 100 ¦
--------------------------------------------------------------------------
请帮助!
Please try with this query:
SELECT orders.order_id as 'Order ID',
companies.name as 'Company Name',
contacts.name as 'Contact Name ',
CONCAT(brands.brand , ', ' ,colors.colcor) as 'Car Ordered',
order_items.price as 'price'
FROM order_items
left outer join orders on orders.order_id = order_items.order_id
left outer join contacts on orders.contact_id = contacts.contact_id
left outer join companies on orders.company_id = companies.company_id
left outer join cars on order_items.car_id =cars.car_id
left outer join colors on cars.color_id = colors.color_id
left outer join brands on cars.brand_id = brands.brand_id
order by brands.brand asc
谢谢。它的工作除了“汽车订购”栏。汽车有序列在所有行中显示“0”零。此列应显示“母品牌,子品牌,颜色”即“Nissa,阳光明媚,绿色” – user1362473 2014-10-01 10:06:50
请再试一次 – jainvikram444 2014-10-01 10:52:29
以前的例子是MS-SQL服务器,现在加入concat函数加入 – jainvikram444 2014-10-01 10:53:26
你试图达到这样的结果的地方在哪里? – 2014-09-29 09:15:16
请注意,order_items中的sn列(可能是)多余的 – Strawberry 2014-09-29 09:33:19
我是mysql新手。我可以写简单的querries。我试图用嵌套querry实现这一点,但它并没有工作......这对我来说是一种火箭科学 – user1362473 2014-09-29 09:52:09