Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    8

    Post Unanswered: Too many left joins? - newbie stuff, help needed.

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    have you tried running those statements? because i think they will fail

    this won't work --
    Code:
    foreign key (id) references multi_language_text(id)
    since the multi_language_text table has a composite primary key, you cannot reference only part of it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2004
    Posts
    8
    It does (MySQL?), although when writing the query i was sure that the standard would not allow it.

    Anyways, apart from the constraints, do you think the left join style query is any effective ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the tables have to be InnoDB in order for the foreign keys to work

    as far as your left join query is concerned, i cannot comment on it because i don't understand your table design
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2004
    Posts
    8
    All tables are supposed to be InnoDB. I dont know how it slipped out only product_packages specifies the InnoDB engine... In my sql files all tables specify InnoDB engine.

    The table design is (poorly?) explained in the topic.

    Thanks for the help. For now I left the query as is, because the practical speed implications are negliagible (for now).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •