troubleshooting Question

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

Avatar of rmmarsh
rmmarshFlag for United States of America asked on
SQL
3 Comments1 Solution252 ViewsLast Modified:
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))"];

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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros