Jump to content

Getting Data From Multiple Tables


burhankhan

Recommended Posts

Hi:

 

I want to get data from multiple tables.

I have designed a design of my database in MS Access.

 

dbnf5.gif

 

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.