MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Andrew Dunstan Date:June 29 1999 2:43pm
Subject:Re: Question concering data retrieval from multiple tables...
View as plain text  
As I understand the problem you want a union but your query asks for a 
product.

The best solution to your problem is probably not to have separate tables 
for each product type but to have one table with an extra column specifying 
the product type.

e.g. create table products (Art_Num int(5) zerofill not null primary key, 
Product_Type text not null, Art_Name text not null, Art_Price float(5,2) not 
null);

Now you could simply do:

select * from products where Art_Num = 03025

If you just wanted CPUs you could do

select * from products where Art_Num = 03025 and Product_Type = 'CPU'

Hope this helps

andrew


>From: "Technische Dienst" <td@stripped>
>To: <mysql@stripped>
>Subject: Question concering data retrieval from multiple tables...
>Date: Tue, 29 Jun 1999 12:23:27 -0400
>
>Hi there, I'm sortta having a big problem.
>
>For my work I'm putting our price list into MySQL (or atleast I'm trying 
>to)...
>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.
>Tables are all alike and were built like this.
>create table table-name (Art_Num int(5) zerofill not null primary key, 
>Art_Name text not null, Art_Price float(5,2) not null);
>
>Now the html form will send back the article number and the number of how 
>much the person wants from this article.
>
>Let's keep it simple, I have 2 tables Towers & CPU looking like this
>Towers.
>03025 Sales ATX Midi Tower 89.30
>03026 Sales ATX Full Tower 129.00
>
>CPU
>01034 Intel Pentium-II 450 489.30
>01035 Intel Pentium-III 450 509.83
>
>There's more in there and prices etc don't match but that doesn't matter 
>here.
>Anyways I get back the article number so I want to search that item in all 
>the tables and I want the whole line in return.
>select * from Towers, CPU where Art_Num=03025; (just picked a number)
>gives me:
>ERROR 1052: Column: 'Art_Num' in where clause is ambiguous
>
>however if I do:
>mysql> select * from Towers where Art_Num=03025;
>+---------+----------------------+-----------+
>| Art_Num | Art_Name             | Art_Price |
>+---------+----------------------+-----------+
>|   03025 | Sales ATX Midi Tower |     89.00 |
>+---------+----------------------+-----------+
>1 row in set (0.01 sec)
>
>it does work...
>
>How come I can't make it search like that in more tables at once? Is there 
>something wrong with my command line?
>
>Thanks for your time,
>
>Ferry van Steen


_______________________________________________________________
Get Free Email and Do More On The Web. Visit http://www.msn.com
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