Home » SQL & PL/SQL » SQL & PL/SQL » UNION SELECT which removes results from the second table if they exist in the first
UNION SELECT which removes results from the second table if they exist in the first [message #304534] Wed, 05 March 2008 10:03 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

As per subject line, I have two select statements retreiving like information from different parts of our system.


what I want to be able to do is create a composite key in both selects (out of 3 fields)
and filter out the results of the second select if the composite key has already been returned in the first.

can anyone tell me if this is possible?

thanks in advance...

Matt
Re: UNION SELECT which removes results from the second table if they exist in the first [message #304535 is a reply to message #304534] Wed, 05 March 2008 10:07 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Can you give us an example of the source data you have and the data you expect to be in the result?

Reason I ask, is that I was about to answer you "look up UNION in the SQL reference". However, there is a functional question to be answered first: if you determine if two rows are the same, only based on 3 fields, what if the rest of the data is different? What do you want to show then?
Re: UNION SELECT which removes results from the second table if they exist in the first [message #304539 is a reply to message #304534] Wed, 05 March 2008 10:15 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi,
an example of data in the first table


KEY, PROJECT, LOCATION, PHASE, MANAGER, PRODUCT, TASK, ETC

D01800-AUKDEV, D01800-A, UK, DEV, John Smith, Product1, Coding, 5
D01800-AUKDEV, D01800-A, UK, DEV, John Smith, Product1, Design, 20
D01800-AUKDEV, D01800-A, UK, DEV, John Smith, Product1, Testing, 15
D01550-AUKDEV, D01550-A, UK, DEV, Dave Jones, Product1, Coding, 8
D01550-AUKDEV, D01550-A, UK, DEV, Dave Jones, Product1, Development, 9
D02100-AUKDEV, D02100-A, UK, DEV, Fred Bloggs, Product1, Coding, 11



second Table

KEY, PROJECT, LOCATION, PHASE, MANAGER, PRODUCT, TASK, ETC

D01800-AUKDEV, D01800-A, UK, DEV, John Smith, Product1, Coding, 5
D01800-AUKDEV, D01800-A, UK, DEV, John Smith, Product1, Design, 20

D06660-AUKDEV, D06660-A, UK, DEV, Sam White, Product1, Coding, 7
D07770-AUKDEV, D07770-A, UK, DEV, Pete Briggs, Product1, Development, 8
D04300-AUKDEV, D04300-A, UK, DEV, Rich Gull, Product1, Coding, 9


The results I want are:
KEY, PROJECT, LOCATION, PHASE, MANAGER, PRODUCT, TASK, ETC

D01800-AUKDEV, D01800-A, UK, DEV, John Smith, Product1, Coding, 5
D01800-AUKDEV, D01800-A, UK, DEV, John Smith, Product1, Design, 20
D01800-AUKDEV, D01800-A, UK, DEV, John Smith, Product1, Testing, 15
D01550-AUKDEV, D01550-A, UK, DEV, Dave Jones, Product1, Coding, 8
D01550-AUKDEV, D01550-A, UK, DEV, Dave Jones, Product1, Development, 9
D02100-AUKDEV, D02100-A, UK, DEV, Fred Bloggs, Product1, Coding, 11
D06660-AUKDEV, D06660-A, UK, DEV, Sam White, Product1, Coding, 7
D07770-AUKDEV, D07770-A, UK, DEV, Pete Briggs, Product1, Development, 8
D04300-AUKDEV, D04300-A, UK, DEV, Rich Gull, Product1, Coding, 9



Notice, the D01800-A rows from the second table have been filtered out as the composite key already exists in the first table. This key is not unique to each row.

Hope that makes sense.

Matt




Re: UNION SELECT which removes results from the second table if they exist in the first [message #304541 is a reply to message #304539] Wed, 05 March 2008 10:20 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Which fields make up this composite key?
Re: UNION SELECT which removes results from the second table if they exist in the first [message #304543 is a reply to message #304534] Wed, 05 March 2008 10:23 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

PROJECT, LOCATION and PHASE.

Re: UNION SELECT which removes results from the second table if they exist in the first [message #304545 is a reply to message #304543] Wed, 05 March 2008 10:30 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Quote:
PROJECT, LOCATION and PHASE.

is the composite key.
I probably misunderstand something, but how can these two records exist then:
KEY, PROJECT, LOCATION, PHASE, MANAGER, PRODUCT, TASK, ETC

D01800-AUKDEV, D01800-A, UK, DEV, John Smith, Product1, Coding, 5
D01800-AUKDEV, D01800-A, UK, DEV, John Smith, Product1, Design, 20


Since they both have D01800-A, UK, DEV in common. So, before comparing these records to table1, I would like to know if (and how) to determine to keep those two records or not.

More general: maybe I'm the only one not understanding this (hey, it's late in the afternoon here at my side of the planet), but I think you should try to explain more clearly what the logic is, like: fields x, y and z of record N are equal to fields x, y and z of record M so only show record N.
Re: UNION SELECT which removes results from the second table if they exist in the first [message #304549 is a reply to message #304534] Wed, 05 March 2008 10:35 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

its more like,

if a distinct key in table 1 matches a distinct key in table 2, do not return it from table two.

so it is basically comparing a distinct list of composite keys in each table, and saying 'where [compositekey] not in (select distinct composite key from....)'

where the nested query would have to be the same as the first table.
In fact, I may have just answered myself, although it seems a little inefficient.


unfortunately I am only trying ot find the existence of a project in the first table, rather than identifyin the existence of a task within the project. each row represents a task in a project, and the two tables will have different tasks in them.
Re: UNION SELECT which removes results from the second table if they exist in the first [message #304577 is a reply to message #304534] Wed, 05 March 2008 13:04 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member



select KEY, PROJECT,location,phase ,manager,  task,etc from
(
select KEY, PROJECT,location,phase ,manager,  task,etc,
 case when count(distinct tbl ) over
  (partition by PROJECT,location,phase) =2 and tbl=2 then null
  else tbl
  end  as rn
   from (
select KEY, PROJECT,location,phase, task,manager,etc,1 tbl  from foo1
union all
select KEY, PROJECT,location,phase, task,manager,etc ,2 from foo2
)
) where rn is not null
Re: UNION SELECT which removes results from the second table if they exist in the first [message #304751 is a reply to message #304534] Thu, 06 March 2008 08:38 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

PERFECT.

thank you so much.

works like a charm.
Re: UNION SELECT which removes results from the second table if they exist in the first [message #304838 is a reply to message #304751] Thu, 06 March 2008 19:50 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Depending on the data volumes, you might find that the analytic function does not scale very well because it performs a sort.

Another possibility (that may scale better) is:

WITH f1 AS (
    select KEY, PROJECT,location,phase, task,manager,etc
    from   foo1
)
SELECT *
FROM   f1
UNION ALL
SELECT KEY, PROJECT,location,phase, task,manager,etc
FROM   f2
WHERE  (
         nvl(key, chr(1))
        ,nvl(project, chr(1))
        ,nvl(location, chr(1))
       ) NOT IN (
         SELECT nvl(key, chr(1)), nvl(project, chr(1)), nvl(location, chr(1)) 
         FROM f1
       )


Ross Leishman
Previous Topic: How to use TO_CHAR to get week day and filter by weekend days
Next Topic: generate DDL
Goto Forum:
  


Current Time: Sat Dec 03 18:24:27 CST 2016

Total time taken to generate the page: 0.05806 seconds