Select Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-28 : 09:31:27

Hi,

I have the following select statement, which runs but the output result is not as accepted

SELECT cc.Surname
	,cc.ContactCode as [Contact Code]
	,ge.ObsResearchOther as [Other Observational Research]
	,gf.InterResearchOther as [Other Interventional Research]
    , ga.InterestGroupADesc as [Area of Interest]
    , gb.InterestGroupBListADesc as [Observational Research]
    , gc.InterestGroupBListBDesc as [Interventional Research]

 FROM  dbo.aspnet_cprdContacts                 AS cc

 LEFT  OUTER JOIN
       (
       SELECT ai .ContactCode
            , iga.InterestGroupADesc
            , ROW_NUMBER()
                 OVER( PARTITION BY ai.ContactCode
                           ORDER BY iga.InterestGroupADesc
                     ) AS rnum
        FROM  dbo.aspnet_AreaofInterest        AS ai
        INNER JOIN
              dbo.aspnet_InterestGroupA        AS iga
          ON  ai.InterestCode = iga.InterestGroupAID
       )                                       AS ga

 FULL  OUTER JOIN
       (
       SELECT ab.ContactCode
            , igb.InterestGroupBListADesc
            , ROW_NUMBER()
                 OVER( PARTITION BY ab.ContactCode
                           ORDER BY igb.InterestGroupBListADesc
                     ) AS rnum
        FROM  dbo.aspnet_ObservationalResearch AS ab
        INNER JOIN
              dbo.aspnet_InterestGroupBListA   AS igb
          ON  ab.ObsResearchCode = igb.InterestGroupBListAID
       )                                       AS gb
   ON  gb.ContactCode = ga.ContactCode
   AND gb.rnum        = ga.rnum
  

FULL  OUTER JOIN
       (
       SELECT ac .ContactCode
            , igc.InterestGroupBListBDesc
            , ROW_NUMBER()
                 OVER( PARTITION BY ac.ContactCode
                           ORDER BY igc.InterestGroupBListBDesc
                     ) AS rnum
        FROM  dbo.aspnet_InterventionalResearch AS ac
        INNER JOIN
              dbo.aspnet_InterestGroupBListB   AS igc
          ON  ac.IntResearchCode = igc.InterestGroupBListBID
       )                                       AS gc
   ON  gc.ContactCode = gb.ContactCode
   AND gc.rnum        = gb.rnum


FULL  OUTER JOIN
       (
       SELECT ae.ContactCode
            , ige.ObsResearchOther
            , ROW_NUMBER()
                 OVER( PARTITION BY ae.ContactCode
                           ORDER BY ige.ObsResearchOther
                     ) AS rnum
        FROM  dbo.aspnet_ObsResearchOther  AS ae
        INNER JOIN
            dbo.aspnet_cprdContacts AS ige
          ON  ae.ContactCode = ige.ContactCode
       )                                       AS ge
   ON  ge.ContactCode = gc.ContactCode
   AND ge.rnum        = gc.rnum

Full Outer JOIN
       (
       SELECT af.ContactCode
            , igf.InterResearchOther
            , ROW_NUMBER()
                 OVER( PARTITION BY af.ContactCode
                           ORDER BY igf.InterResearchOther
                     ) AS rnum
        FROM  dbo.aspnet_InterResearchOther  AS af
      INNER JOIN
            dbo.aspnet_cprdContacts AS igf
          ON  af.ContactCode = igf.ContactCode
       )                                       AS gf
   ON  gf.ContactCode = ge.ContactCode
   AND gf.rnum        = ge.rnum
ON  cc.ContactCode = COALESCE(gf.ContactCode , ge.ContactCode)
where cc.Surname ='Mascrier'

Output Result:

Surname CC OBR OIR AofI OR IR
Macsrier AB Obes Inter biology Data Clinical Trials

Expected Result:

Surname CC OBR OIR AofI OR IR
Macsrier AB Obes Inter biologics Data Clinical
NULL NULL NULL Extract Chemical Device Surveliiance
NULL NULL NULL Refine Cancer Genetic Global

N/B: Entered CC, OBR, OIR as abbreviations to save space.

