need a query without using union
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: need a query without using union

  1. #1
    Join Date
    Jun 2005
    Posts
    20

    need a query without using union

    object :
    To get the details from user who are not having any contact.


    1.user table
    -------------
    owner -> uid
    owner have more users.

    the contact may be on two ways

    2. Interaction table
    ---------------------
    Rel columns => interaction_uid = owner.uid

    3. opportunity (o) -> interaction (i), user (u) table
    ------------------------------------------------------

    Rel.columns => 1.i.interaction_uid = u.owner_uid and
    2. i.parent_oid = o.uid
    3. value < 5 (where condi)

    relationship is like this.

    select user_name,uid from user
    where uid not in
    (select interaction_uid
    from interaction i, opportunity o
    where i.parent_uid = o.uid
    and o.value < 5
    union
    select interaction_uid from user u,interaction i
    where u.uid = i.interaction_uid )
    and o_uid = '&owner_id'



    i have written the query using union clause.
    but developer want to written in sql without using union .ie. using joins

    tks in advance
    Last edited by balasmg; 06-28-2005 at 10:32 AM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Did said developer express any rational reason for the requirement to avoid UNION? I ask just in case there's anything else that he'd prefer us not to use: any particular letter of the alphabet, or whatever?

    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jun 2005
    Posts
    20
    hi,
    dev.. want to write the same query using joins

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How about if you outer-joined the user table to the two other tables, and checked for rows that match in neither one? You might find it more efficient to outer-join to an in-line view, and you might also require a distinct on the final result set -- hopefully that won't contravene your developers religious convictions
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Quote Originally Posted by slimdave
    Did said developer express any rational reason for the requirement to avoid UNION? I ask just in case there's anything else that he'd prefer us not to use: any particular letter of the alphabet, or whatever?


    I prefer writing all of my select statements without using the letter "E"! I'm sure I get better performance that way as "E" is a very inEfficient letter!
    this space intentionally left blank

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Quote Originally Posted by balasmg
    object :
    i have written the query using union clause.
    but developer want to written in sql without using union .ie. using joins
    You need to explain to him that a union is not the same as an inner join. In an inner join each row returned can be from any of the tables being joined. When you do a union, each row returned is from one table or the other, but not both. However given the way you wrote the query, you can also express it with two not in clauses, or better yet not exists clauses one for each table.
    this space intentionally left blank

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    I think you can do it if you create a new database, setup a dblink, and then insert into a new table one of the original tables plus the result from a minus (make sure you go the right way on that) of the two original tables. And then to be on the safe side of things, make sure the new database is on a different platform and using a different version. This should completely avoid that nasty union relational operator thingy.

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