Difference between same table groups

[betazold]

For example, we have some table for categories:

CREATE TABLE `p1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

We have some categories:

mysql> select * from p1;
+----+-------+
| id | title |
+----+-------+
|  1 | cat1  |
|  2 | cat2  |
|  3 | cat3  |
|  4 | cat4  |
+----+-------+
4 rows in set (0.00 sec)

Then, we have table for properties:

CREATE TABLE `p2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(20) DEFAULT NULL,
  `cat_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
There are list of properties:
mysql> select * from p2;
+----+--------+--------+
| id | title  | cat_id |
+----+--------+--------+
|  1 | prop11 |      1 |
|  2 | prop12 |      1 |
|  3 | prop21 |      2 |
|  4 | prop22 |      2 |
|  5 | prop23 |      2 |
|  6 | prop31 |      3 |
|  7 | prop32 |      3 |
|  8 | prop33 |      3 |
|  9 | prop41 |      4 |
+----+--------+--------+
9 rows in set (0.00 sec)
Next, we need to have list of properties values, which may be contained in "groups":

CREATE TABLE `p3` (
  `gid` int(11) NOT NULL,
  `prop_id` int(11) NOT NULL,
  `value` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`gid`,`prop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
with the following content:

mysql> select * from p3;
+-----+---------+------------+
| gid | prop_id | value      |
+-----+---------+------------+
|   1 |       1 | prop 1     |
|   1 |       2 | prop 2     |
|   1 |       4 | diff value |
|   1 |       5 | same value |
|   1 |       6 | prop 6     |
|   2 |       1 | prop 1     |
|   2 |       2 | prop 2     |
|   2 |       3 | prop 3     |
|   2 |       4 | prop 4     |
|   2 |       5 | same value |
|   2 |       7 | prop 7     |
+-----+---------+------------+
11 rows in set (0.00 sec)

For example, I want to know, which values of second group are missed or have different value in the first group. And properties in this dataset must be included in categories 1 and 2.

mysql> select *, 
             (select b.prop_id 
              from p3 as b 
              where gid = 1 
                    and a.prop_id = b.prop_id
             ) as is_exists 
       from p3 as a 
       join p2 
       on p2.id = a.prop_id 
       where gid = 2 
             and cat_id in (1, 2) 
             and (prop_id, value) not in 
                 (select prop_id, value 
                  from p3 
                  where gid = 1 
                        and prop_id = a.prop_id
                 );
+-----+---------+--------+----+--------+--------+-----------+
| gid | prop_id | value  | id | title  | cat_id | is_exists |
+-----+---------+--------+----+--------+--------+-----------+
|   2 |       3 | prop 3 |  3 | prop21 |      2 |      NULL |
|   2 |       4 | prop 4 |  4 | prop22 |      2 |         4 |
+-----+---------+--------+----+--------+--------+-----------+
2 rows in set (0.00 sec)

@темы: MySQL