The results are accurate OK as shown below when the highlited in red statement is removed, but when entered then the reults are unsatisfactory..

Output Result:

Surname CC OBR AofI OR IR
Macsrier AB Obes biologics Data Clinical
NULL NULL NULL Chemical Device Surveliiance
NULL NULL NULL Cancer Genetic Global

any help...
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 12:20:33

try like this


SELECT cc.Surname
	,cc.ContactCode as [Contact Code]
	,ge.ObsResearchOther as [Other Observational Research]
	,gf.InterResearchOther as [Other Interventional Research]
    , ga.InterestGroupADesc as [Area of Interest]
    , gb.InterestGroupBListADesc as [Observational Research]
    , gc.InterestGroupBListBDesc as [Interventional Research]

 FROM  dbo.aspnet_cprdContacts                 AS cc

 LEFT  OUTER JOIN
       (
       SELECT ai .ContactCode
            , iga.InterestGroupADesc
            , ROW_NUMBER()
                 OVER( PARTITION BY ai.ContactCode
                           ORDER BY iga.InterestGroupADesc
                     ) AS rnum
        FROM  dbo.aspnet_AreaofInterest        AS ai
        INNER JOIN
              dbo.aspnet_InterestGroupA        AS iga
          ON  ai.InterestCode = iga.InterestGroupAID
       )                                       AS ga

 FULL  OUTER JOIN
       (
       SELECT ab.ContactCode
            , igb.InterestGroupBListADesc
            , ROW_NUMBER()
                 OVER( PARTITION BY ab.ContactCode
                           ORDER BY igb.InterestGroupBListADesc
                     ) AS rnum
        FROM  dbo.aspnet_ObservationalResearch AS ab
        INNER JOIN
              dbo.aspnet_InterestGroupBListA   AS igb
          ON  ab.ObsResearchCode = igb.InterestGroupBListAID
       )                                       AS gb
   ON  gb.ContactCode = ga.ContactCode
   AND gb.rnum        = ga.rnum
  

FULL  OUTER JOIN
       (
       SELECT ac .ContactCode
            , igc.InterestGroupBListBDesc
            , ROW_NUMBER()
                 OVER( PARTITION BY ac.ContactCode
                           ORDER BY igc.InterestGroupBListBDesc
                     ) AS rnum
        FROM  dbo.aspnet_InterventionalResearch AS ac
        INNER JOIN
              dbo.aspnet_InterestGroupBListB   AS igc
          ON  ac.IntResearchCode = igc.InterestGroupBListBID
       )                                       AS gc
   ON  gc.ContactCode = gb.ContactCode
   AND gc.rnum        = gb.rnum


FULL  OUTER JOIN
       (
       SELECT ae.ContactCode
            , ige.ObsResearchOther
            , ROW_NUMBER()
                 OVER( PARTITION BY ae.ContactCode
                           ORDER BY ige.ObsResearchOther
                     ) AS rnum
        FROM  dbo.aspnet_ObsResearchOther  AS ae
        INNER JOIN
            dbo.aspnet_cprdContacts AS ige
          ON  ae.ContactCode = ige.ContactCode
       )                                       AS ge
   ON  ge.ContactCode = gc.ContactCode
   AND ge.rnum        = gc.rnum

Full Outer JOIN
       (
       SELECT af.ContactCode
            , igf.InterResearchOther
            , ROW_NUMBER()
                 OVER( PARTITION BY af.ContactCode
                           ORDER BY igf.InterResearchOther
                     ) AS rnum
        FROM  dbo.aspnet_InterResearchOther  AS af
      INNER JOIN
            dbo.aspnet_cprdContacts AS igf
          ON  af.ContactCode = igf.ContactCode
       )                                       AS gf
   ON  gf.ContactCode = ge.ContactCode
   AND gf.rnum        = ge.rnum
ON  cc.ContactCode = COALESCE(gf.ContactCode , ge.ContactCode)
and cc.Surname ='Mascrier'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-28 : 12:29:29

Tried the;

and cc.Surname = 'Mascrier'

Received results of all surnames. That is not what I was expecting...

Changing to where - I return to my initial problem...

