Select Query

dr223 · #1 (**permalink**) 02-09-12, 06:37

Hi,

I have the following SQL query

Code:

SELECT     dbo.aspnet_cprdContacts.Surname, dbo.aspnet_InterestGroupA.InterestGroupADesc, dbo.aspnet_InterestGroupBListA.InterestGroupBListADesc
FROM         dbo.aspnet_cprdContacts INNER JOIN
                      dbo.aspnet_AreaofInterest ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_AreaofInterest.ContactCode INNER JOIN
                      dbo.aspnet_InterestGroupA ON dbo.aspnet_AreaofInterest.InterestCode = dbo.aspnet_InterestGroupA.InterestGroupAID INNER JOIN
                      dbo.aspnet_ObservationalResearch ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_ObservationalResearch.ContactCode INNER JOIN
                      dbo.aspnet_InterestGroupBListA ON 
                      dbo.aspnet_ObservationalResearch.ObsResearchCode = dbo.aspnet_InterestGroupBListA.InterestGroupBListAID
WHERE     (dbo.aspnet_cprdContacts.Surname = 'Mascrier')

Results:

surname InterestGroupADesc InterestGroupBListADesc

Mascrier Biologics Data Linkage
Mascrier Devices Data Linkage
Mascrier Health Data Linkage
Mascrier Biologics Device Related Research
Mascrier Devices Device Related Research
Mascrier Health Device Related Research

I want the result to be as;

surname InterestGroupADesc InterestGroupBListADesc

Mascrier Biologics Data Linkage
Mascrier Devices Device Related Research
Mascrier Health NULL

Any help will be highly appreciated..

Many Thanks

PracticalProgram · #2 (**permalink**) 02-09-12, 13:30

You have not supplied enough information here for us to help you.

Why would you expect the result set you are looking-for?

There is no reason for us to look at the result set you are getting and for us to be able to say that any particular piece of data being return is correct, or incorrect.

tonkuma · #3 (**permalink**) 02-09-12, 21:44

Aothough I have no MS SQL Server,
I thought that a solution of these kind of problems might be
using FULL OUTER JOIN of dbo.aspnet_InterestGroupA table and dbo.aspnet_InterestGroupBListA table.

The reason to use FULL OUTER JOIN is that
I don't know which is larger between the number of InterestGroupADesc and the number of InterestGroupBListADesc.

Anyhow, if you published enough sample/test data of 5 tables including all special cases and the expected result from the data,
more relevant solutions might be possible.

Example 1:

Code:

SELECT cc.Surname
     , ga.InterestGroupADesc
     , gb.InterestGroupBListADesc
 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 aor.ContactCode
            , igb.InterestGroupBListADesc
            , ROW_NUMBER()
                 OVER( PARTITION BY aor.ContactCode
                           ORDER BY igb.InterestGroupBListADesc
                     ) AS rnum
        FROM  dbo.aspnet_ObservationalResearch AS aor
        INNER JOIN
              dbo.aspnet_InterestGroupBListA   AS igb
          ON  aor.ObsResearchCode = igb.InterestGroupBListAID
       )                                       AS gb
   ON  gb.ContactCode = ga.ContactCode
   AND gb.rnum        = ga.rnum
   ON  cc.ContactCode = COALESCE(gb.ContactCode , ga.ContactCode)
 WHERE cc.Surname = 'Mascrier'
;

The following example also worked on DB2.

Example 2:

Code:

SELECT cc.Surname
     , ga.InterestGroupADesc
     , gb.InterestGroupBListADesc
 FROM  dbo.aspnet_cprdContacts                 AS cc
 LEFT  OUTER JOIN
       LATERAL (
       SELECT 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
        WHERE cc.ContactCode  = ai.ContactCode
       )                                       AS ga
 FULL  OUTER JOIN
       LATERAL (
       SELECT igb.InterestGroupBListADesc
            , ROW_NUMBER()
                 OVER( PARTITION BY aor.ContactCode
                           ORDER BY igb.InterestGroupBListADesc
                     ) AS rnum
        FROM  dbo.aspnet_ObservationalResearch AS aor
        INNER JOIN
              dbo.aspnet_InterestGroupBListA   AS igb
          ON  aor.ObsResearchCode = igb.InterestGroupBListAID
        WHERE cc.ContactCode = aor.ContactCode
       )                                       AS gb
   ON  gb.rnum = ga.rnum
   ON  0=0
 WHERE cc.Surname = 'Mascrier'
