asked on

How to query multiple tables distinctly on one column? Multiple inner joins needed?

I would like to query 4 tables for a searchphrase. Two of the tables describe Departments and Subdepartments and are "linked" through primary key, the other two tables are for keyword-association (that means, I have a keywordlist in "Keywords" and a table for "linking" the keywords to the Departments). How can I have a result showing distinct list of Ids and Names of Departments? See below for an example (that doesn't quite work like expected). I need this to be compatible to mysql 4.1.

SELECT DISTINCTROW d.Id, d.Name
                  FROM Departments d, Subdepartments s, Keywordassocs ka, Keywords k
                  WHERE ((s.Name LIKE "%%%searchphrase%%%" AND  s.Searchable = 1) 
                  OR (d.Name LIKE "%%%searchphrase%%%" AND d.Searchable = 1)
                  OR (k.Text LIKE "%%%searchphrase%%%"))
                  AND d.Id = s.Departmentid AND ka.Keywordid = k.Id
                  ORDER BY d.Name;
 
 
+-------------+
+ Departments +
+-------------+
 
+----------------+
+ Subdepartments +
+----------------+
 
+----------+
+ Keywords +
+----------+
 
+---------------+
+ Keywordassocs +
+---------------+

Open in new window

Guy Hengel [angelIII / a3]

with MySQL, use GROUP BY:

SELECT d.Id, d.Name
                  FROM Departments d, Subdepartments s, Keywordassocs ka, Keywords k
                  WHERE ((s.Name LIKE "%%%searchphrase%%%" AND  s.Searchable = 1) 
                  OR (d.Name LIKE "%%%searchphrase%%%" AND d.Searchable = 1)
                  OR (k.Text LIKE "%%%searchphrase%%%"))
                  AND d.Id = s.Departmentid AND ka.Keywordid = k.Id
GROUP BY d.Id, d.Name 
                  ORDER BY d.Name;

Open in new window

l00ny_tn1

ASKER

@angelIII:

>>> with MySQL, use GROUP BY

This returns all entries of "Departments" and not only the ones that have the searchphrase within Name or have Subdepartments with searchphrase within their Name or have a keyword associated with.

Guy Hengel [angelIII / a3]

you are indeed missing a join condition between Keywordassocs and the other table(s)...
I reorganized your query a bit to make it clear (and possibly enhance the performance of the query):

SELECT d.Id, d.Name
FROM Departments d
JOIN Subdepartments s
  ON d.Id = s.Departmentid 
JOIN Keywordassocs ka
  ON <you are missing the join between ka and either of the above tables...>
JOIN Keywords k
 AND ka.Keywordid = k.Id
WHERE ( d.Name LIKE "%%%searchphrase%%%" AND d.Searchable = 1 )
   OR ( s.Name LIKE "%%%searchphrase%%%"  AND s.Searchable = 1)
   OR ( k.Text LIKE "%%%searchphrase%%%" )
GROUP BY d.Id, d.Name 
  ORDER BY d.Name;

Open in new window

l00ny_tn1

ASKER

>>> I reorganized your query a bit to make it clear (and possibly enhance the performance of the query)

This returns the departments associated with keywords only and not the departments which have the searchphrase e.g. in their Name :)

SELECT d.Id, d.Name
FROM Departments d
JOIN Subdepartments s
  ON d.Id = s.Departmentid 
JOIN Keywordassocs ka
  ON ka.Departmentid = d.Id
JOIN Keywords k
 ON ka.Keywordid = k.Id
WHERE ( d.Name LIKE "%%%searchphrase%%%" AND d.Searchable = 1 )
   OR ( s.Name LIKE "%%%searchphrase%%%"  AND s.Searchable = 1)
   OR ( k.Text LIKE "%%%searchphrase%%%" )
GROUP BY d.Id, d.Name 
  ORDER BY d.Name;

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

l00ny_tn1

ASKER

So I have made a testcase... perhaps this helps to solve the problem...
The result should be more than one department...

CREATE database departments;
USE departments;
CREATE TABLE IF NOT EXISTS `Departments` (
  `Id` int(11) NOT NULL auto_increment,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
 
INSERT INTO `Departments` (`Id`, `name`) VALUES 
(1, 'Spirits'),
(2, 'Winedepart'),
(3, 'Accounting'),
(4, 'Marketing');
 
CREATE TABLE IF NOT EXISTS `Keywordassocs` (
  `Id` int(11) NOT NULL auto_increment,
  `Keywordid` int(11) NOT NULL,
  `Departmentid` int(11) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
 
INSERT INTO `Keywordassocs` (`Id`, `Keywordid`, `Departmentid`) VALUES 
(1, 1, 1),
(2, 2, 1),
(3, 3, 2),
(4, 4, 1),
(5, 3, 3),
(6, 3, 4);
 
CREATE TABLE IF NOT EXISTS `Keywords` (
  `Id` int(11) NOT NULL auto_increment,
  `Text` varchar(255) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
 
INSERT INTO `Keywords` (`Id`, `Text`) VALUES 
(1, 'Whiskey'),
(2, 'Scotch'),
(3, 'Wine'),
(4, 'Wodka');
 
CREATE TABLE IF NOT EXISTS `Subdepartments` (
  `Id` int(11) NOT NULL auto_increment,
  `name` varchar(10) NOT NULL,
  `Departmentid` int(11) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
 
INSERT INTO `Subdepartments` (`Id`, `name`, `Departmentid`) VALUES 
(1, 'Wodkadepar', 1),
(2, 'Scotchdepa', 1),
(3, 'Merlot', 2),
(4, 'Cabernet', 2);
 
SELECT d.Id, d.Name
FROM Departments d
JOIN Subdepartments s
  ON d.Id = s.Departmentid 
JOIN Keywordassocs ka
  ON ka.Departmentid = d.Id
JOIN Keywords k
 ON ka.Keywordid = k.Id
WHERE ( d.Name LIKE "%%%wine%%%")
   OR ( s.Name LIKE "%%%wine%%%")
   OR ( k.Text LIKE "%%%wine%%%" )
GROUP BY d.Id, d.Name 
  ORDER BY d.Name;

Open in new window

l00ny_tn1

ASKER

This one works, thanks!!