Join Problem?

andris2000 · #1 (**permalink**) 02-21-04, 09:11

Hello all
I have following two tables:

table_X
+-------+
| a_id | b_id |
+-------+
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 3 | 3 |
| 4 | 1 |
| 4 | 2 |
| 4 | 3 |

table_Z
+-----------------+
| b_id | system |
+----------------+
| 1 | 0 |
| 2 | 1 |
| 3 | 0 |
| 4 | 1 |

Now I need to have the result which would list all table_X a_id's that contain table+Z b_id's that have system flag=1.

How can I achieve this ?

reneeb · #2 (**permalink**) 02-23-04, 02:51

Is b_id in table_X the foreign key??

Code:

SELECT a_id FROM table_X INNER JOIN table_Z ON table_X.b_id = table_Z.b_id WHERE system = 1;

andris2000 · #3 (**permalink**) 02-25-04, 11:55

Thanks, that's ok but now I need to have the result which would list all table_X a_id's that contain table+Z b_id's with all system flag=0.
I mean all b_id's should be not system. If even one of the b_id's for specific a_id is system, record is not returned.

Is it possible with mysql?

Internet Services

Web Development

Digital Marketing

Consumer Tech