Avatar of rmmarsh
rmmarsh
Flag 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

SQL

Avatar of undefined
Last Comment
rmmarsh

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
rmmarsh

ASKER
Thank you... works like a champ!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy