Link to home
Create AccountLog in
Avatar of rmmarsh
rmmarshFlag for United States of America

asked on

How do I get the data in a table using a foreign key?

This is how I defined my tables:
        [db executeUpdate:@"CREATE TABLE IF NOT EXISTS CustData ("  //  one-to-many
         "Customer_ID INTEGER PRIMARY KEY, "
         "BUS_NAME TEXT, "
         "EMAIL TEXT, "
         "PHONE TEXT, "
         "SHOP_NAME TEXT, "
         "SHOP_ADDR1 TEXT, "
         "SHOP_ADDR2 TEXT, "
         "SHOP_CITY_STATE TEXT, "
         "SHOP_ZIP TEXT, "
         "SHIP_NAME TEXT, "
         "SHIP_ADDR1 TEXT, "
         "SHIP_ADDR2 TEXT, "
         "SHIP_CITY_STATE TEXT, "
         "SHIP_ZIP TEXT, "
         "SALES_NAME TEXT, "
         "NOTES TEXT)"];
        
        [db executeUpdate:@"CREATE TABLE IF NOT EXISTS OrderInfo ("  //  many-to-one
         "Order_ID INTEGER PRIMARY KEY, "
         "CUST_ID TEXT, "  //  must map to CustData.Customer_ID
         "ORDER_NBR TEXT, "
         "ORDER_DATE TEXT, "
         "ORDER_DEL_INST TEXT, "
         "ORDER_NOTES TEXT, "
         "ORDER_TERMS TEXT, "
         "ORDER_VIA TEXT, "
         "FOREIGN KEY(Cust_ID) REFERENCES CustData(Customer_ID))"];

        [db executeUpdate:@"CREATE TABLE IF NOT EXISTS OrderData ("  //  many-to-one
         "OrderData_ID INTEGER PRIMARY KEY, "  //  must map to OrderInfo.Cust_ID
         "BOLTS TEXT TEXT, "
         "COLOR TEXT TEXT, "
         "STYLE TEXT TEXT, "
         "PRICE DECIMAL, "  //  really want decimal  ???  TODO
         "ITEM_DEL_INST TEXT, "
         "FOREIGN KEY(OrderData_ID) REFERENCES OrderInfo(Cust_ID))"];

Open in new window


I want to get all of the OrderInfo for a particular BUS_NAME in CustData.  The foreign keys are what's giving me a problem.

What is the SELECT statement supposed to look like?  This is what I have so far:
SELECT CustData.Bus_Name, OrderInfo.Order_Date FROM CustData WHERE

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Member_2_276102
Member_2_276102

...for a particular BUS_NAME in CustData.

Either of matthewspatrick's answers should work. Add the appropriate WHERE clause to limit the query to a specific BUS_NAME, e.g., ...WHERE CustData.BUS_NAME = 'MyValue'.

The foreign keys effectively don't enter into it.

Tom
Avatar of rmmarsh

ASKER

Thank you... works like a champ!