Page 1 of 1

joins Question Help Please

Posted: Thu Sep 02, 2004 6:42 pm
by Subliminal
Hey Gang...
Yup Me again...

So I have a question about relations. I have mastered the art of querying and inserting within 1 table.. now I need to learn how to do it over 2 tables.

Lets say I have 2 tables

TABLE1 (cars)
id
car_name
car_type

TABLE 2 (parts)
id
part_name
car_relation

Now what I need to do if FIND ALL CARS THAT CONTAIN A CERTAIN PART AND THEN LIST THEM -

So I search for

part# 1001

My Search results would look like:

Honda Civic
Parts In This Car Include
1001
1002
1003

Toyota
Parts In The Car Include
1001
1004
1005

----

So it shows all cars that contain part # 1001 and also lists all other parts that that car contains as well... If that isn't possible though the query its not that big a deal it can list just the part that was searched for...

Can someone give me an example query for the situation above. I have come up empty and even more confused when reading about outter joins and natural joins..

Thanks a million

I owe this forum forever!

Posted: Thu Sep 02, 2004 8:17 pm
by feyd
I can think of a way to do the whole other parts thing right now. So.... *deep breath*

I'd switch to a 3 table set up:
  1. cars
  2. parts
  3. linkage
this allows for 1 part to be on many cars, and many cars to have many parts; a many-to-many relationship. You actually could break it down further with having a Manufacturers table, a car models table to give you the specs and junk for a stock car, and a cars table for the individual cars so you can have their aftermarket parts lists and things. Anyway, for this set up, 3 tables is the key for now. Here's the table structure I'm using:

Code: Select all

#
# Table structure for table `car_parts`
#

DROP TABLE IF EXISTS car_parts;
CREATE TABLE car_parts (
  link_car_id int(10) unsigned NOT NULL default '0',
  link_part_id int(10) unsigned NOT NULL default '0',
  KEY car_id (link_car_id,link_part_id)
) TYPE=MyISAM;

#
# Dumping data for table `car_parts`
#

INSERT INTO car_parts (link_car_id, link_part_id) VALUES (1, 1);
INSERT INTO car_parts (link_car_id, link_part_id) VALUES (1, 1);
INSERT INTO car_parts (link_car_id, link_part_id) VALUES (1, 1);
INSERT INTO car_parts (link_car_id, link_part_id) VALUES (1, 2);
INSERT INTO car_parts (link_car_id, link_part_id) VALUES (2, 1);
INSERT INTO car_parts (link_car_id, link_part_id) VALUES (3, 3);

# --------------------------------------------------------

#
# Table structure for table `cars`
#

DROP TABLE IF EXISTS cars;
CREATE TABLE cars (
  car_id int(10) unsigned NOT NULL auto_increment,
  car_make varchar(10) NOT NULL default '',
  PRIMARY KEY  (car_id),
  KEY make (car_make)
) TYPE=MyISAM;

#
# Dumping data for table `cars`
#

INSERT INTO cars (car_id, car_make) VALUES (1, 'Honda');
INSERT INTO cars (car_id, car_make) VALUES (2, 'Honda');
INSERT INTO cars (car_id, car_make) VALUES (3, 'Toyota');

# --------------------------------------------------------

#
# Table structure for table `parts`
#

DROP TABLE IF EXISTS parts;
CREATE TABLE parts (
  part_id int(10) unsigned NOT NULL auto_increment,
  part_name varchar(10) NOT NULL default '',
  PRIMARY KEY  (part_id),
  KEY parts_name (part_name)
) TYPE=MyISAM;

#
# Dumping data for table `parts`
#

INSERT INTO parts (part_id, part_name) VALUES (1, 'part1');
INSERT INTO parts (part_id, part_name) VALUES (2, 'part2');
INSERT INTO parts (part_id, part_name) VALUES (3, 'part3');

Code: Select all

SELECT  cars.*, parts.*, count(part_id) qty
FROM cars
INNER  JOIN parts
INNER  JOIN car_parts ON car_id = link_car_id AND part_id = link_part_id
GROUP BY car_id, part_id
will tell you where and which parts are used.

Code: Select all

SELECT  DISTINCT  car_id
FROM cars
INNER  JOIN car_parts ON car_id = link_car_id
WHERE link_part_id = 1
will tell you which car's are using part X
you can use the results of this last query to find all the parts of each of those cars:

Code: Select all

<?php
//........

$part_id = 1;
$sql = 'SELECT  DISTINCT  car_id
FROM cars
INNER  JOIN car_parts ON car_id = link_car_id
WHERE link_part_id = ' . $part_id;

$query = mysql_query($sql) or die(mysql_error());
$cars = array();
while($row = mysql_fetch_row($query)) $cars[] = intval($row[0]);
mysql_free_result($query);
if(!sizeof($cars))
  die('no cars matched: ' . $part_id);

$sql = 'SELECT  cars.*, parts.*, count(part_id) qty
FROM cars
INNER JOIN parts
INNER  JOIN car_parts ON car_id = link_car_id AND part_id = link_part_id
WHERE car_id IN(' . implode(',',$cars) . ')
GROUP BY car_id, part_id';

$query = mysql_query($sql) or die(mysql_error());
$list = array();
while($row = mysql_fetch_assoc($query)) $list[] = $row;
mysql_free_result($query);

echo '<pre>' . var_export($list,true) . '</pre>';

//........
?>

Code: Select all

SELECT  cars.*, parts.*, count(part_id) qty
FROM cars
INNER JOIN parts
INNER  JOIN car_parts ON car_id = link_car_id AND part_id = link_part_id
WHERE car_id IN(1,2)
GROUP BY car_id, part_id

Posted: Thu Sep 02, 2004 11:34 pm
by Subliminal
Wow you are totally kewl.

I actually have a setup of 3 tables already one contains the parts the other is the car and then the 'linkage' table. The only difference is that I was inserting all the parts data into the linkage table with the relation ids

This specfic query worked wonderfully for my purpose.

Code: Select all

SELECT  cars.*, parts.*, count(part_id) qty 
FROM parts 
INNER  JOIN cars 
INNER  JOIN car_parts ON car_id = link_car_id AND part_id = link_part_id 
WHERE part_id = '1'
GROUP BY car_id, part_id;
However I would also like to make it so the user can search by part name in boolean mode. This may sound a little odd but we aren't actually talking about cars and their parts it is something else I just used it as an example. So lets say the part name was "rear brake" they searched for "brake -rear"

How can I alter this code to work for this purpose?


???????????????????????

Code: Select all

SELECT  cars.*, parts.*, count(part_id) qty 
FROM parts 
INNER  JOIN cars 
INNER  JOIN car_parts ON car_id = link_car_id AND part_id = link_part_id 
WHERE part_name MATCH 'brake -rear' IN BOOLEAN MODE
GROUP BY car_id, part_id

THANK YOU!!!!!!!!!