Getting Data From Multiple Tables

[burhankhan]

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

[revraz]

Have the middle table store the product_id, country_id and option_id for each record.

[burhankhan]

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

[fenway]

Why not country_id?

[burhankhan]

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?

[fenway]

So why not have two "middle tables", as you call them? Clearly they relate two completely separate things.

[revraz]

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.

[burhankhan]

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?

[revraz]

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

 

[burhankhan]

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?

[burhankhan]

Is there no one for help me?

[fenway]

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.

[burhankhan]

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?

 

[AjBaz100]

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.

[Hooker]

Can you post what you have so far? (table structures/queries) or are you waiting for someone to write it for you?

[burhankhan]

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.

[Hooker]

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