> Yann Larrivée wrote:
>
> Thanks for the answer but it is a bit more complexe, i actually
> creates these sample table for the example. Here is an attachement of
> the structure dump.
>
> Hummm you got me to think more about my database structure withc is
> really good. The idea you bourght up seems really good.
> I am a junior developper, actually only been 2 month i really program
> soi now have more experience then i used to have spacially when
> people like yuo give me tricks , this really helps me out :-)
>
> CREATE TABLE produits (
> PID varchar(5) NOT NULL,
Why a VARCHAR? Numeric values (INT) are better.
> titre tinytext NOT NULL,
> description text,
> artiste tinyint(3) NOT NULL, Artiste reference number see the artiste table
TINYINT has value range from -128 to 127. It's too small for an ID. How
man artists will your database have?
> image varchar(5) NOT NULL, Image name
> num_page smallint(5), Number of page if a book
> duree smallint(4), Length if video or cd,
>tape ...
> format tinyint(3) NOT NULL, Format of the product hardcover
>softcover, tape , cd, dvd
You better use ENUM instead: format ENUM('softcover','hardcover',...)
> ISBN smallint(10), ISBN number or serial number
A SMALLINT is not enought for an ISBN number. I must be an INT at least,
or even an BIGINT. Check it out in the manual.
> quantite tinyint(3) NOT NULL, Stock quantity
What if you have more then 127 on the stock? see artiste...
> fournisseur tinyint(4) NOT NULL, Provider ID number
> prix float NOT NULL, Our sales prices
> retail float NOT NULL, Retail Price
> UNIQUE PID (PID)
> UNIQUE PID (PID)
> );
> CREATE TABLE artiste (
> ID smallint(5) unsigned NOT NULL auto_increment, artiste ID number
> nom varchar(15) NOT NULL, artiste name
> UNIQUE ID (ID)
> );
>
>
> CREATE TABLE fournisseurs (
> My provider table
> ID tinyint(5) unsigned NOT NULL auto_increment, Provider ID
> Nom varchar(20) NOT NULL,
> Provider Name
> adresse1 varchar(50) NOT NULL,
> addresse1
> adresse2 varchar(50),
> address2
> ville varchar(20) NOT NULL,
> city
> province varchar(20) NOT NULL,
> state/province
> pays varchar(30) NOT NULL,
> country
> telephone varchar(20) NOT
> NULL, phone number
> fax varchar(20),
> fax
> email varchar(25),
> site_web varchar(30),
> website
> pers_ressources varchar(40),
> person to contact
> num_compte varchar(20) NOT
> NULL, account number
> code_postal varchar(7) NOT NULL,
> Postal code
> UNIQUE ID (ID)
> );
>
>
> I got more tables but the others are not interesting in the specific
> query i want to make.
>
SELECT artist.name,produits.name,provider.name FROM artist LEFT JOIN
produits ON artist.ID=produits.artist_ID LEFT JOIN provider ON
produits.provider_ID=provider.ID WHERE provider.name LIKE 'some_name';
Robert
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
