asked on

Advanced SQL Query (MS-SQL 2008)

I am having problems with a query... unfortunately I need to use fictitious data so here goes.

I need to grab data from 4 different tables. Table A has a primary key called "id". This is a foreign key in the other 3 tables, B,C and D.

There is a 'date' field in Table A. There are 'updated' date fields in the 3 other tables.

There are also 'groupnumber' fields in each of the three tables, B, C and D but not all tables may necessarily have a groupnumber for a given id (well, a.id more specifically)

I need to select all of the "id" from table A that are within a date range and the latest updated 'groupnumbers' along with other fields from the corresponding table. If a groupnumber does not exist in one of the three tables (B, C, and D), it should return NULL for the fields selected in that table.

For example:

Table A (id, date, ...)
1, Mar 8 2011
2, Mar 8 2011
...

Table B (id, table_a_id, updated, groupnumber, fieldx, fieldy, fieldz)
32423, 1, Mar 8 2011 @ 1pm, 55, 1, 12, 13
32424, 1, Mar 8 2011 @ 3pm, 57, 14, 15, 16
32425, 1, Mar 8 2011 @ 2pm, 55, 17, 18, 19
32426, 2, Mar 8 2011 @ 4pm, 55, 20, 21, 21

Table C (id, table_a_id, updated, groupnumber, fielda, fieldb)
43534, 1, Mar 8 2011 @ 12pm, 55, 23, 24
43535, 2, Mar 8 2011 @ 1pm, 55, 26, 27
43536, 2, Mar 8 2011 @ 1pm, 57, 29, 30

Table D (id, table_a_id, updated, groupnumber, fieldm, fieldn)
23345, 1, Mar 8 2011 @ 3pm, 57, 32, 33
23345, 2, Mar 8 2011 @ 3pm, 54, 35, 36

If the where clause was A.date > 'Mar 8 2011' and A.date < 'Mar 9 2011'

The returned data should be:
(table_a_id, groupnumber, fieldx, fieldy, fieldz, fielda, fieldb, fieldm, fieldn)
1, 55, 17, 18, 19, 26, 27, NULL, NULL
1, 57, 14, 15, 16, 29, 30, 32, 33
2, 54, NULL, NULL, NULL, NULL, NULL, 35, 36

Hope this makes sense!

To recap,

- SELECT all id values from table A within date range
- There will be a unique ID, GroupNumber in each returned row
- I need the most recent groupnumber row (most recent 'dateupdated')
- If groupnumber in one of the three tables does not exist, return NULL for the columns that were to be selected.

I believe I may need to use CTE and/or RANK() OVER PARTITION... but not certain.

Thank you!
Adrian

Lowfatspread

yes use row_number()

but i do't see the need for a cte....

you just need to left join (?) (or Inner Join)
to the FULL JOINed set of the other 3 tables B,C,D

like this

Select a.ID
      ,z.groupnumber
      ,fieldx,fieldy,fieldz,fielda,fieldb,fieldm,fieldn
  From TableA as A
 Left Outer Join
(
  Select coalesce(b.tablea_id,c.tablea_id,d,tablea_id) as id
        ,coalesce(b.groupnumber,c.groupnumber,d.groupnumber) as groupnumber
        ,fieldx,fieldy,fieldz,fielda,fieldb,fieldm,fieldn
    from
(
Select y.*
  from (
select x.*
      ,row_number() over (partition by tableA_id
                             ,groupnumber 
                           order by updated desc) as rn
  from tableB as x
       ) as Y
  Where rn=1
) as B
Full Outer Join
(
Select y.*
  from (
select x.*
      ,row_number() over (partition by tableA_id
                             ,groupnumber 
                           order by updated desc) as rn
  from tableC as x
       ) as Y
  Where rn=1
) as C
on b.tablea_id=c.tablea_id
and b.groupnumber=c.groupnumber
full outer join
(
Select y.*
  from (
select x.*
      ,row_number() over (partition by tableA_id
                             ,groupnumber 
                           order by updated desc) as rn
  from tableD as x
       ) as Y
  Where rn=1
) as d
on c.tablea_id=b.tablea_id
and c.groupnumber=b.groupnumber
) as Y
) as Z
on A.id=Z.id
Where a,date between '20110308' and '20110309'
order by 1

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adrian78

ASKER

Hi LowFat... I'm having a problem getting your translated query to execute. I think there is a missing bracket along the way so I just need to flush that out first and then hopefully get it to run.

Sharath, thanks for your solution. I will give it a go as well.

Thanks, guys!

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adrian78

ASKER

Hi Guys,

Lowfat... thank you for your responses. I tried it out and I did get it to execute but it didn't return the desired result. It was very close but there were some duplicate entries (ie. The same id,groupnumber appeared more than once a couple of times out of 100 or so records)... so close but the query needs a bit more tweaking.

Sharath, your solution worked right out of the box and returned the desired result.

I'm not sure who's query is more efficient.. I think if I'm able to get Lowfats solution to work it may be a bit faster but I'm not sure. Because Sharath's solution worked I'm going to award him with the correct solution but also credit some towards lowfat's solution which is close.