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.
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
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)
---)
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
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.
Reply With Quote
Bookmarks