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:
FROM table1 u,
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.
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:
---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:
from table1 u
from table2 r
from table5 g
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.
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!
Click Here to Expand Forum to Full Width