Database Insert Help?
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 a开发者_运维知识库dd reservation details. I am also wondering if I can include the 2 insert statement in one button or add them seperately..
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.
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.
精彩评论