mySQL experts - need help with 'intersect'

Posted by MTCreations on Stack Overflow See other posts from Stack Overflow or by MTCreations
Published on 2010-04-05T20:46:28Z Indexed on 2010/04/05 21:03 UTC
Read the original article Hit count: 391

Filed under:

mysql

|

intersect

|

union

|

select

I know that mySQL 5.x does not support INTERSECT, but that seems to be what I need.

Table A: Products (p_id)

Table B: Prod_cats (cat_id) - category info (name, description, etc)

Table C: prod_2cats (p_id, cat_id) - many to many

prod_2cats holds the many (1 or more) categories that have been assigned to Products (A).

Doing a query/filter lookup, (user interactive) and need to be able to select across multiple categories the products that meet ALL the criteria.

Ex: - 80 products assigned to Category X - 50 products assigned to Category Y - but only 10 products (intersect) are assigned to BOTH cat X AND cat Y

This sql works for one category:

SELECT * FROM products WHERE p_show='Y' AND p_id IN ( SELECT p_id FROM prods_2cats AS PC WHERE PC.cat_id =" . $cat_id ."

<-$cat_id is sanitized var passed from query form .

I can't seem to find the means to say ' give me the intersect of cat A and cat B' and get back the subset (10 records, from my example)

Help!

© Stack Overflow or respective owner

Related posts about mysql

How to remove MySQL completely with config and library files on ubuntu 12.04 gnome 3.0

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I tried everything till now: sudo apt-get remove mysql-server mysql-client mysql-common sudo apt-get purge mysql-server mysql-client mysql-common sudo apt-get autoremove and even more commands... But whenever I am trying to locate mysql. I get a no. of files related to mysql command: shell>>… >>> More
mysql: Cannot load from mysql.proc. The table is probably corrupted

as seen on Server Fault - Search for 'Server Fault'
Mysql was started: /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 but when I'm trying to do something: ERROR 1548 (HY000) at line 1: Cannot… >>> More
Why is there a /etc/init.d/mysql file on this Slackware machine? How could it have gotten there?

as seen on Server Fault - Search for 'Server Fault'
A client of my IT-consulting service owns a web-development shop. He's been having problems with a Slackware 12.0 server running MySQL 5.0.67. The machine was set up by the client's sysadmin, who left on bad terms. My client no longer employs a sysadmin. As far as I can tell, the only copy of MySQL… >>> More
mysql: Bind on unix socket: Permission denied

as seen on Server Fault - Search for 'Server Fault'
Can't start mysql with: sudo /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 120222 13:40:48 mysqld_safe Starting mysqld daemon with databases… >>> More
MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

as seen on SQL Authority - Search for 'SQL Authority'
MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which… >>> More

Related posts about intersect

SQLite INTERSECT gives a huge performance decrease

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a query that runs in less than 1 ms: SELECT product_to_value.category AS category, features.name AS featurename, featurevalues.name AS valuename FROM product_to_value, features, featurevalues WHERE product_to_value.category IN(:int, :bla, :bla1) … >>> More
mySQL experts - need help with 'intersect'

as seen on Stack Overflow - Search for 'Stack Overflow'
I know that mySQL 5.x does not support INTERSECT, but that seems to be what I need. Table A: Products (p_id) Table B: Prod_cats (cat_id) - category info (name, description, etc) Table C: prod_2cats (p_id, cat_id) - many to many prod_2cats holds the many (1 or more) categories that have been assigned… >>> More
Finding rows that intersect with a date period

as seen on SQL Blogcasts - Search for 'SQL Blogcasts'
This one is mainly a personal reminder but I hope it helps somebody else too. Let's say you have a table that covers something like currency exchange rates with columns for the start and end dates of the period each rate was applicable. Now you need to list the rates that applied during the year… >>> More
In SQL, if we rename INNER JOIN as INTERSECT JOIN, LEFT OUTER JOIN as LEFT UNION JOIN, and FULL OUTE

as seen on Stack Overflow - Search for 'Stack Overflow'
In SQL, the name Join gives an idea of "merging" or a sense of "union", making something bigger. But in fact, as in the other post http://stackoverflow.com/questions/2706051/in-sql-a-join-is-actually-an-intersection-and-it-is-also-a-linkage-or-a-sidew it turns out that a Join (Inner Join) is actually… >>> More
Intersect a collection of collections in LINQ

as seen on Stack Overflow - Search for 'Stack Overflow'
I've got a list of lists which I want to intersect: List<List<int>> input = new List<List<int>>(); input.Add(new List<int>() { 1, 2, 4, 5, 8 }); input.Add(new List<int>() { 3, 4, 5 }); input.Add(new List<int>() { 1, 4, 5, 6 }); Output should be { 4, 5… >>> More