Query problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Query problem

  1. #1
    Join Date
    Mar 2001
    Posts
    26
    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

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  3. #3
    Join Date
    Feb 2001
    Posts
    123
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  5. #5
    Join Date
    Mar 2001
    Posts
    26
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width