INTERSECT workaround?

[cchapman]

I'm having issues with a query that I believe would normally use INTERSECT.

 

First the details...

 

MySQL Server 5.0.91

 

CREATE TABLE IF NOT EXISTS `songs_x_keywords` (
  `id` int(11) NOT NULL auto_increment,
  `keywords_id` int(11) NOT NULL,
  `songs_id` int(11) NOT NULL,
  `rank` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `keywords_id` (`keywords_id`),
  KEY `songs_id` (`songs_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

INSERT INTO `songs_x_keywords` (`id`, `keywords_id`, `songs_id`, `rank`) VALUES
(1, 3, 1, 4),
(2, 3, 2, 3),
(3, 3, 3, 3),
(4, 5, 1, 2),
(5, 5, 2, 1),
(6, 7, 1, 1),
(7, 7, 2, 2),
(8, 7, 3, 2);

$keywords_id = array(3,5,7);

 

In our script, we will receive the keywords_id in the form of an array (of unknown count). We are looking for all instances of *common* songs_id for the provided set of keywords_id.

 

Normally I believe we would use INTERSECT to find all instances of unique songs_id based upon the keywords_id, something like:

 

SELECT songs_id FROM songs_x_keywords WHERE keywords_id = '3'
INTERSECT
SELECT songs_id FROM songs_x_keywords WHERE keywords_id = '5'
INTERSECT
SELECT songs_id FROM songs_x_keywords WHERE keywords_id = '7';

 

which should yield songs_id = 1 & songs_id = 2

 

But MySQL does not understand INTERSECT.  I've read that you can accomplish this with inner joins but how would you inner join on yourself exactly?

[Adam]

Don't you mean it would return `songs_id` 1, 2 & 3? .. Since `id` 3 & 8 have `songs_id` 3?

[cchapman]

errr right. Sorry I didn't review my sample data closely. But rest assured there are thousands of entries that do not match. ;-)

[mikosiko]

now I don't follow...

 

then you are not talking about INTERSECT ... because song_id # 3 is not in the keywords_id group #5... or I understood you incorrectly?...

 

[cchapman]

[Dang - sorry I confused myself there.]  I was correct the first time, the select should result in songs_id 1 and 2, as they are the only songs_id's that appear for *all three* keywords (3,5,7).  That is an Intersect, is it not?  If so, how would you accomplish this in MySQL?

 

 

[mikosiko]

so I was right... and you are not looking for an INTERSECT... you are after a RELATIONAL DIVISION...

 

couple links where you can read about:

http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

http://www.tc.umn.edu/~hause011/code/SQLexample.txt

 

here is an example that should solve your query:

SELECT DISTINCT a.songs_id AS sngid
FROM songs_x_keywords AS a
WHERE NOT EXISTS
   (SELECT b.keywords_id FROM vkeywordsid AS b
      WHERE b.keywords_id NOT IN (SELECT c.keywords_id FROM songs_x_keywords AS c WHERE c.songs_id = a.songs_id));

 

the trick.... to do this you must create a table  (vkeywordsid) with the values that you get in your array of keywords_id... try it...

 

I did test the code with your example data at it works... it return songs_id's 1 & 2.

 

[cchapman]

Hmmm I'm going to read up on relational division now. Thanks a bunch for the links. In the meantime I have been digging on an inner join that seems to accomplish the same result:

 

SELECT a.songs_id FROM songs_x_keywords as a 
INNER JOIN songs_x_keywords as b ON b.songs_id = a.songs_id 
INNER JOIN songs_x_keywords as c ON c.songs_id = a.songs_id
WHERE a.keywords_id = '3' AND b.keywords_id = '5' AND c.keywords_id = '7'

 

[mikosiko]

...yes that could work too...... I'm not sure...

 

how your select solve this:  (if your first sentence was correct)

we will receive the keywords_id in the form of an array (of unknown count)

 

are you going to modify you select every time?....

[cchapman]

Sure - why not?

 

$keyword = array (3, 5, 7);
ksort($keyword);

$i = 0;
$count = count($keyword);
$js = ''; // join statement
$ji = 'a'; // join increment - must be a letter, not a number
$where_clause = '';

while ($i < $count) {
	if($ji == 'a') {
		$where_clause .= " a.keywords_id = '$keyword[$i]' ";
	}
	else {
		$js .= " INNER JOIN songs_x_keywords as $ji ON $ji.songs_id = a.songs_id ";
		$where_clause .= " AND $ji.keywords_id = '$keyword[$i]' ";
	}
	$i++;
	$ji++;
} // end while

$sql = "SELECT a.songs_id FROM songs_x_keywords as a $js WHERE $where_clause";
$sql_result = mysql_query($sql) or die("Could not execute the select query." . mysql_error());
$rows = mysql_num_rows($sql_result);
while ($row = mysql_fetch_array ($sql_result)) {
	echo $row["songs_id"];
	}

 

 

I'm still testing this but it seems to product correct results.

[mikosiko]

that is ok.... if you like it and works for you ....

 

me... being more a SQL type of guy I'll not solve it like that not even in my worst nightmare ... I'm imagining how that select will look if you receive an array with 50 or more elements to test..... 50 or more INNER JOINS and your where clause with 50 or more AND's....       

 

but... as I said... as long as it works for you 

 

glad to try to help.. good luck