A trick with SQL

Hi people - need help with a little thing on SQL...

I have a function that returns a table of values where one row is called 'UserID' - lets call this table 'x'

I have a another table with a row called 'UserID' in it - lets call this table 'y'

I want an SQL statement that achieves:

Select everything from table x (the table that was generated by the function) where the UserID is not in any row of table y.

Anyone think they can help?

Regards,

Will

SELECT * FROM table_x WHERE UserID NOT IN (SELECT UserID FROM table_y)

or

SELECT * FROM table_x WHERE UserID NOT EXISTS (SELECT 1 FROM table_y WHERE table_y.UserID=table_x.UserID)

|||

This example:

 SELECT t1.* FROM t1 WHERENOT EXISTS (SELECT *FROM t2WHERE t1.id = t2.t1id)

Came from this page:http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

|||

Thanks Guys

A trick query.

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 -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.comwrote in message
news:45d71f80$0$90276$14726298@.news.sunsite.dk...

Quote:

Originally Posted by

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 -
>