MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Clyde Smith-Stubbs Date:June 29 1999 11:26am
Subject:Re: Question concering data retrieval from multiple tables...
View as plain text  
Now I'm no database expert, so the database experts may correct me without
fear, but...

On Tue, Jun 29, 1999 at 12:23:27PM -0400, Technische Dienst wrote:

> For each section we have (we're into computers so we have like towers, cpu's, etc) I
> made a different table since this was the easiest way for me to make perl generate
> HTML-tables. It reads an entire SQL-Table converts it to HTML-table and this part works
> fine.

I think this is the root of your problems; what I would have done was to use
one table (basically, my view is that if you have two tables with identical structures
storing data about similar things, they should be combined) and have another
field to store the type of the item. This field would be an index into another
table that listed the possible kinds of things (so you can dynamically add
new types of products).

> select * from Towers, CPU where Art_Num=03025; (just picked a number)

The reason this doesn't work is because the column Art_Num could refer to the
column of that name in either table. The simple answer to this is:

select * from Towers, CPU where Towers.Art_Num=03025 or CPU.Art_Num=03025;

HOWEVER! This will give you a result, but it will not be the result you want;
each row of the result will have fields from both tables, and you will
get a very large number of rows.

The result you want (i.e. a set of rows containing records from either table)
is not achievable in this way since every row in this result will contain data
from one record each in both tables. To do what you want you will need to
make two queries, one from each table, then merge the results.

Which brings me back to my first comment; your database design is wrong.
If you combine the tables, then your query is easy, and your other requirement
(extracting the rows for one class of product) can be achieved like this:

Have another table, e.g. Article_classes with columns

class_id   	int
class_name	varchar

Now to select just the product records for say CPUs, you would do

select * from Article_classes, Products where Article_classes.class_name='CPU' and
       Article_classes.class_id=Products.class_id; 

IOW this produces a result where every row is the concatenation (join) of
one row from Article_classes, and one row from Products, but limited to
only those products that have a class that corresponds to CPU.


Regards, Clyde

-- 
Clyde Smith-Stubbs               |            HI-TECH Software
Email: clyde@stripped          |          Phone            Fax
WWW:   http://www.htsoft.com/    | USA: (408) 490 2885  (408) 490 2885
PGP:   finger clyde@stripped   | AUS: +61 7 3355 8333 +61 7 3355 8334
---------------------------------------------------------------------------
HI-TECH C: compiling the real world.
Thread
Question concering data retrieval from multiple tables...Technische Dienst29 Jun
  • Re: Question concering data retrieval from multiple tables...Clyde Smith-Stubbs29 Jun
  • Re: Question concering data retrieval from multiple tables...Colin McKinnon29 Jun
Re: Question concering data retrieval from multiple tables...Andrew Dunstan29 Jun