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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.