any help please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 12:31:58

[code]
SELECT cc.Surname
,cc.ContactCode as [Contact Code]
,ge.ObsResearchOther as [Other Observational Research]
,gf.InterResearchOther as [Other Interventional Research]
, ga.InterestGroupADesc as [Area of Interest]
, gb.InterestGroupBListADesc as [Observational Research]
, gc.InterestGroupBListBDesc as [Interventional Research]

FROM dbo.aspnet_cprdContacts AS cc

LEFT OUTER JOIN
(
SELECT ai .ContactCode
, iga.InterestGroupADesc
, ROW_NUMBER()
OVER( PARTITION BY ai.ContactCode
ORDER BY iga.InterestGroupADesc
) AS rnum
FROM dbo.aspnet_AreaofInterest AS ai
INNER JOIN
dbo.aspnet_InterestGroupA AS iga
ON ai.InterestCode = iga.InterestGroupAID
) AS ga

FULL OUTER JOIN
(
SELECT ab.ContactCode
, igb.InterestGroupBListADesc
, ROW_NUMBER()
OVER( PARTITION BY ab.ContactCode
ORDER BY igb.InterestGroupBListADesc
) AS rnum
FROM dbo.aspnet_ObservationalResearch AS ab
INNER JOIN
dbo.aspnet_InterestGroupBListA AS igb
ON ab.ObsResearchCode = igb.InterestGroupBListAID
) AS gb
ON gb.ContactCode = ga.ContactCode
AND gb.rnum = ga.rnum

FULL OUTER JOIN
(
SELECT ac .ContactCode
, igc.InterestGroupBListBDesc
, ROW_NUMBER()
OVER( PARTITION BY ac.ContactCode
ORDER BY igc.InterestGroupBListBDesc
) AS rnum
FROM dbo.aspnet_InterventionalResearch AS ac
INNER JOIN
dbo.aspnet_InterestGroupBListB AS igc
ON ac.IntResearchCode = igc.InterestGroupBListBID
) AS gc
ON gc.ContactCode = gb.ContactCode
AND gc.rnum = gb.rnum

FULL OUTER JOIN
(
SELECT ae.ContactCode
, ige.ObsResearchOther
, ROW_NUMBER()
OVER( PARTITION BY ae.ContactCode
ORDER BY ige.ObsResearchOther
) AS rnum
FROM dbo.aspnet_ObsResearchOther AS ae
INNER JOIN
dbo.aspnet_cprdContacts AS ige
ON ae.ContactCode = ige.ContactCode
) AS ge
ON ge.ContactCode = gc.ContactCode
AND ge.rnum = gc.rnum

Full Outer JOIN
(
SELECT af.ContactCode
, igf.InterResearchOther
, ROW_NUMBER()
OVER( PARTITION BY af.ContactCode
ORDER BY igf.InterResearchOther
) AS rnum
FROM dbo.aspnet_InterResearchOther AS af
INNER JOIN
dbo.aspnet_cprdContacts AS igf
ON af.ContactCode = igf.ContactCode
) AS gf
ON gf.ContactCode = ge.ContactCode
AND gf.rnum = ge.rnum
ON cc.ContactCode = COALESCE(gf.ContactCode , ge.ContactCode)
WHERE cc.Surname ='Mascrier'
OR cc.Surname IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-29 : 06:05:10

Same results as my initial problem..

The problem is not at the surname - it is at

AofI, OR, IR

All these have 3 values each.. But when I execute the code it provided me with one result only.
As mentioned earlier when I had only selected

ge.ObsResearchOther as [Other Observational Research]

it worked fine and it displayed all the 3 values for AofI, OR, IR , however, when I introduced

gf.InterResearchOther as [Other Interventional Research]

though using the same logic, it brought forward only a record each for AofI, OR, IR.

I think the problem is the red highligted text in the code. Maybe something am not doing right!!

Any help please
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 11:39:55

ok...can you post some data from tables? that will be helpful to see how data is. Also post output so that we can see what you're after

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 11:44:02

I would say you over analyzed this

You probably need to start small and build...

Post the DDL, sample data in DML Form and expected results based on the sample Data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources