Posted by MC on 02/17/07 16:19

So ,basically, you want to have all values from the table a and matching
values (if they exist) from table b? How does this work for you:

select
Table1.val,
5 as CriteriaValue, ---probably variable...
OtherTable.id,
OtherTable.Text
from
Table1
left join OtherTable on table1.val = OtherTable.a and OtherTable.b = 5


Now, if this query works, you can delete all values from OtherTable that
have a value b = 5 and id not in the select. You can insert the rows
returned by query if you add filter WHERE otherTable.ID is null.
If this doesnt work for you, please elaborate...





MC


"Rick" <rickcool22@hotmail.com> wrote in message
news:45d71f80$0$90276$14726298@news.sunsite.dk...
> Hi all...
>
> I would like to know how it is possible to make my problem below all in
> ONE query or stored procedure.
>
> I select some rows from a table where the resultset is one column with
> some values. Lets say 1, 4 and 7:
>
> row val
> 1 1
> 2 4
> 3 7
>
> Then I would like these results to manipulate another table together with
> another value (lets say some 'b' with value 5)
>
> Lets say the other table looks like this:
>
> id a b text
> 1 1 1 'Some text'
> 2 1 3 'Some text'
> 3 1 5 'Some text'
> 4 4 5 'Some text'
> 5 7 4 'Some text'
> 6 2 5 'Some text'
>
> in the above example the rows with id 3 and 4 match my criteria because 1
> and 4 (and not 7) was in the column 'a' together with the value 5 in
> column 'b'.
>
> Here comes the tricky part (at least for me):
> Now because a row without the 'a' value 7 and the 'b' value 5 existed in
> the table I would like to create one row with those values.
> Also because the 'b' column did have a the value 5 (the row with id 6)
> without any of the 'a' values of 1, 4 and 7 (here 'a' is 2), that row
> shall be deleted.
>
> Then at last I would like the resultset matching 'a' column of 1, 4 and 7
> AND 'b' column 5 as a resultset.
>
> I hope that it is understandable and someone can help.
>
> - rick -
>

[Back to original message]