Tables not showing anything when creating new one

Question

I have problem with SQL . I am trying to create tables from other tables with foreign keys. The table is created normally without problem but when I'm trying to see the data inside the table there is none of the data inside! Anyone who knows?

-- Here is my table with the foreign keys

CREATE TABLE StaffXCustomersXMeals(
        --MealID int FOREIGN KEY REFERENCES Meals(MealID),
        --CustomersID int FOREIGN KEY REFERENCES Customers(CustomersID),
        --StaffID int FOREIGN KEY REFERENCES Staff(StaffID)
     )

Answer 1

You might want to consider reading up on the concept of a SQL View - think of it as a "virtual table based on the result-set of an SQL statement." When you create a view based on the JOIN between multiple tables, it saves your query and you can then select from it like you would a table.

For example, you might have the following:

CREATE VIEW StaffCustomerMeals 
AS

SELECT 
    m.MealID,
    c.CustomersID,
    s.StaffID 
FROM 
    Meals m
     LEFT JOIN
    Customers c ON 
        m.SomeIDThatMeansThisCustomer = c.CustomersID
     LEFT JOIN 
    Staff s ON 
        m.SomeIDThatMeansAStaffMember = s.StaffID

You need to define these relationships in accordance with your own schema - it's your homework assignment, so do your best to figure it out. A couple more questions to consider:

1. Does a meal always have both a customer and a staff member, or are they customers who might happen to be staff members?
2. Should you include other information besides the IDs (e.g., CustomerName, StaffMemberDepartment, MealPrice, PurchaseDate)