Join Query advice

[oldschool]

I want to list the manufactuers names where a product's sub_cat_id equals a particular value. I've tried lot of combinations of queries but none seem to work. The most recent below is an example.

For example.

[code]SELECT `tsav_products`.`product_manufacturer_id` FROM `tsav_products` WHERE `tsav_products`.`product_sub_cat_id` = 1[/code]

Would give me a list of

1
1
5

I then want to match these to the manufacturers table, so displaying a list of manufactuers based on the products, but I do not want to have any manufacturer names to repeat, so for example, the two entries of '1' would only be displayed once.

I've tried a number of different query with joins here there and everywhere but can't seem to crack it. Can anybody help me out here please? I've include a dump for anybody to test


[code]
--
-- Table structure for table `tsav_main_categories`
--

CREATE TABLE `tsav_main_categories` (
  `main_cat_id` int(11) NOT NULL auto_increment,
  `main_cat_name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`main_cat_id`)
) TYPE=MyISAM COMMENT='main categories' AUTO_INCREMENT=9;

--
-- Dumping data for table `tsav_main_categories`
--

INSERT INTO `tsav_main_categories` VALUES (1, 'Hi-Fi');
INSERT INTO `tsav_main_categories` VALUES (2, 'Home Cinema');
INSERT INTO `tsav_main_categories` VALUES (3, 'PA, DJ & Recording');
INSERT INTO `tsav_main_categories` VALUES (4, 'Musical Instruments');
INSERT INTO `tsav_main_categories` VALUES (5, 'Theatre & Stage Lighting');
INSERT INTO `tsav_main_categories` VALUES (6, 'Plasma, Projection and LCD');

-- --------------------------------------------------------

--
-- Table structure for table `tsav_manufacturers`
--

CREATE TABLE `tsav_manufacturers` (
  `manufacturer_id` int(11) NOT NULL auto_increment,
  `manufacturer_name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`manufacturer_id`)
) TYPE=MyISAM AUTO_INCREMENT=86;

--
-- Dumping data for table `tsav_manufacturers`
--

INSERT INTO `tsav_manufacturers` VALUES (9, 'Inca Designs    ');
INSERT INTO `tsav_manufacturers` VALUES (10, 'Myryad    ');
INSERT INTO `tsav_manufacturers` VALUES (12, 'Roksan    ');
INSERT INTO `tsav_manufacturers` VALUES (13, 'Bandridge    ');
INSERT INTO `tsav_manufacturers` VALUES (14, 'Chord    ');
INSERT INTO `tsav_manufacturers` VALUES (15, 'Merlin Cables    ');
INSERT INTO `tsav_manufacturers` VALUES (16, 'QED    ');
INSERT INTO `tsav_manufacturers` VALUES (17, 'Van Den Hal    ');
INSERT INTO `tsav_manufacturers` VALUES (19, 'Onkyo    ');
INSERT INTO `tsav_manufacturers` VALUES (26, 'Pioneer    ');
INSERT INTO `tsav_manufacturers` VALUES (27, 'Alphason Designs    ');
INSERT INTO `tsav_manufacturers` VALUES (28, 'Arca    ');
INSERT INTO `tsav_manufacturers` VALUES (29, 'Sound Organisation    ');
INSERT INTO `tsav_manufacturers` VALUES (30, 'Sound Style    ');
INSERT INTO `tsav_manufacturers` VALUES (32, 'Beyerdynamic    ');
INSERT INTO `tsav_manufacturers` VALUES (33, 'Grado    ');
INSERT INTO `tsav_manufacturers` VALUES (34, 'Sennheiser    ');
INSERT INTO `tsav_manufacturers` VALUES (35, 'Isol-8    ');
INSERT INTO `tsav_manufacturers` VALUES (36, 'Isotek    ');
INSERT INTO `tsav_manufacturers` VALUES (38, 'Acoustic Energy    ');
INSERT INTO `tsav_manufacturers` VALUES (43, 'MJ Acoustics    ');
INSERT INTO `tsav_manufacturers` VALUES (45, 'Rel    ');
INSERT INTO `tsav_manufacturers` VALUES (46, 'Sun Fire    ');
INSERT INTO `tsav_manufacturers` VALUES (49, 'Audio Technica    ');
INSERT INTO `tsav_manufacturers` VALUES (50, 'Goldring    ');
INSERT INTO `tsav_manufacturers` VALUES (51, 'Michell Engineering    ');
INSERT INTO `tsav_manufacturers` VALUES (52, 'Origin Live    ');
INSERT INTO `tsav_manufacturers` VALUES (53, 'Project    ');
INSERT INTO `tsav_manufacturers` VALUES (54, 'SME    ');
INSERT INTO `tsav_manufacturers` VALUES (59, 'Arcam    ');
INSERT INTO `tsav_manufacturers` VALUES (61, 'Finlux    ');
INSERT INTO `tsav_manufacturers` VALUES (62, 'Lexicon    ');
INSERT INTO `tsav_manufacturers` VALUES (64, 'Primare    ');
INSERT INTO `tsav_manufacturers` VALUES (65, 'Teac    ');
INSERT INTO `tsav_manufacturers` VALUES (70, 'Parasound    ');
INSERT INTO `tsav_manufacturers` VALUES (73, 'Wharfedale    ');
INSERT INTO `tsav_manufacturers` VALUES (77, 'Audio Pro    ');
INSERT INTO `tsav_manufacturers` VALUES (78, 'Denon    ');
INSERT INTO `tsav_manufacturers` VALUES (79, 'Quad    ');
INSERT INTO `tsav_manufacturers` VALUES (82, 'Densen    ');
INSERT INTO `tsav_manufacturers` VALUES (84, 'Definitive Technology    ');
INSERT INTO `tsav_manufacturers` VALUES (85, 'Audiovector    ');

