Re: Complex mysql query

Wed, 28 Mar 2001 09:19:23 -0800

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 (                    My product table
   PID varchar(5) NOT NULL,              Product ID
   titre tinytext NOT NULL,                    Title
   description text,                                    product descrition
   artiste tinyint(3) NOT NULL,                Artiste reference number see the artiste table
   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
   ISBN smallint(10),                                ISBN number or serial number
   quantite tinyint(3) NOT NULL,            Stock quantity
   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.
 
What do you think about this structure. If you want teh files are also attatched in txt format so you can take a look at it in TextPad. :-)
 
Thanks for your comments i really appreciate :-)
 
Yann
----- Original Message -----
From: "Bob Hall" <[EMAIL PROTECTED]>
To: "Yann Larrivée" <[EMAIL PROTECTED]>
Sent: Wednesday, March 28, 2001 6:14 AM
Subject: Re: Complex mysql query

> >Sorry i think this is my mistake the format that i gave did not get reoected
> >by the mailing liste
> >
> >so my structure is
> >
> >provider                      books             music
> >video
> >provider_id                title                   title
> >title
> >provider_name         provider_id   provider_id       provider_id
> >
> >
> >I am making a smal search engine for the site.  people can look up all the
> >books, cd, video from a specific provider. So i need to out put all the
> >title that correspond to the "keyword they entered" witch would be the
> >providers name right ... and the providers name
> >
> >Yann
> >
> >----- Original Message -----
> >From: "Bob Hall" <[EMAIL PROTECTED]>
> >To: <[EMAIL PROTECTED]>
> >Sent: Tuesday, March 27, 2001 8:26 AM
> >Subject: Re: Complex mysql query
> >
> >
> >  > >Hi everybody. I got a complexe question well according to me. I am
> >  > >working with PHP and i am making some queries to some database. here
> >  > >is a description of the tables.
> >  > >
> >  > >       provider book video music
> >  > >       provider_id title title title
> >  > >       name provider_id provider_id provider_id
> >  > >
> >  > >
> >  > >
> >  > >I need to select all the title from book, video, music that matches
> >  > >a provider name and not a provider id.
> >  > >
> >  > >I know i can make 2 queries to do it but i am shure i can do a
> >  > >complexe join somewhere that would allow me only to connect once. I
> >  > >want to do it this one because to connect twince to a db could slow
> >  > >down the performance.
> >  > >
> >  > >This problem goes beyound my knowledge of MySQL a bit of help would
> >  > >be appreciated :-)
> >  > >
> >  > >Thanks
> >  > >
> >  > >
> >  > >Yann Larrivée
> >  > >www.ProtonicDesign.Com
> >  >
> >  > Sir, I doubt that you have a table with three columns with the same
> >  > name. If I am wrong, then your copy is MySQL is broken.
> >  >
> >  > If your table structure is what I think it is (It's impossible to be
> >  > sure from your description), it's going to cause you a lot of
> >  > problems. Get a book on relational database design and learn to
> >  > normalize tables. In the interim, if you post a description of what
> >  > you are trying to do, there are a number of active contributors here
> >  > who can make suggestions for a schema.
>
> Sir, I apologize for mis-reading your schema. The textbook way of
> describing a table structure is
>     table_name(column1_name, column2_name, ...)
> A more common representation on the list is
>     table_name
>     column1_name, column2_name, ...
> or to give the CREATE TABLE statement. I tend to read the list
> between 4:30 and 5:30 in the morning, before coffee. If you post a
> schema in a novel format, there's a very high probability that I will
> misunderstand it.
>
> You can get get what you want from your tables with an outer join.
>     SELECT *
>     FROM ((provider p NATURAL LEFT JOIN book)
>           LEFT JOIN video v ON p.provider_id = v.provider_id)
>          LEFT JOIN music m ON p.provider_id = m.provider_id
>     WHERE p.name = 'name';
>
> This is inefficient because the server won't use indices on the table
> being preserved. The basic problem is that your three media tables
> should be one table. I would structure it as
>     files(title, provider, medium)
> where medium is type ENUM restricted to 'book', 'video', or 'music'.
> Then you can use a much faster SELECT statement.
>     SELECT *
>     FROM provider p INNER JOIN files f ON p.provider_id = f.provider_id
>     WHERE p.name = 'name';
>
> Bob Hall
>
> Know thyself? Absurd direction!
> Bubbles bear no introspection.     -Khushhal Khan Khatak
> 
# phpMyAdmin MySQL-Dump
#
# localhost: localhost : christiansolution_com
# --------------------------------------------------------

#
#  'produits'
#

CREATE TABLE produits (
   PID varchar(5) NOT NULL,
   titre tinytext NOT NULL,
   description text,
   artiste tinyint(3) NOT NULL,
   image varchar(5) NOT NULL,
   num_page smallint(5),
   duree smallint(4),
   format tinyint(3) NOT NULL,
   ISBN smallint(10),
   quantite tinyint(3) NOT NULL,
   fournisseur tinyint(4) NOT NULL,
   prix float NOT NULL,
   retail float NOT NULL,
   UNIQUE PID (PID)
);

# phpMyAdmin MySQL-Dump
#
# localhost: localhost : christiansolution_com
# --------------------------------------------------------

#
#  'fournisseurs'
#

CREATE TABLE fournisseurs (
   ID tinyint(5) unsigned NOT NULL auto_increment,
   Nom varchar(20) NOT NULL,
   adresse1 varchar(50) NOT NULL,
   adresse2 varchar(50),
   ville varchar(20) NOT NULL,
   province varchar(20) NOT NULL,
   pays varchar(30) NOT NULL,
   telephone varchar(20) NOT NULL,
   fax varchar(20),
   email varchar(25),
   site_web varchar(30),
   pers_ressources varchar(40),
   num_compte varchar(20) NOT NULL,
   code_postal varchar(7) NOT NULL,
   UNIQUE ID (ID)
);

# phpMyAdmin MySQL-Dump
#
# localhost: localhost : christiansolution_com
# --------------------------------------------------------

#
#  'artiste'
#

CREATE TABLE artiste (
   ID smallint(5) unsigned NOT NULL auto_increment,
   nom varchar(15) NOT NULL,
   prenom varchar(15) NOT NULL,
   photo varchar(5),
   UNIQUE ID (ID)
);


---------------------------------------------------------------------
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

Reply via email to