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!
joins Question Help Please
Moderator: General Moderators
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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:
will tell you where and which parts are used.
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:
I'd switch to a 3 table set up:
- cars
- parts
- linkage
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_idCode: Select all
SELECT DISTINCT car_id
FROM cars
INNER JOIN car_parts ON car_id = link_car_id
WHERE link_part_id = 1you 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-
Subliminal
- Forum Commoner
- Posts: 40
- Joined: Thu Oct 23, 2003 8:12 pm
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.
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?
???????????????????????
THANK YOU!!!!!!!!!
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;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_idTHANK YOU!!!!!!!!!