How do I like three SQL tables together (2 tables to connect to 1)

Question

1.00/5 (1 vote)

See more:

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:

SQL

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

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!!!!!!!!!!!

Posted 6-Jan-18 10:07am

Member 13591382

Updated 6-Jan-18 23:16pm

Maciej Los

v2

Add a Solution

Comments

Maciej Los 7-Jan-18 4:49am

Please, improve the questaion by adding sample data.

EZW 9-Jan-18 1:50am

Looks like the database was poorly planned and needs to be redesigned

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-01-06T23:16:00

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

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

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?