;

dr223 · #4 (**permalink**) 02-10-12, 05:08

Thanks Tried the code and I received the following error;

Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'or'
am working with SQL server 2005..

Thank you so much

tonkuma · #5 (**permalink**) 02-10-12, 23:11

Quote:

Originally Posted by dr223

Thanks Tried the code and I received the following error;

Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'or'
am working with SQL server 2005..

Thank you so much

Rename or to another one. For example aor.
I'll edit Example 1 and Example 2 in my previoue post.

tonkuma · #6 (**permalink**) 02-10-12, 23:40

Modified examples passed the test on Mimer SQL Developers - Mimer SQL-2003 Validator

The result of Example 1 was

Code:

Result:

The following features outside Core SQL-2003 are used:

F391, "Long identifiers"
T611, "Elementary OLAP operations"
F591, "Derived tables"
F401, "Extended joined table"

The result of Example 2 was

Code:

Result:

The following features outside Core SQL-2003 are used:

F391, "Long identifiers"
T611, "Elementary OLAP operations"
T491, "LATERAL derived table"
F401, "Extended joined table"

If used "or" instead of "aor", the Validator also returned syntax error, like

Code:

Result:

SELECT cc.Surname 
     , ga.InterestGroupADesc 
     , gb.InterestGroupBListADesc 
 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 or.ContactCode 
              ^-
syntax error: or
  correction: <identifier>

            , igb.InterestGroupBListADesc 
...
...

I thought those who returned syntax error didn't read the context enough.

Here is the result of test on DB2.

Example 3:
Note 1: replaced table names with alias itself like
/*dbo.aspnet_cprdContacts AS*/ cc
so on...
Note 2: WITH clause was used to generate test data.

Code:

------------------------------ Commands Entered ------------------------------
WITH
  cc(Surname , ContactCode) AS (
VALUES
  ( 'Mascrier'  ,  1 )
, ( 'Newdecade' ,  9 )
, ( 'Zolotow'   , 99 )
)
, ai(ContactCode , InterestCode) AS (
VALUES
  ( 1 , 11 )
, ( 1 , 12 )
, ( 1 , 13 )
, ( 9 , 19 )
)
, iga(InterestGroupAID , InterestGroupADesc) AS (
VALUES
  ( 11 , 'Biologics'       )
, ( 12 , 'Devices'         )
, ( 13 , 'Health'          )
, ( 19 , 'Auto Mobile'     )
, ( 99 , 'Special Mission' )
)
, or(ContactCode , ObsResearchCode) AS (
VALUES
  ( 1 , 21 )
, ( 1 , 22 )
, ( 9 , 92 )
, ( 9 , 93 )
)
, igb(InterestGroupBListAID , InterestGroupBListADesc) AS (
VALUES
  ( 21 , 'Data Linkage'            )
, ( 22 , 'Device Related Research' )
, ( 92 , 'Electric Veicle'         )
, ( 93 , 'Hydrogen Fuel Veicle'    )
)
SELECT cc.Surname
     , ga.InterestGroupADesc
     , gb.InterestGroupBListADesc
 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 or .ContactCode
            , igb.InterestGroupBListADesc
            , ROW_NUMBER()
                 OVER( PARTITION BY or.ContactCode
                           ORDER BY igb.InterestGroupBListADesc
                     ) AS rnum
        FROM  /*dbo.aspnet_ObservationalResearch AS*/ or
        INNER JOIN
              /*dbo.aspnet_InterestGroupBListA   AS*/ igb
          ON  or.ObsResearchCode = igb.InterestGroupBListAID
       ) AS gb
   ON  gb.ContactCode = ga.ContactCode
   AND gb.rnum        = ga.rnum
   ON  cc.ContactCode = COALESCE(gb.ContactCode , ga.ContactCode)
-- WHERE cc.Surname = 'Mascrier'
;
------------------------------------------------------------------------------

SURNAME   INTERESTGROUPADESC INTERESTGROUPBLISTADESC
--------- ------------------ -----------------------
Mascrier  Biologics          Data Linkage           
Mascrier  Devices            Device Related Research
Mascrier  Health             -                      
Newdecade Auto Mobile        Electric Veicle        
Newdecade -                  Hydrogen Fuel Veicle   
Zolotow   -                  -                      

  6 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech