0

I'm having trouble understanding how to write a specific MySQL query to perform a relational division/ intersect. I have looked at the following links/ SO questions, but I'm still confused :

MySQL equivalent of IN() clause doing Logical AND ; Relational Algebra : Division ; Divided we Stand ; Advanced SQL : Intersect ; Doing INTERSECT and MINUS in MySQL and Alternative to INTERSECT in MySQL

I attempted to adapt the SQL query shown in "Alternative to INTERSECT in MySQL", but got lost in the implementation.

Pseudocode :

Want : package.* from package WHERE package.pkid equalto package_products.pkid AND package_products.pid is_all_of (
    WANT : option_products.pid FROM option_products 
    WHERE option_products.oid = [optionID]
), // returning packages that have all of the product IDs (pids) and not just one of them.
[OptionID] = $option

MySQL (inside db_query() :

"SELECT package.* FROM package WHERE package.pkid = package_products.pkid && package_products.pid is_allof(".
    // What do I use in place of 'is_all_of'? How do I do an intersection/ relational division with INNER JOIN and/ or USING(pid)?
"    SELECT options_products.pid FROM options_products WHERE options_products.oid = :option
)", array(":option" => $option )
Community
  • 1
  • 1
Agi Hammerthief
  • 2,114
  • 1
  • 22
  • 38

1 Answers1

0

Maybe I am missing something here, but I'd say that 

db_query("SELECT package.* FROM package 
WHERE package.pkid = package_products.pkid 
&& package_products.pid IN 
(SELECT options_products.pid FROM options_products 
WHERE options_products.oid = %d)", $option));

should work for you.

I have rewritten your syntax to meet Drupal 6 db_query requirements (http://api.drupal.org/api/drupal/includes!database.mysql-common.inc/function/db_query/6).

pamatt
  • 892
  • 10
  • 13