Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Need help with subquery on same table


help with same table subquery

I am trying to extract and compare 2 sets of data from the same table in a
T-SQL (or even SP) but am running into a major head ache. The sample database
layout is as follows:

[GroupSeqNumber] = is a bit column that contains an int value for each batch
load. So one batch of 100 records get loaded they are set to one. Then
another batch of records get loaded to 2 and so forth. This is per location.

[Review] = is a bit column that sets the state of that record in a needs
review state. So there could be 1 or more batches in a review state.

[Processed] = is a bit column that states that the record has been
processed. Also there can be one or more batches that have been processed
here.

[SSN]
[FirstName]
[LastName]
[Location] sting column

What I am trying to do is retrieve all the batches that need review, only
the last batch that has been processed (the last processed batch is the
baseline) which are in the same location and determine if thier first or last
name has changed based on thier SSN.

sample data

1 | 0 | 1 | 2225553333 | John | Smith | NH
2 | 0 | 1 | 2225553333 | John | Smith | NH
3 | 1 | 0 | 2225553333 | Johny | Smith |NH
1 | 0 | 1 | 5556664444 | Andy | Doe | FL
2 | 1 | 0 | 5556664444 | Andy | Dole | FL
4 | 1 | 0 | 2225553333 | John | Apple| NH

-- This retrieves all the validated GroupSeqNumber and Location sets I need
to compare with
select Max( GroupSeqNumber) as MaxNum, Location from SampleTbl where
Processed = 1 group by MaxNum

-- Then retrieve all the review sets, again based on GroupSeqnumber and
Location as sets
select distinct GroupSeqNumber, location from SampleTbl where Review = 1

The problem I am running into is when I try to nest them as a subquery I can
only get errors.

--example
select * from  
        SampleTbl as M1,
        (select Max( S1.GroupSeqNumber) as MaxNum, S1.Location from SampleTbl as S1
where S1.Processed = 1 group by S1.Location) as SO1,  
        (select distinct B1GroupSeqNumber, B1.Location from SampleTbl as B1 where
B1.Review = 1) as BO1
where ..? not sure how I'm going to bind 3 table to compare 2 result sets.

I'm probably trying to solve it the backwards way,
any help TIA

On May 30, 8:34 am, Spaz <S@discussions.microsoft.com> wrote:

I think  you want some thing like this . Not sure

Select  M1.* from SampleTbl as M1
left outer join -- (inner join ??)
     (select Max( S1.GroupSeqNumber) as MaxNum, S1.Location from
SampleTbl as S1
where S1.Processed = 1 group by S1.Location) as SO1
on M1.GroupSeqNumber = SO1.MaxNum
AND M1.Location  = SO1.Location
left outer join
 (select distinct B1.GroupSeqNumber, B1.Location from SampleTbl as B1
where
B1.Review = 1) as BO1
on M1.GroupSeqNumber = BO1.GroupSeqNumber
AND M1.Location  = BO1.Location

-----------------------------------------------Reply-----------------------------------------------

Thanks for the help. It definitely pointed me in the right direction. The
resulting query (for the sample) is:

Select BO1.* from (select M1.* SampleTbl as M1 inner join  
        (select Max( S1.GroupSeqNumber) as MaxNum, S1.Location from
        SampleTbl as S1
        where S1.Processed = 1 group by S1.Location) as SO1
 on M1.GroupSeqNumber = SO1.MaxNum AND M1.Location = SO1.Location,
        (select M2.* SampleTbl as M2 inner join
        (select distinct B1.GroupSeqNumber, B1.Location from SampleTbl as B1
        where B1.Review = 1) as BO1
on M1.GroupSeqNumber = BO1.GroupSeqNumber AND M1.Location = BO1.Location
where BO1.location = SO1.location and ( not (B01.firstname = AO1.firstname)
or
                not (B01.lastname = AO1.lastname))

The only odd thing that I don't get is in the outer "Select *"  does not
return the results of both AO1 and BO1 tables.  I have to explicitly state
"select BO1.*"  otherwise it seems as though I am getting an implicit "Select
AO1.*" , other that the quirky feature, everything is great,
Thanks again

-----------------------------------------------Reply-----------------------------------------------

On May 30, 12:36 pm, Spaz <S@discussions.microsoft.com> wrote:

What you posted can't possibly be the SQL you came up with:
1) the parens don't balance ... there are 8 open parens and only 6
close parens
2) The construct "SELECT M1.* SampleTbl AS M1" seems to be missing a
"FROM" keyword
3) You usually use O1 (letter O, numeral one) but twice you use 01
(numeral zero, numeral one)
4) You mention an AO1 table in your complaint, but there is no "AS
AO1" clause to label anything as an AO1 table

-----------------------------------------------Reply-----------------------------------------------
rpresser,
      Correct, this is not the actual working code. The working code is
based of a table with 7 different flag fields and 40+ data fields. It is a
quick representation of the TSql query being used, based on the sampletbl of
the orig post,  just for use of feedback to the group.

-----------------------------------------------Reply-----------------------------------------------
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.  It is very hard to debug code when you do not let us
see it.

-----------------------------------------------Reply-----------------------------------------------
sry to offend,
in light of re-reading the code, seeing how botched it was (to many projects
over here) I revamped it to read correct.

the working code is as follows (with clean up)
Select NT.* from (select M1.* SampleTbl as M1 inner join  
        (select Max( S1.GroupSeqNumber) as MaxNum, S1.Location from
        SampleTbl as S1
        where S1.Processed = 1 group by S1.Location) as S2
 on M1.GroupSeqNumber = S2.MaxNum AND M1.Location = S2.Location) as MS,

        (select N2.* from SampleTbl as N2 inner join
        (select distinct B1.GroupSeqNumber, B1.Location from SampleTbl as T1
        where T1.Review = 1) as T2
on N2.GroupSeqNumber = T1.GroupSeqNumber AND N2.Location = T1.Location ) as NT

where MS.location = NT.location and ( not (MS.firstname = NT.firstname) or
                not (MS.lastname = NT.lastname))

I guess in this example the keys would be the  [GroupSeqNumber] , [Location]
& [SSN]

the data types were listed in the orig post except make a correction:
"[GroupSeqNumber] = is a bit column that contains an int value" should state
"[GroupSeqNumber] = is an int column"

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc