Here's the scenario:
Two tables, Tab1 and Tab2. A One-to-many relationship exists between the tables, with the 1 being in Tab1 and the many being in Tab2. Now, suppose I want a record from Tab1 where a particular value in the many is not associated with that value from Tab1. To visualize, the tables could look like this
Tab1
ID Name
1 Jim
2 Chris
Tab2
ID T1ID Num
1 1 10
2 1 11
3 2 10
The relationship is formed from Tab1.ID and Tab2.T1ID.
So, suppose I want all records that do NOT have the value of 11 associated with them. One with think this could be done with a simple query:
select t1.* from Tab1 as t1, Tab2 as t2 where ti.id = t2.T1ID and t2.Num != 11
However, this does not work, since the != check passes in the case that Num = 10, so therefore I get returned 1 and 2 from Tab1.
I would be so greatful if anyone could solve this problem for me (the question being how can I test to see if != applies once and reject all other associations, even when the != fails) because I've been dealing with this case in PHP and I know I'm hurting speed and efficiency by bringing all records across and then filtering.
I've explained the situation as well as I can, but if anything needs cleared up I'd be more than happy to go into more detail.
Thanks ! ! ! !
Two tables, Tab1 and Tab2. A One-to-many relationship exists between the tables, with the 1 being in Tab1 and the many being in Tab2. Now, suppose I want a record from Tab1 where a particular value in the many is not associated with that value from Tab1. To visualize, the tables could look like this
Tab1
ID Name
1 Jim
2 Chris
Tab2
ID T1ID Num
1 1 10
2 1 11
3 2 10
The relationship is formed from Tab1.ID and Tab2.T1ID.
So, suppose I want all records that do NOT have the value of 11 associated with them. One with think this could be done with a simple query:
select t1.* from Tab1 as t1, Tab2 as t2 where ti.id = t2.T1ID and t2.Num != 11
However, this does not work, since the != check passes in the case that Num = 10, so therefore I get returned 1 and 2 from Tab1.
I would be so greatful if anyone could solve this problem for me (the question being how can I test to see if != applies once and reject all other associations, even when the != fails) because I've been dealing with this case in PHP and I know I'm hurting speed and efficiency by bringing all records across and then filtering.
I've explained the situation as well as I can, but if anything needs cleared up I'd be more than happy to go into more detail.
Thanks ! ! ! !