Database Insert Help?

Question

Hello I am making a reservation system and I am having troubles with my table can someone help me with my problem. I have a table customer and a table reservation. My customer table handles all the customers personal info and the reservation handles the reservation details.

My customer table has a customer ID which is a primary key and auto increment. and my reservation table has a reservation ID which is a primary key and auto increment.

MY PROBLEM IS... How could I connect the two?

I have a big problem on how to join them in my select statement because I dont know what values will I join.

*Note: Btw, I'm using c# winform and I seperated the add customer and add reservation details. I am also wondering if I can include the 2 insert statement in one button or add them seperately..

It would do some good to familiarize yourself with Database Normalization, if you aren't already. This will give you a primer on how keys and foreign keys work, and why they are important. en.wikipedia.org/wiki/Database_normalization Knowing how to normalize a DB is the first step to knowing how to denormalize (flatten) it back with SQL Joins. — EtherDragon
– EtherDragon, Commented Sep 12, 2011 at 22:33

gilly3 · Accepted Answer · 2011-09-13 00:23:57Z

2

Use the SQL JOIN statement in your SELECT QUERY:

SELECT c.CustomerName, r.ReservationTime
FROM reservation r
JOIN customer c ON r.CustomerId = c.CustomerId

Edit: It sounds like you are having trouble understanding how to get a newly created CustomerId in order to include it in the Reservation table. The key here is LAST_INSERT_ID(). After your insert into Customer, get the value from LAST_INSERT_ID() - that is the newly created CustomerId. You can then use that value when you insert into Reservation. Eg:

INSERT INTO Customer (CustomerName)
VALUES ('Joe Schmoe');

INSERT INTO Reservation (ReservationTime, CustomerId)
VALUES ('2011-09-12 18:30:00', LAST_INSERT_ID());

Pardon syntax errors - SQL Server is my primary language, if you hadn't gathered that already.

edited Sep 13, 2011 at 0:23

answered Sep 12, 2011 at 22:04

gilly3

92.6k26 gold badges148 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

rj tubera Over a year ago

I get your point. But how would I know what customerID will I put in the reservation table?? How would I know that that reservation has customerID 1? that's my problem T_T

gilly3 Over a year ago

Take a look at @@Identity. After you do your insert into the Customer table, use the @@Identity value as the CustomerId in the Reservation table.

rj tubera Over a year ago

Thank you Mr.Gilly. So how would that exactly look like?

rj tubera Over a year ago

uhmm. Mister My real problem is inserting the value for the foreign key CustID. Because I don't know what value will I put it. I know that the R.CustID and C.CustID should be equal but I dont know how to make them equal. I dont know where will I get a reference for the value that I will input in r.custId. THANKS!

gilly3 Over a year ago

MySQL doesn't have @@IDENTITY. Instead use LAST_INSERT_ID(). I'll update my answer with some detail.

|

Christian Specht · Accepted Answer · 2011-09-12 22:05:05Z

1

Well, your reservation table should have a customer ID as well.
As I understand you, that's not the case yet.
But it should be like that, because every reservation belongs to a customer.

Then, you can join both tables on the customer ID.

answered Sep 12, 2011 at 22:05

Christian Specht

36.6k16 gold badges132 silver badges190 bronze badges

5 Comments

rj tubera Over a year ago

Sir how would I know what custID has a certain reservation have?

Christian Specht Over a year ago

How does your UI for saving a reservation look like? Your users probably have to select a customer who the reservation is for, right? You need to insert the ID of that customer into the reservation table.

rj tubera Over a year ago

So. I would Have a combobox with all the customer in my database and the user will choose which customer will have the reservation??

rj tubera Over a year ago

Sir How Would I connect my combobox to the database so that its values will be connected to the database? that gives me another problem T_T

Christian Specht Over a year ago

You can use data binding for that. I'm no WinForms/databinding guru, so I can't explain it properly. Just search for winforms combobox databinding on Google or here on SO.

Collectives™ on Stack Overflow

Database Insert Help?

2 Answers 2

7 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related