我如何一起使用三个SQL表（2个表连接到1个） [英] How do I like three SQL tables together (2 tables to connect to 1)

问题描述

我有三个表 - 用户，详细信息和系统。用户可以进行预订，信息存储在详细信息和系统下，最初我将信息链接在一起如下：

I have three tables - users, details, and system. A user can make a booking, and the information is stored under 'details' and 'system', originally, I linked the information together like so:

SELECT users.user_id, users.user_email, system.date, system.time, 
   system.table_layout, details.party_size, details.children_no, 
         details.diets, 
      details.occassion, details.more
      FROM users
    Join system  on users.user_id = system.id
    Join details on system.id = details.ID
ORDER BY users.user_id

然后我意识到，一个用户可以进行多次预订，但是，系统中的ID '和'详细信息'将具有不同的ID号，并且将不再与用户原始ID匹配。我该如何解决这个问题？我尝试通过创建一个名为'customer_id'的列来将新列添加到'systems'和'details'表中，以将user.id链接到它。但是，这不起作用，因为它一直显示结果'0'，我必须手动输入用户ID。



有人可以帮我链接一个用户下的三个表一起？谢谢



我的尝试：



google

youtube

stackquestions

编码人员

没什么!!!!!!!!!!!

But then I realised, one user can make multiple bookings, however, the ID in 'system' and 'details', will then have a different ID number and will no longer match the users original ID. How do I solve this issue? I attempted to add new columns to the 'systems' and 'details' table by creating a column called 'customer_id' to link the user.id to it. However, this did not work as it kept showing the result '0' and I'd have to manually enter in the users ID.

Can someone please help me link the three tables together under one user? Thank you

What I have tried:

google
youtube
stackquestions
coding people
nothing!!!!!!!!!!!

推荐答案

这没有多大意义 - 为什么您的系统ID和您的详细信息ID是相同的？这意味着System表中的每一行在Details表中都有一个匹配的行，这意味着它们是有效的，它们是同一个表！

我不知道你到底想要做什么实现 - 而你的问题根本没有明确 - 但我怀疑你需要回到舞台并再次查看你的数据要求，以便重新设计你的数据库。



我认为您需要这样的事情：暂时考虑发票。

公司可能会向您发出几个订单，每个订单都需要发票。

每张发票可能是多件物品。

所以你最终得到四张桌子：

物品

That doesn't make a whole lot of sense - why would your System ID and your Details ID be the same? That implies that every row in your System table has a single matching row in your Details table, which would mean that effectively, they are the same table!
I don't know exactly what you are trying to achieve - and your question doesn't make that at all clear - but I suspect that you need to go back a stage and look at your data requirements again, with a view to redesigning your database.

I think you need something like this: Think of invoices for a moment.
A company may make several orders with you, each order needs an invoice.
Each invoice may be for multiple items.
So you end up with four tables:
Items

ID            Primary key, probably IDENTITY or GUID
Description
Price

客户

Customers

ID            Primary key, probably IDENTITY or GUID
CustName
Address

发票

Invoices

ID            Primary key, probably IDENTITY or GUID
InvoiceNo     
InvoiceDate
terms
CustID        Foreign key to Customers.ID

InvoiceItems

InvoiceItems

ID            Primary key, probably IDENTITY or GUID
InvoiceID     Foreign key to Invoices.ID
ItemID        Foreign key to Items.ID

At没有必要指望不同表中的ID匹配，只需通过外键引用正确的值。

因此，当您处理订单时，您会查找客户ID，并创建一个使用该ID作为外键的Invoices表中的新行。然后，您使用新的发票ID

在InvoiceLines表中创建每个新行作为外键返回整个发票。



有意义吗？

At no point do you expect the ID's in the different tables to match, just that they reference the correct value via foreign keys.
So when you process an order, you look up the Customer ID, and create a new row in the Invoices table using that ID as the foreign key. You then use that new Invoice ID
to create each new line in the InvoiceLines tables as the Foreign key back to the overall invoice.

Make sense?

这篇关于我如何一起使用三个SQL表（2个表连接到1个）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！