burhankhan Posted August 20, 2008 Share Posted August 20, 2008 Hi: I want to get data from multiple tables. I have designed a design of my database in MS Access. Actually there are 4 tables. one is PRODUCTS (i want to get products from that table) 2nd is COUNTRIES (products can be deliver to multiple countries) 3rd is OPTIONS (each product will have some DEFINED options) and 4rd is MIDDLE-TABLE, i have created it as middle table. Actually each PRODUCT can have multiple COUNTRIES and multiple OPTIONS. My questions are: how to fetch all products in USA? or all products that have OPTION # 3 ? I know middle-table is not a good idea, because it contains tables names and their IDs, like country_id or option_id..... But i am not understanding how to do this? Can anyone suggest me with a sample query? Regards, Burhan Khan Quote Link to comment Share on other sites More sharing options...
revraz Posted August 20, 2008 Share Posted August 20, 2008 Have the middle table store the product_id, country_id and option_id for each record. Quote Link to comment Share on other sites More sharing options...
burhankhan Posted August 20, 2008 Author Share Posted August 20, 2008 Hi Revraz, thanks for reply. Middle table contains product_id, table_name and table_id. (Table_name is name of other table, like countries or options) For example if a product_id 7 exists in USA, CANADA, INDIA, CHINA (these countries have IDs like; 1, 2, 3, 4 respectively) and options are OPTION-1, OPTION-2, OPTION-3 (these options have IDs like; 1, 2, 3 respectively) then Middle table is like: Middle-table: product_id table_name table_id 7 countries 1 7 countries 2 7 countries 3 7 countries 4 7 options 1 7 options 2 7 options 3 I am not sure, if my database design is correct or not..... If it is not correct can you please guide me? Actually each product have multiple countries, and multiple options.... that's why i design like this. Regards, Burhan Khan Quote Link to comment Share on other sites More sharing options...
fenway Posted August 20, 2008 Share Posted August 20, 2008 Why not country_id? Quote Link to comment Share on other sites More sharing options...
burhankhan Posted August 20, 2008 Author Share Posted August 20, 2008 because table_id can be either country_id or option_id. that's why i named it table_id and i will identify that ID using table_name. For example if table_name is countries, then table_id will be ofcourse ID of country...... Let me know if i am doing wrong? Quote Link to comment Share on other sites More sharing options...
fenway Posted August 20, 2008 Share Posted August 20, 2008 So why not have two "middle tables", as you call them? Clearly they relate two completely separate things. Quote Link to comment Share on other sites More sharing options...
revraz Posted August 20, 2008 Share Posted August 20, 2008 You really only have 3 tables that holds the data you want to reference: Products Countries Options With that in mind, you just need a 4th table to link them together. Each of the three tables will store ID Description With that, your fourth table only stores the related IDs. You can then do a JOIN to grab the Description anytime you like based on the ID. Quote Link to comment Share on other sites More sharing options...
burhankhan Posted August 20, 2008 Author Share Posted August 20, 2008 ok i understand. So middle table should be like: product_id | country_id | option_id | Right? But i think it will not allow me to have multiple countries and multiple options against each product??? If i am wrong, what should be design of Middle Table? Quote Link to comment Share on other sites More sharing options...
revraz Posted August 20, 2008 Share Posted August 20, 2008 Sure it would. 1 | 1 | 1 1 | 2 | 1 1 | 1 | 2 So product ID 1 has links to country_id 1 and 2 as well as option_id 1 and 2 Quote Link to comment Share on other sites More sharing options...
burhankhan Posted August 20, 2008 Author Share Posted August 20, 2008 Will following BOLD values are NOT un-nessassary? 1 | 1 | 1 1 | 2 | 1 1 | 1 | 2 What should be complete Optimized query to get all products that have country_id = 2? I know joins or inner queries should be use, but don't know how.... Can you please provide me complete query? Quote Link to comment Share on other sites More sharing options...
burhankhan Posted August 21, 2008 Author Share Posted August 21, 2008 Is there no one for help me? Quote Link to comment Share on other sites More sharing options...
fenway Posted August 21, 2008 Share Posted August 21, 2008 Will following BOLD values are NOT un-nessassary? 1 | 1 | 1 1 | 2 | 1 1 | 1 | 2 What should be complete Optimized query to get all products that have country_id = 2? I know joins or inner queries should be use, but don't know how.... Can you please provide me complete query? I don't understand what you mean. Quote Link to comment Share on other sites More sharing options...
burhankhan Posted August 22, 2008 Author Share Posted August 22, 2008 ok here is my database design and its values: I want two queries: 1- get all products that have country USA. 2- get all products that have option_id 1. Can you help me? Quote Link to comment Share on other sites More sharing options...
AjBaz100 Posted August 22, 2008 Share Posted August 22, 2008 You need to put the product_id in the countries table so you can do a join. Currently there is no way of linking a product to a country. Quote Link to comment Share on other sites More sharing options...
Hooker Posted August 22, 2008 Share Posted August 22, 2008 Can you post what you have so far? (table structures/queries) or are you waiting for someone to write it for you? Quote Link to comment Share on other sites More sharing options...
burhankhan Posted August 22, 2008 Author Share Posted August 22, 2008 Yes i have this: I want two queries: 1- get all products that have country USA. 2- get all products that have option_id 1. Quote Link to comment Share on other sites More sharing options...
Hooker Posted August 22, 2008 Share Posted August 22, 2008 Something along the lines of: SELECT * FROM (products LEFT JOIN middle_table ON products.product_id = middle_table.product_id) LEFT JOIN countries ON middle_table.country_id = countires.country_id; Should work fine, modify it to suit your second similar query Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.