We help IT Professionals succeed at work.

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

l00ny_tn1
l00ny_tn1 asked
on
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

Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

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

Author

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.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

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

Author

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

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
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

Author

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

Author

Commented:
This one works, thanks!!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.