Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-05-31 : 07:52:55
|
Jim writes "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
<pre>[size=2]ID Name
1 Jim
2 Chris</font></pre>
Tab2
<pre>[size=2]ID T1ID Num
1 1 10
2 1 11
3 2 10</font></pre>
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 you 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.
This will be done on Windows and SQL Server 2000." |
|
|
|
|
|
Not getting a One in the One to Many