Results 1 to 6 of 6

Thread: Select Query

  1. #1
    Join Date
    Jul 2009
    Posts
    23

    Unanswered: Select Query

    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

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    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.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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'
    ;
    Last edited by tonkuma; 02-11-12 at 00:15. Reason: Replace "or" to "aor" in sample codes.

  4. #4
    Join Date
    Jul 2009
    Posts
    23
    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

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by dr223 View Post
    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.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •