时间戳

首页 > 解决方案 > SQL Left Join via foreign keys, how to avoid duplicate columns?

mysql - SQL Left Join via foreign keys, how to avoid duplicate columns?

问题描述

I have numerous tables I join with a primary key field. I can query with the below to get the results I need.

Select *
from orders
Left Join serviceorders on orders.contractLineID = serviceorders.lineID
Left Join all_orders on orders.OrderNum = all_orders.ordernum
Left Join invoices on orders.OrderNum = invoices.OrderNum
Left Join schedule on orders.OrderNum = schedule.Ordernum;

But when I try to create a view from this, I face duplicate columns, as each table I'm joining has the ordernum in it.

I know I shouldn't use * to select everything, but I don't know the syntax for selecting columns from multiple tables?

Is it just:

SELECT orders.column1, orders.column2 schedule.column1, schedule.column 2, invoice.column1, invoice.column2

FROM orders, schedule, invoice

I've also seen you can use the USING command, but the syntax for that is unclear. I'm not sure if that would be the better option as it's just the one column that's being repeated across all tables.

标签： mysqlsql

解决方案

这是语法：

SELECT TableA.id AS tableA_id, -- Or anything you want to name it after 'AS'
       TableB.id AS tableB_id
  FROM TableA
 INNER
  JOIN TableB
    ON TableA.key = TableB.key

这是如果您需要视图具有两个 id，并具有不同的名称。更好的是只选择您想要的列。

推荐阅读