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

l00ny_tn1Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
maybe LEFT JOINs are needed...
SELECT d.Id, d.Name
FROM Departments d
LEFT JOIN Subdepartments s
  ON d.Id = s.Departmentid 
LEFT JOIN Keywordassocs ka
  ON ka.Departmentid = d.Id
LEFT 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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
l00ny_tn1Author Commented:
@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.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
l00ny_tn1Author Commented:
>>> 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

0
 
l00ny_tn1Author Commented:
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

0
 
l00ny_tn1Author Commented:
This one works, thanks!!
0
All Courses

From novice to tech pro — start learning today.