Need to combine/merge/join tables based on item's id number

Databases
1

Greetings,

I have a table called “items” which contains about 30 basic fields. Then I have multiple related tables like “item_shipping_details”, “item_return_policy” and “item_location” which have about 10 fields each. The one thing in common with all of these tables is the item id number.

I split these up into multiple tables so MySQL wouldn’t have to search through a huge table for the basic info.

Now am trying to set up a Google Product Feed which requires little bits of information from each of the different tables. I am using PHP to generate an XML file and I would like to call just 1 query to call up the data and create an output instead of 1000s of tiny queries inside while loops.

Question: How do I do this? There must be some way to temporarily combine the tables so I can get the data I need.

Thanks

2

yup, it’s called a join query

if you’ve never joined tables in a query before, may i suggest searching the web for some basic tutorials?

3

Thanks for the reply, I’ve heard of JOIN, but I also heard of other things like UNION and FOREIGN KEYS being similar. I will look into JOIN.

Another quick question: Let’s say I call up 7 different queries for the 7 different tables to display all item info, would a single query Joining these tables be more efficient or will it take more work for MySQL to do?

Thanks
Kind regards

4

yes, a single query joining all tables (assuming you’re looking for all related rows for a particular item) will be much more efficient