-- --------------------------------------------------------

--
-- Table structure for table `tsav_products`
--

CREATE TABLE `tsav_products` (
  `product_id` int(11) NOT NULL auto_increment,
  `product_sub_cat_id` int(11) NOT NULL default '0',
  `product_manufacturer_id` int(11) NOT NULL default '0',
  `product_name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`product_id`)
) TYPE=MyISAM AUTO_INCREMENT=5;

--
-- Dumping data for table `tsav_products`
--

INSERT INTO `tsav_products` VALUES (1, 1, 1, 'arcam product 1');
INSERT INTO `tsav_products` VALUES (2, 1, 1, 'arcam product 2');
INSERT INTO `tsav_products` VALUES (3, 1, 5, 'sony product 1');
INSERT INTO `tsav_products` VALUES (4, 2, 4, 'phillips product 1');

-- --------------------------------------------------------

--
-- Table structure for table `tsav_sub_categories`
--

CREATE TABLE `tsav_sub_categories` (
  `sub_cat_id` int(11) NOT NULL auto_increment,
  `sub_cat_name` varchar(255) NOT NULL default '',
  `sub_main_cat` int(11) NOT NULL default '0',
  PRIMARY KEY  (`sub_cat_id`)
) TYPE=MyISAM COMMENT='sub categories' AUTO_INCREMENT=21;

--
-- Dumping data for table `tsav_sub_categories`
--

INSERT INTO `tsav_sub_categories` VALUES (1, 'CD Player', 1);
INSERT INTO `tsav_sub_categories` VALUES (2, 'Headphones & Media', 1);
INSERT INTO `tsav_sub_categories` VALUES (3, 'Furniture', 1);
INSERT INTO `tsav_sub_categories` VALUES (4, 'Mains Filtration', 1);
INSERT INTO `tsav_sub_categories` VALUES (5, 'Turntables', 1);
INSERT INTO `tsav_sub_categories` VALUES (6, 'Subwoofers', 2);
INSERT INTO `tsav_sub_categories` VALUES (7, 'Styli', 0);
INSERT INTO `tsav_sub_categories` VALUES (8, 'Amplification', 0);
INSERT INTO `tsav_sub_categories` VALUES (10, 'Cassette Decks', 0);
INSERT INTO `tsav_sub_categories` VALUES (11, 'DVD Players', 2);
INSERT INTO `tsav_sub_categories` VALUES (12, 'Systems', 3);
INSERT INTO `tsav_sub_categories` VALUES (13, 'Processing & Amplification', 3);
INSERT INTO `tsav_sub_categories` VALUES (14, 'Speakers', 3);
INSERT INTO `tsav_sub_categories` VALUES (15, 'Cables', 3);
[/code]

[Wildbug]

(Note: In your example code, the example products' manufacturer's codes in the tsav_products table don't match up with what they're supposed to be.)

It looks like all you need is a DISTINCT.

[code]SELECT DISTINCT tsav_manufacturers.manufacturer_name
FROM
    tsav_manufacturers,
    tsav_products
WHERE
    tsav_products.product_manufacturer_id = tsav_manufacturers.manufacturer_id
    AND tsav_products.product_sub_cat_id = 1
[/code]
See the MySQL documentation for more details about DISTINCT: [a href=\"http://dev.mysql.com/doc/refman/5.0/en/select.html\" target=\"_blank\"]MySQL SELECT syntax[/a].

[oldschool]

Thanks 'Wildbug'!

Good way to start your post count! :)