Joining tables that have identical column names

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
snappydesigns
Forum Commoner
Posts: 28
Joined: Tue Feb 13, 2007 11:37 am

Joining tables that have identical column names

Post by snappydesigns »

Hi,

I'm trying to join tables that have identical column names, but the data within the tables is different. Here's some background info, in case this doesn't make sense. Each table is in the same category (address labels) but from a different vendor. For instance, I'm trying to join tables stacy_addresslabels with donovan_addresslabels (stacy and donovan are the vendors) where both tables have column names product_id, product_name, item_type, vendor, image_name, thumb_name, etc. {If you're wondering why I don't just combine the tables into one, I want to keep them separated for ease of adding/deleting products and for also making it possible to make the webpages for just the one vendor.} Here's an example of the code I was trying to use to combine these tables:

Code: Select all

$query = 'SELECT product_name, image_name, vendor FROM donovan_addresslabels, stacy_addresslabels WHERE item_id = ' . $id;
and this:

Code: Select all

$query = "SELECT COUNT(*) FROM donovan_addresslabels, stacy_addresslabels";
and this:

Code: Select all

$query = "SELECT thumb_name, product_name, item_id, image_name FROM donovan_addresslabels, stacy_addresslabels LIMIT $start, $display";
I totally know my syntax is wrong (that's why I'm here :wink:). I've searched all over about joining tables, but none of the examples I come across talk about joining tables with the exact same column names. Thanks to anyone who can help this clueless girl out :P . I really appreciate it!

Jen
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You're not looking to JOIN the tables. You're looking to UNION the tables. Frankly, this would be a lot easy (on the whole) if they were in the same table. Yes, even maintenance for each vendor is quite simple when they are in a single table. You would add a column specifying the vendor the product is from. Also, generally you wouldn't actually delete a product, but simply mark it as not provided by the vendor anymore. The reason behind this is when users look at their order history, they may want to look at the product information. If the record is deleted, that's not possible.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

A further explanation of Feyd's comment

Instead of one table for each "company" have only two tables

Table address labels. This holds address labels for every company

Code: Select all

address_label_id integer,
  company_id integer
  firstline varchar(64)
  secondline varchar(64)
  postcode varchar(16)
  etc....
Table company. This is the details of each company other than address labels

Code: Select all

company_id integer
   company_name varchar(32)
   etc...
You can then join the two labels using company_id. This has the advantage of being able to easily add more companies by simply adding additional information within the database values rather than having to add new tables and possibly having to change additional code.
snappydesigns
Forum Commoner
Posts: 28
Joined: Tue Feb 13, 2007 11:37 am

Post by snappydesigns »

Wow, thanks a bunch everyone. I've got a lot to consider here. Streamlining my database/products is definitelyThe problem I'm foreseeing with combining the tables into one is how would I display data from just one vendor? I'm sure this is basic but I haven't found how to do this yet.

If you haven't figured out the products my client is selling here is stationery (http://www.twinpapers.com). The other problems I'm running into on the individual product page and order pages is that each vendor has different quantities (sets of 12 versus sets of 10 versus sets of 25), fonts, inks, or pages in a notepad. My head is spinning with how to combine all this so that the proper info is displayed for the specific vendor. Up 'til now I've just displayed the information by vendor, but the client would like to be able to display the products in a category for all vendors at once.

Any ideas for how to organize this?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

snappydesigns wrote:Wow, thanks a bunch everyone. I've got a lot to consider here. Streamlining my database/products is definitelyThe problem I'm foreseeing with combining the tables into one is how would I display data from just one vendor? I'm sure this is basic but I haven't found how to do this yet.

Code: Select all

SELECT `data` FROM `products` WHERE `vendor` = 'foo';
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Ok this isn't an easy topic to go into briefly so I won't attempt to. What I would like to point out is the following
Wiki Database Normalization. You may want to start investigating this in more depth (Google). Good database design early on saves loads of hassle later.

Another useful link when designing databases, especially if using MySql, is fabforce dbdesigner an application where you can visually see a database design. The MySql team are putting their own version together called Workbench but it is only in apha version at the moment
Post Reply