MySQL Query problem

[davidjennings]

Hi all, if anyone can assist I have a query which just will not resolve it self no matter what I have tried.

I am testing this PHPmyadmin before I apply to the website if this helps

Thanks in advance DJ

The query that is being run produces duplicate row of each product

SELECT manufacturer_name, vehicles_model, vehicles_year, vehicles_engine_size, vehicles_fuel_type, products_ID
FROM manufacturer, products, vehicles
WHERE manufacturer_name =  "ALFA ROMEO"
LIMIT 0 , 30;

manufacturer_name   vehicles_model  vehicles_year   vehicles_engine_size    vehicles_fuel_type  products_ID
ALFA ROMEO  75  86-93   1.8, 2.0, 2.5   PETROL  063
ALFA ROMEO  75  86-93   1.8, 2.0, 2.5   PETROL  065
ALFA ROMEO  75  87-93   3.0 PETROL  063
ALFA ROMEO  75  87-93   3.0 PETROL  065
ALFA ROMEO  145 94- n/a PETROL  063
ALFA ROMEO  145 94- n/a PETROL  065
ALFA ROMEO  146 94-     PETROL  063
ALFA ROMEO  146 94-     PETROL  065

[apegram]

You've got a cartesian join. You're selecting multiple tables into your query without providing a joining statement to link the tables together based on 1 or more common fields. As a result, each row of each table is being combined with each row of the other tables so as to produce all possible joins because otherwise the database engine has no idea how you wanted to view the data.

What are the common fields between your data? Do a join on those fields.

[davidjennings]

Hi
The common fields would be the foreign keys which are

FK_manufacturer_ID, vehicle_ID, FK_products_ID

The primary table keys are
vehicle.vehicle_ID
products.products_ID
manufacturer.manufacturer_ID
category.category_ID

would the code go something this?

manufacturer_name,vehicles_model, vehicles_year, products_ID
FROM vehicles, manufacturer,products 
WHERE manufacturer.FK_manufacturer_ID = vehicle.vehicle_ID
and vehicle.vehicle_ID = vehicles.FK_products_ID
and vehicles.FK_products_ID = manufacturer.FK_manufacturer_ID

Thanks

DJ

[drjohn]

you'd really have to show us the table schemas before we can answer which fields to use in the join.

also it gets a bit easier to read if you use table aliases eg

SELECT m.fname, m.sname, s.game, l.league
FROM membership as m
JOIN gameplayed as g ON m.mid=g.mid
JOIN leaguetype as l ON g.gid=l.gid
WHERE.....

[davidjennings]

Thanks for your reply, I am on a learning curve so if I haven't got it right, please point me in the right direction

Thanks in-advance

Cheers

DJ

CREATE TABLE `vehicles` (
  `vehicles_id` int(11) NOT NULL auto_increment,
  `vehicles_model` varchar(30) NOT NULL,
  `vehicles_year` varchar(20) NOT NULL,
  `vehicles_engine_size` varchar(30) NOT NULL,
  `vehicles_fuel_type` varchar(25) NOT NULL default 'PETROL',
  `FK_manufacturer_ID` int(11) NOT NULL,
  `FK_category_ID` int(11) NOT NULL,
  `FK_products_ID` varchar(25) NOT NULL,
  PRIMARY KEY  (`vehicles_id`),
  KEY `FK_manufacturer_ID` (`FK_manufacturer_ID`),
  KEY `FK_category_ID` (`FK_category_ID`),
  KEY `FK_products_ID` (`FK_products_ID`)
) ENGINE=MyISAM

CREATE TABLE `products` (
  `products_id` varchar(25) NOT NULL,
  `products_price` varchar(35) NOT NULL,
  `products_ah_20hr` varchar(20) default NULL,
  `products_sae_cold_start` varchar(20) default NULL,
  `products_bench_amps` varchar(20) default NULL,
  `products_length` varchar(20) NOT NULL,
  `products_width` varchar(20) NOT NULL,
  `products_height` varchar(20) NOT NULL,
  `products_weight` varchar(20) NOT NULL,
  `products_layout` varchar(20) default NULL,
  `products_terminals` varchar(20) default NULL,
  `products_base_holddown` varchar(20) default NULL,
  `products_notes` varchar(20) default NULL,
  `products_ah_5hr` varchar(15) default NULL,
  `products_ah_cap` varchar(15) NOT NULL,
  `products_pulse_amps` varchar(15) default NULL,
  `products_reserve_capacity` varchar(15) default NULL,
  `products_Image` varchar(64) default NULL,
  `products_quantity` varchar(10) default '1',
  `products_last_modified` datetime default NULL,
  `products_date_added` datetime default NULL,
  `products_warranty` varchar(20) default '2',
  `products_other2` varchar(20) default NULL,
  `products_other3` varchar(20) default NULL,
  `products_other4` varchar(20) default NULL,
  `products_output_voltage` varchar(15) default NULL,
  PRIMARY KEY  (`products_id`),
  KEY `products_notes` (`products_notes`)
) ENGINE=MyISAM

CREATE TABLE `manufacturer` (
  `manufacturer_ID` int(11) unsigned NOT NULL auto_increment,
  `manufacturer_name` varchar(255) NOT NULL,
  PRIMARY KEY  (`manufacturer_ID`),
  UNIQUE KEY `manufacturer_name` (`manufacturer_name`)
) ENGINE=MyISAM

CREATE TABLE `category` (
  `category_id` int(11) NOT NULL auto_increment,
  `category_name` varchar(50) NOT NULL,
  PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM

[drjohn]

Okay it looks like the ERD is manufacturer--< vehicles >--products
Ie a manufacturer makes many vehicles, and a product is used by many vehicles

That means this might be what you are after

SELECT m.manufacturer_name, v.vehicles_model, v.vehicles_year, v.vehicles_engine_size, v.vehicles_fuel_type, p.products_ID
FROM manufacturer as m
JOIN vehicles as v on m.manufacturer_ID = v.FK_manufacturer_ID
JOIN products as p on p.products_id = v.FK_products_ID
WHERE manufacturer_name = "ALFA ROMEO"
LIMIT 0 , 30;

But that is possibly not what you really, really want. It implies from your table design that one product is used in many vehicles, but each vehicle uses only ONE product. Is that right?

Are you sure there isn't a linking table, as a more normal (sic) structure would be that a vehicle uses many products and a product is used in many vehicles ie vehicles>--<products
which would then require the linking table vehicleproducts, with the following relationship
vehicles--<vehicleproducts>--products

and vehicleproducts (vehicle_ID, product_id) with a joint primary key.
Unless of course this is a very special product and a vehicle only uses one eg one model of turbocharger is used, and the web site is to sell the one model they make that suits a given car perfectly. (I saw a site just like that two days ago).

[davidjennings]

Hi thanks for your help on this and for going into so much detail.

The ERD representation you have given is exactly how I had interpreted it on paper

manufacturer--< vehicles >--products

One product (battery) has (fits) many vehicles
One manufacturer has many vehicles

I will be needing extract the data by category_ID would this be a similar query?

It works fantastically Thanks again for your expert advise It is good to know that help is available when things don't go right.

Just out of interest where had you seen this before?
Thanks again

DJ

[drjohn]

re by category - yes just substitute the category bits for the manufacturer bits.

[davidjennings]

Thanks

DJ