List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:June 30 1999 4:45pm
Subject:Re: Fw: Question concering data retrieval from multiple tables...
View as plain text  
Technische Dienst wrote:
> 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
> 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

Apparently Art_Num is present in both Towers and CPU. You must be more
specific when joining the tables that have a column with the same name
when referring to in in the WHERE clause:

SELECT * FROM Towers,CPU where Towers.Art_Num = CPU.Art_Num and
CPU.Art_Num = 03025

Sasha Pachev (home) (work)
Fw: Question concering data retrieval from multiple tables...Technische Dienst30 Jun
  • Re: Fw: Question concering data retrieval from multiple tables...Sasha Pachev30 Jun