Joins/dedupes

• Jay Bee-249994

  SSCrazy

  Points: 2393

  More actions

  January 4, 2007 at 10:52 am

  #193568

  Hi all,

  I've got a series of 5 tables, each has 8 columns entitled Application 1, Application 2....Application 8.  Each column has plenty of dupes, and blanks.

  I need to merge these 40 columns into one column on one table, but with no dupes, and no blanks, THEN, I have to delete all entries with matching values in column 'apps' in table 'Filter'.

  Any idea how the SQL should look?  I imagine a "select distinct" and a join or two will be involved, but I'm an Admin guy...dev was never my strong point!!

  Cheers all,

   

  Jaybee.

• Jo Pattyn

  SSC-Dedicated

  Points: 32348

  More actions

  January 4, 2007 at 11:15 am

  #681278

  Part 1: inserting in a new table

  Insert into newtable

  (Application)

  select ApplicationX from tableY

  where ApplicationX is not null /*blank checking

  and application1''

  and not exists

  (select * from newtable where application=ApplicationX ) /*dupe checking*/

  repeat for all table (x from 1-8..., y 1-5...)

  part 2:

  delete from filter

  from filter

  inner join newtable

  on filer.apps=newtable.application

  be sure to test it first

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  January 5, 2007 at 3:36 am

  #681424

  Another take on part1:

  insert newTable (application)

  select application1 from tab1 union

  select application2 from tab1 union

  .....

  select application8 from tab1

  Repeat for through tab5, or if there may be dupes in tabs 1-5, union all 40 columns.

  Also may need to add filter on what you consider as 'blank'

  Can't say much about the delete part, need to see a bit more on what those tables look like first.

  /Kenneth