Hi all.
I would like to get your opinion on how effective my query is, i am sure gurus and alike would see immediately if I am doing some newbie mistakes. I will try to explain my simple database.
basically a table of products exists, where each product has a name, price, qty, unit of qty measure, package, and distributor. The website which operates on this information is multi-lingual, so naturally all text has to have several versions - that includes product names, product package names, and product distributor names. A table for packages and one for distributors also exist.
Code:
create table multi_language_text
(
id int not null,
txt text character set utf8,
language_id int not null,
primary key (id, language_id)
);
create table product_makers
(
id int not null,
primary key (id),
foreign key (id) references multi_language_text(id)
);
create table product_packages
(
id int not null,
primary key (id),
foreign key (id) references multi_language_text(id)
) engine=InnoDB;
create table products
(
id int not null,
maker_id int not null,
package_id int not null,
qty real not null,
unit char not null,
price real not null,
primary key (id, maker_id, package_id, qty, unit),
foreign key (id) references multi_language_text(id),
foreign key (package_id) references product_packages(id),
foreign key (maker_id) references product_makers(id)
);
Now, to retrieve the list of all products, with names, package names, and distributor names instead of numbers, i use following query:
Code:
select
p.id as id, pl.txt as name, pml.txt as maker, ppl.txt as package, p.qty as qty, p.unit as unit, p.price as price
from
products as p
left join
multi_language_text as pl
on p.id = pl.id
left join
multi_language_text as pml
on p.maker_id = pml.id
left join
multi_language_text as ppl
on p.package_id = ppl.id
where pl.language_id =1 and pml.language_id =1 and ppl.language_id =1;
Do you think this is over the top? I am fairly new to SQL (about 1-2 years of here-and-there experience making it maybe 3 months pure SQL time), but I did my reading, and i am also a programmer.
Should i replace the join thing with subqueries ? Also isn't it possible to eliminate text for other languages than the chosen (=1) more effectively, without doing it three times ?
The database is MySQL 4.1, so views are out of the questions..
Thanks.
dBforums
>
Too many left joins? - newbie stuff, help needed.
11-23-06, 16:35
Too many left joins? - newbie stuff, help needed.
Linear Mode
