Query problem

Hi guys,

I don't understand why my query is hanging. I have five tables with about 100 rows in each. All I'm trying to do is determine if a specific user exsits in any of the tables provided:

SELECT u.UserID
FROM table1 u,
table2 r,
table3 s,
table4 t,
table5 g
where r.userid = 1
or s.userid = 1
or t.userid = 1
or g.userid = 1

When I use three paramaters on the WHERE clause, it works fine. But when I use more than three, it just hangs.

Any help would be greatly appreciated.

Thanks,
- Gary

That's because you have it structured wrong. When you have tables in the FROM clause, you must join them in the WHERE clause or suffer cartesian joins. Do the math :)

1 Table: 100 rows
2 Tables: 100 * 100 = 10,000 rows
3 Tables: 100 * 100 * 100 = 1,000,000 rows
4 Tables: 100 * 100 * 100 * 100 = 100,000,000 rows

You problem, as stated, actually has no inteest in joining these tables. What you appear to be trying to do can be accomplished through:

SELECT
---u.UserID
FROM
---table1 u
where
---u.user_id = 1---AND
---(
------exists ( select 1 from table2 r where r.user_id = u.user_id) OR
------exists ( select 1 from table3 s where s.user_id = u.user_id) OR
------exists ( select 1 from table4 t where t.user_id = u.user_id) OR
------exists ( select 1 from table5 g where g.user_id = u.user_id)
---)


HTH,

- Chris

What Gary asked for is if the user exists in *any* of the tables. From looking at Chris' solution, what this will do is report the username if it appears in table 1 *and* one of the others.

I think that a union will satisfy the basic requirement:

select u.userid
from table1 u
where u.userid=1
union
select r.userid
from table2 r
where r.userid=1
.
.
.
union
select g.userid
from table5 g
where g.userid=1

HTH

David.

True, but I made some assumptions to reach that conclusion. He 'said' he wanted the result if it existed in *any* table, yet the way he wrote the statement, it looked as if he assumed that it existed first in table1. If my assumption was incorrect, then Dave_A has the correct solution.

- Chris

Hi guys,

chrislong was right. I was looking for a value that existed in ANY of the tables. The EXIST operator worked like a charm!

Thanks for your help guys!

- Gary