Column condition two times

[Dragu Costel]

Hello ,

I have 2 tables

+----------------+
| Tables_in_demo |
+----------------+
| category |
| node_index |
+----------------+

node_index
+----+-----+--------+-------------+
| id | oid | title | description |
+----+-----+--------+-------------+
| 1 | 1 | node 1 | node 1 |
| 2 | 2 | node 2 | node 2 |
| 3 | 3 | node 3 | node 3 |
| 4 | 4 | node 4 | node 4 |
+----+-----+--------+-------------+

category;
+-----+-----+
| oid | cid |
+-----+-----+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 1 |
| 2 | 1 |
| 3 | 2 |
+-----+-----+

Each cid represent a category that is associated to a node
If you select one category we have the following result:

mysql> SELECT * FROM `node_index` n LEFT JOIN `category` c on n.oid = c.oid WHERE c.cid = 1 GROUP by n.oid;
+----+-----+--------+-------------+------+------+
| id | oid | title | description | oid | cid |
+----+-----+--------+-------------+------+------+
| 1 | 1 | node 1 | node 1 | 1 | 1 |
| 2 | 2 | node 2 | node 2 | 2 | 1 |
+----+-----+--------+-------------+------+------+
2 rows in set (0.00 sec)

Fine .. i select a new category :

mysql> SELECT * FROM `node_index` n LEFT JOIN `category` c on n.oid = c.oid WHERE c.cid = 1 AND c.cid = 2 GROUP by n.oid;

The SQL is wrong but is want I want.

If i try:

mysql> SELECT * FROM `node_index` n LEFT JOIN `category` c on n.oid = c.oid WHERE c.cid IN (1,2) GROUP by n.oid;
+----+-----+--------+-------------+------+------+
| id | oid | title | description | oid | cid |
+----+-----+--------+-------------+------+------+
| 1 | 1 | node 1 | node 1 | 1 | 1 |
| 2 | 2 | node 2 | node 2 | 2 | 1 |
| 3 | 3 | node 3 | node 3 | 3 | 2 |
+----+-----+--------+-------------+------+------+
3 rows in set (0.00 sec)

The result is wrong.

I want to have AND not OR between the values.

I receive the result that I want by doing this:

mysql> SELECT * FROM `node_index` n
-> LEFT JOIN `category` c1 on n.oid = c1.oid
-> LEFT JOIN `category` c2 on n.oid = c2.oid
-> WHERE c1.cid = 1
-> AND c2.cid = 2
-> GROUP BY n.oid;
+----+-----+--------+-------------+------+------+------+------+
| id | oid | title | description | oid | cid | oid | cid |
+----+-----+--------+-------------+------+------+------+------+
| 1 | 1 | node 1 | node 1 | 1 | 1 | 1 | 2 |
+----+-----+--------+-------------+------+------+------+------+

The LEFT JOIN it is expensive. If user select 20 filters the left join will be made on the same table 20 times.
It is another way to do it ?

Thanks,