We help IT Professionals succeed at work.

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

rmmarsh
rmmarsh asked
on
249 Views
Last Modified: 2012-06-21
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

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
...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

Author

Commented:
Thank you... works like a champ!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.