Query 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

Query Statement..

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-10 : 05:43:14

Hi,

I have an error stating that;

Msg 156, Level 15, State 1, Line 51
Incorrect syntax near the keyowrd 'ON'

SELECT cc.Surname
     , ga.InterestGroupADesc
     , gb.InterestGroupBListADesc
	 , gc.InterestGroupBListBDesc
 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
   ON  cc.ContactCode = COALESCE(gb.ContactCode , ga.ContactCode)

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 = ga.ContactCode
   AND gc.rnum        = ga.rnum
 ON  cc.ContactCode = COALESCE(gc.ContactCode , ga.ContactCode)
 WHERE cc.Surname = 'Mascrier'
;

any help please

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-10 : 05:55:45

ON ... AND ... ON ... will not work

No, you're never too old to Yak'n'Roll if you're too young to die.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-10 : 06:02:30

It worked for;


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 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
   ON  cc.ContactCode = COALESCE(gb.ContactCode , ga.ContactCode)
 WHERE cc.Surname = 'Mascrier'
;

I am trying to enter another Full outer join with an extra column of display ( igc.InterestGroupBListBDesc)

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 09:13:15

quote:

Originally posted by dr223

It worked for;


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
ON  cc.ContactCode = ga.ContactCode
 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
   ON  cc.ContactCode = COALESCE(gb.ContactCode , ga.ContactCode)
 WHERE cc.Surname = 'Mascrier'
;

I am trying to enter another Full outer join with an extra column of display ( igc.InterestGroupBListBDesc)

Thanks

should be this
gb.ContactCode = ga.ContactCode imples that cc.ContactCode = gb.ContactCode so no need to repeat it again in second right outer join

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

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-10 : 09:32:11

Ok - the output result isnt what I expected when I deleted the second ON cc.ContactCode...

Therefore post #3 seems to be working OK - with exactly the results I want, I only faced the problem when I wrote another full join and introduced another column as shown in the coe below;

 SELECT cc.Surname
     , ga.InterestGroupADesc
     , gb.InterestGroupBListADesc
	 , gc.InterestGroupBListBDesc
 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
   ON  cc.ContactCode = COALESCE(gb.ContactCode , ga.ContactCode)

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 = ga.ContactCode
   AND gc.rnum        = ga.rnum
 ON  cc.ContactCode = COALESCE(gc.ContactCode , ga.ContactCode)
 WHERE cc.Surname = 'Mascrier'
;

Error message:

Msg 156, Level 15, State 1, Line 51
Incorrect syntax near the keyowrd 'ON'

Any ideas - thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 09:36:33

quote:

Originally posted by dr223

Ok - the output result isnt what I expected when I deleted the second ON cc.ContactCode...

Therefore post #3 seems to be working OK - with exactly the results I want, I only faced the problem when I wrote another full join and introduced another column as shown in the coe below;

 SELECT cc.Surname
     , ga.InterestGroupADesc
     , gb.InterestGroupBListADesc
	 , gc.InterestGroupBListBDesc
 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
   ON  cc.ContactCode = COALESCE(gb.ContactCode , ga.ContactCode)

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 = ga.ContactCode
   AND gc.rnum        = ga.rnum
 ON  cc.ContactCode = COALESCE(gc.ContactCode , ga.ContactCode)
 WHERE cc.Surname = 'Mascrier'
;

Error message:

Msg 156, Level 15, State 1, Line 51
Incorrect syntax near the keyowrd 'ON'

Any ideas - thanks

its again the same issue
remove that extra ON you've just like waht i showed before. there's no need for that as gc.ContactCode = ga.ContactCode already covers it

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

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-10 : 09:46:00

[code]SELECT cc.Surname
, ga.InterestGroupADesc
, gb.InterestGroupBListADesc
, igc.InterestGroupBListBDesc
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 = ga.ContactCode
AND gc.rnum = ga.rnum
WHERE cc.Surname = 'Mascrier'
;

[/code]

Msg 156, Level 15, State 1, Line 52
Incorrect syntax near the keyword 'WHERE'.

Why?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 09:58:34

please use it as suggested. please dont make any changes and mess it up again


SELECT cc.Surname
     , ga.InterestGroupADesc
     , gb.InterestGroupBListADesc
	 , igc.InterestGroupBListBDesc
 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
ON  cc.ContactCode = ga.ContactCode
 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 = ga.ContactCode
   AND gc.rnum        = ga.rnum
 WHERE cc.Surname = 'Mascrier'

i would suggest you to learn on sql joins
http://www.w3schools.com/sql/sql_join.asp

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

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-10 : 11:12:06

Thank you visakh16 - Finally resolved.

Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 11:18:59

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources