?
Solved

Correlation error in SQL 2000

Posted on 2006-04-21
2
Medium Priority
?
572 Views
Last Modified: 2008-02-01
Hi,

I am using SQL 2000 - and the Nortwind database - specifically the Order Details table and the Orders table. I am writing a correlated sub query that will display each country name and the order with the highest OrderID. So far this is what I have:

SELECT    Orders.OrderID,
          Orders.ShipCountry,
          OrderDetails.Quantity
         
FROM     Orders
        INNER JOIN (SELECT  OrderID,
                                 MAX(Quantity)AS Quantity
                       FROM     OrderDetails
          Group By ShipCountry) OrderDetails On Orders.OrderID = OrderDetails.OrderID
        INNER JOIN Orders On Orders.ShipCountry = OrderDetails.Quantity
Order By Order Details.Quantity

This is the error I am receiving. Please show me the way that I can alphabetize each contry and withing show the highet ordered.

Server: Msg 1013, Level 15, State 1, Line 10
Tables or functions 'Orders' and 'Orders' have the same exposed names.
 Use correlation names to distinguish them.

Thanks,

Maria
0
Comment
Question by:garciam7254
2 Comments
 

Author Comment

by:garciam7254
ID: 16520287
USE NORTHWIND
GO
select OrderID, ShipCountry
FROM       Orders
where Quantity =
   (select MAX (Quantity)
     from [Order Details]
where Orders.OrderID = [Order Details].Quantity)
order by ShipCountry, OrderID


OK, I know have this - but still getting an error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Quantity'.

Qunatiy is a valid column name undr Order Detaisl

Please help
0
 
LVL 4

Accepted Solution

by:
csachdeva earned 2000 total points
ID: 16522541
Dear maria,

use the following query

SELECT    Orders.OrderID,
          Orders.ShipCountry,
          OrderDetails1.Quantity
FROM     Orders
        INNER JOIN (SELECT  OrderID, MAX(Quantity)AS Quantity
                       FROM     [Order Details]
          Group By OrderID) OrderDetails1 On Orders.OrderID = OrderDetails1.OrderID
      Order By OrderDetails1.Quantity

its giving the folloowing result:

10782      Argentina      1
10807      Italy      1
10767      Belgium      2
10963      Portugal      2
10992      USA      2
10422      Italy      2
10531      Argentina      2
10505      Canada      3
10509      Germany      3
10738      France      3
10754      Italy      3
10900      Brazil      3
10867      USA      3
10870      Poland      3
11019      Argentina      3
11057      UK      3
11037      Spain      4
10843      France      4
10947      UK      4
10586      Italy      4
10589      USA      4
10295      France      4
10308      Mexico      5
10323      Germany      5
10366      Spain      5
10568      Spain      5
10602      Denmark      5
10615      Finland      5
10620      Canada      5
10753      Italy      5
10708      USA      5
10710      Italy      5
10674      UK      5
10950      Italy      5
10928      Spain      5
10898      Argentina      5
10828      Argentina      5
10887      Spain      5
10775      USA      6
10822      USA      6
11004      Belgium      6
10673      Finland      6
10517      UK      6
10371      France      6
10281      Spain      6
10282      Spain      6
10378      Sweden      6
10426      Spain      7
10311      France      7
10538      UK      7
10544      USA      7
10972      France      7
10810      Canada      7
10883      USA      8
11006      USA      8
10899      Venezuela      8
10520      Norway      8
10631      France      8
10639      Norway      8
10752      UK      8
10683      France      9
10528      USA      9
10341      Denmark      9
10969      Brazil      9
10831      Norway      9
11067      Germany      9
11060      Italy      10
11049      Brazil      10
11051      France      10
11043      France      10
11026      Italy      10
10840      Venezuela      10
10853      Germany      10
10881      Argentina      10
10874      Spain      10
10858      France      10
10805      USA      10
10798      UK      10
10783      Brazil      10
10778      Sweden      10
10785      Venezuela      10
10973      France      10
10974      USA      10
10964      France      10
11005      Finland      10
11003      USA      10
10917      Spain      10
10926      Mexico      10
10334      France      10
10321      UK      10
10310      USA      10
10306      Spain      10
10307      USA      10
10288      Italy      10
10259      Mexico      10
10268      Venezuela      10
10394      USA      10
10466      Brazil      10
10482      USA      10
10534      Germany      10
10521      Argentina      10
10508      Germany      10
10545      USA      10
10550      Spain      10
10624      USA      10
10625      Mexico      10
10609      France      10
10604      Portugal      10
10599      UK      10
10662      USA      10
10759      Mexico      10
10716      Argentina      10
10725      Brazil      12
10699      Germany      12
10737      France      12
10679      France      12
10671      France      12
10512      Brazil      12
10473      UK      12
10476      Venezuela      12
10500      France      12
10443      Italy      12
10435      UK      12
10409      Argentina      12
10266      Finland      12
10275      Italy      12
10248      France      12
10293      Mexico      12
10313      Germany      12
10354      Mexico      12
10910      Finland      12
10966      Switzerland      12
10955      Sweden      12
10879      Finland      12
10824      Sweden      12
11044      Poland      12
11062      Italy      12
11074      Denmark      14
10793      UK      14
10956      Germany      14
10971      France      14
10907      France      14
10381      Venezuela      14
10484      UK      14
10548      Germany      14
10574      USA      14
10582      Germany      14
10614      Germany      14
10611      Poland      15
10616      USA      15
10585      Brazil      15
10539      UK      15
10518      Mexico      15
10507      Mexico      15
10668      Germany      15
10672      Sweden      15
10648      Brazil      15
10741      UK      15
10728      Brazil      15
10730      France      15
10749      UK      15
10702      Germany      15
10723      USA      15
10491      Portugal      15
10493      France      15
10445      Sweden      15
10468      Germany      15
10386      Brazil      15
10387      Norway      15
10437      Finland      15
10375      USA      15
10331      France      15
10256      Brazil      15
10262      USA      15
10276      Mexico      15
10279      Germany      15
10906      Poland      15
10911      Spain      15
10909      Norway      15
10890      France      15
10891      Germany      15
10792      Poland      15
10794      Brazil      15
10770      Brazil      15
10835      Germany      15
10864      UK      15
11071      Venezuela      15
11061      USA      15
11042      Brazil      15
11034      USA      15
11016      UK      16
10832      France      16
10771      Austria      16
10815      USA      16
10896      Belgium      16
10975      Canada      16
10952      Germany      16
10965      USA      16
10724      Canada      16
10739      France      18
10506      Germany      18
10559      France      18
10336      Portugal      18
10350      France      18
10434      Sweden      18
10391      Germany      18
10397      Portugal      18
10489      Austria      18
11009      Spain      18
11015      Norway      18
10994      Denmark      18
10944      Canada      18
10945      Germany      20
10937      Argentina      20
10940      France      20
10927      France      20
10934      Germany      20
10888      Spain      20
10908      Italy      20
10905      Brazil      20
10916      Argentina      20
10995      Mexico      20
11013      Spain      20
11010      Italy      20
10958      Argentina      20
10959      Brazil      20
10976      Venezuela      20
10982      Canada      20
10809      Brazil      20
10821      USA      20
10818      Italy      20
10819      Argentina      20
10797      Germany      20
10806      France      20
10787      France      20
10790      Brazil      20
10791      Germany      20
10779      Germany      20
10777      Brazil      20
10833      Germany      20
10834      Brazil      20
10825      Germany      20
10842      Mexico      20
10856      Mexico      20
10860      France      20
10863      Venezuela      20
10878      Germany      20
10873      Finland      20
11018      USA      20
11025      Finland      20
11029      Switzerland      20
11040      USA      20
11046      Germany      20
11054      Argentina      20
11055      Venezuela      20
11069      Mexico      20
11065      Venezuela      20
11073      Mexico      20
11076      France      20
10495      Canada      20
10501      Germany      20
10496      Brazil      20
10478      France      20
10446      Germany      20
10448      Argentina      20
10450      France      20
10454      France      20
10385      USA      20
10383      UK      20
10379      Brazil      20
10377      UK      20
10438      Germany      20
10425      France      20
10428      Italy      20
10412      Finland      20
10415      USA      20
10423      Brazil      20
10420      Brazil      20
10416      Finland      20
10352      Portugal      20
10358      France      20
10369      USA      20
10363      Germany      20
10338      USA      20
10329      USA      20
10317      USA      20
10318      UK      20
10322      Mexico      20
10277      Germany      20
10274      France      20
10261      Brazil      20
10251      France      20
10292      Brazil      20
10290      Brazil      20
10299      Brazil      20
10301      Germany      20
10560      Germany      20
10562      Italy      20
10529      Belgium      20
10587      Brazil      20
10577      USA      20
10578      UK      20
10621      UK      20
10622      Brazil      20
10626      Sweden      20
10629      Spain      20
10606      Brazil      20
10732      France      20
10735      USA      20
10727      Italy      20
10696      USA      20
10705      Venezuela      20
10649      Belgium      20
10651      Germany      20
10652      Brazil      20
10654      Sweden      20
10655      Italy      20
10640      Germany      20
10645      Brazil      20
10665      USA      20
10685      Brazil      20
10692      Germany      20
10695      Finland      20
10676      Mexico      21
10644      Brazil      21
10643      Germany      21
10660      USA      21
10720      Brazil      21
10756      USA      21
10610      France      21
10579      USA      21
10576      Mexico      21
10593      Germany      21
10294      USA      21
10284      Germany      21
10254      Switzerland      21
10460      Sweden      21
10462      UK      21
10477      Portugal      21
10474      Mexico      21
11058      Germany      21
10872      Spain      21
10876      France      21
10827      France      21
10829      UK      21
10999      Germany      21
10935      Brazil      21
10943      UK      21
10920      UK      24
10923      France      24
10919      Venezuela      24
10960      Venezuela      24
10803      Brazil      24
11077      USA      24
11045      Canada      24
11020      Germany      24
10271      USA      24
10291      Brazil      24
10312      Germany      24
10365      Mexico      24
10349      USA      24
10583      Finland      24
10532      UK      24
10556      Denmark      24
10542      Germany      24
10706      USA      24
10664      Portugal      24
10636      Finland      25
10726      UK      25
10733      Sweden      25
10564      USA      25
10565      Canada      25
10523      UK      25
10504      USA      25
10592      Germany      25
10628      France      25
10348      Germany      25
10355      UK      25
10278      Sweden      25
10257      Venezuela      25
10486      Venezuela      25
10497      Germany      25
10499      Venezuela      25
10453      UK      25
10799      Germany      25
10772      Germany      25
10875      Sweden      25
10862      Germany      25
10914      Brazil      25
10925      Brazil      25
10942      Italy      28
11014      Venezuela      28
10467      Italy      28
10433      Portugal      28
10384      Sweden      28
10608      Germany      28
10571      Austria      28
10743      UK      28
10656      USA      28
10663      France      30
10653      Germany      30
10650      Brazil      30
10632      Germany      30
10642      Denmark      30
10647      Brazil      30
10669      Denmark      30
10677      Mexico      30
10675      Germany      30
10690      Brazil      30
10686      Austria      30
10682      Mexico      30
10681      USA      30
10734      Brazil      30
10757      USA      30
10755      France      30
10751      Switzerland      30
10712      Ireland      30
10715      France      30
10697      Venezuela      30
10575      Germany      30
10580      Germany      30
10594      USA      30
10596      USA      30
10600      USA      30
10623      Germany      30
10617      USA      30
10525      France      30
10526      Finland      30
10557      Germany      30
10558      UK      30
10552      Venezuela      30
10553      Finland      30
10554      Germany      30
10537      Switzerland      30
10389      Canada      30
10380      Ireland      30
10407      Germany      30
10439      Canada      30
10421      Brazil      30
10464      Portugal      30
10465      Denmark      30
10470      France      30
10471      UK      30
10444      Sweden      30
10498      Venezuela      30
10502      Mexico      30
10494      Brazil      30
10488      Germany      30
10487      Brazil      30
10480      France      30
10270      Finland      30
10265      France      30
10289      UK      30
10280      Sweden      30
10300      Italy      30
10296      Venezuela      30
10304      Mexico      30
10305      USA      30
10309      Ireland      30
10356      Germany      30
10357      Venezuela      30
10364      UK      30
10370      Switzerland      30
10374      Poland      30
10315      UK      30
10319      Mexico      30
10320      Finland      30
11000      USA      30
11007      Portugal      30
10998      Poland      30
10986      Argentina      30
10954      Venezuela      30
10977      Sweden      30
10968      Austria      30
10936      USA      30
10924      Sweden      30
10932      France      30
10933      UK      30
10915      Mexico      30
10901      Venezuela      30
10902      Sweden      30
10877      Brazil      30
10885      Belgium      30
10830      Brazil      30
10839      Brazil      30
10846      Belgium      30
10848      UK      30
10850      France      30
10769      Denmark      30
10789      France      30
10784      Italy      30
10816      USA      30
10820      USA      30
10811      Venezuela      30
10814      France      30
11023      UK      30
11027      Canada      30
11047      UK      30
11041      Switzerland      30
11036      Germany      30
11038      Belgium      30
11075      Switzerland      30
11052      Brazil      30
10882      USA      32
10717      Germany      32
10703      Sweden      35
10704      Brazil      35
10747      Austria      35
10689      Sweden      35
10646      Ireland      35
10536      Germany      35
10566      France      35
10630      Germany      35
10627      USA      35
10597      Austria      35
10569      USA      35
10368      Austria      35
10360      France      35
10283      Venezuela      35
10264      Sweden      35
10255      Switzerland      35
10250      Brazil      35
10483      USA      35
10449      France      35
10408      France      35
10427      Austria      35
10400      UK      35
10857      Sweden      35
10844      Austria      35
10826      France      35
10813      Brazil      35
10796      Venezuela      35
10780      Venezuela      35
10781      Finland      35
10761      USA      35
10893      Germany      35
10904      USA      35
10922      Brazil      35
11053      Austria      35
11059      Brazil      35
11032      USA      35
11028      Germany      35
11022      Brazil      35
11068      Brazil      36
10930      Belgium      36
10985      Ireland      36
10804      UK      36
10457      Germany      36
10367      Denmark      36
10346      USA      36
10541      Brazil      36
10510      USA      36
10666      Switzerland      36
10684      Germany      40
10635      Italy      40
10659      Brazil      40
10746      Switzerland      40
10750      Finland      40
10731      Switzerland      40
10740      USA      40
10736      Ireland      40
10707      UK      40
10709      Brazil      40
10718      Germany      40
10719      USA      40
10519      Switzerland      40
10522      Germany      40
10551      Portugal      40
10546      France      40
10555      USA      40
10573      Mexico      40
10613      Venezuela      40
10325      Germany      40
10314      USA      40
10328      Portugal      40
10332      Canada      40
10333      Finland      40
10337      Germany      40
10340      France      40
10249      Germany      40
10252      Belgium      40
10272      USA      40
10287      Brazil      40
10298      Ireland      40
10302      Belgium      40
10303      Spain      40
10456      Germany      40
10459      France      40
10469      USA      40
10447      Brazil      40
10481      Brazil      40
10404      Italy      40
10388      UK      40
10429      Ireland      40
10436      France      40
10432      USA      40
10411      Canada      40
10413      France      40
10801      Spain      40
10812      Italy      40
10823      Venezuela      40
10760      Belgium      40
10763      France      40
10766      Germany      40
10837      Sweden      40
10859      Germany      40
10866      Sweden      40
10884      USA      40
10989      Brazil      40
10996      Germany      40
11011      Germany      40
10970      Spain      40
10978      Belgium      40
10980      Sweden      40
10957      Venezuela      40
10967      Germany      40
10921      Denmark      40
10939      Italy      40
10946      Denmark      40
10948      Spain      40
10913      Brazil      40
10892      Belgium      40
10889      USA      40
10903      Brazil      40
11070      Germany      40
11063      Ireland      40
11048      Canada      42
11066      USA      42
10931      Switzerland      42
10868      Brazil      42
10861      USA      42
10851      Brazil      42
10786      Brazil      42
10376      Canada      42
10406      Brazil      42
10253      Brazil      42
10619      Canada      42
10701      Ireland      42
10748      USA      44
10745      Germany      45
10667      Austria      45
10285      Germany      45
10335      Ireland      48
10691      Germany      48
10603      USA      48
10661      Ireland      49
10410      Canada      49
10849      Germany      49
10938      Germany      49
10951      Switzerland      50
10953      UK      50
10997      Venezuela      50
10993      Sweden      50
10852      USA      50
10855      USA      50
10841      Belgium      50
10838      Venezuela      50
10869      UK      50
10871      France      50
10880      Sweden      50
10788      Germany      50
10768      UK      50
10774      Sweden      50
10808      USA      50
10800      UK      50
11050      Sweden      50
11056      UK      50
11024      UK      50
10414      Brazil      50
10417      Denmark      50
10431      Canada      50
10405      Venezuela      50
10392      Austria      50
10441      USA      50
10455      Finland      50
10458      Belgium      50
10463      Belgium      50
10330      Venezuela      50
10343      Germany      50
10326      Spain      50
10327      Sweden      50
10347      Brazil      50
10353      Austria      50
10362      France      50
10260      Germany      50
10657      USA      50
10634      France      50
10687      Ireland      50
10680      USA      50
10744      Denmark      50
10742      Canada      50
10729      Venezuela      50
10714      USA      50
10721      Germany      50
10722      USA      50
10572      Sweden      50
10581      Brazil      50
10598      USA      50
10584      France      50
10591      Denmark      50
10561      Sweden      50
10527      Germany      50
10533      Sweden      50
10535      Mexico      50
10530      Austria      50
10513      Germany      50
10511      France      50
10361      Germany      55
10984      USA      55
11002      USA      56
10342      Germany      56
10263      Austria      60
10273      Germany      60
10269      USA      60
10297      France      60
10461      Venezuela      60
10485      Venezuela      60
10475      Belgium      60
10479      USA      60
10490      Venezuela      60
10492      Canada      60
10396      Germany      60
10399      Denmark      60
10382      Austria      60
10390      Austria      60
10424      Canada      60
10418      Germany      60
10419      Switzerland      60
10524      Sweden      60
10567      Ireland      60
10547      UK      60
10540      Germany      60
10590      Canada      60
10570      Canada      60
10601      Venezuela      60
10700      USA      60
10758      Switzerland      60
10688      Denmark      60
10693      USA      60
10670      Germany      60
10637      Brazil      60
10638      Venezuela      60
10641      Venezuela      60
11001      Sweden      60
11012      Germany      60
10987      UK      60
10988      USA      60
10961      Brazil      60
10981      Brazil      60
10949      Canada      60
10929      Germany      60
10912      Ireland      60
10918      Canada      60
10802      Denmark      60
10817      Germany      60
10762      Sweden      60
10836      Austria      60
11035      Belgium      60
11039      Venezuela      60
11021      Germany      63
10795      Austria      65
10698      Austria      65
10402      Austria      65
10258      Austria      65
10990      Austria      66
10773      Austria      70
10845      Germany      70
10886      Brazil      70
11033      Switzerland      70
10267      Germany      70
10339      Canada      70
10316      USA      70
10372      Brazil      70
10344      USA      70
10403      Austria      70
10401      USA      70
10393      USA      70
10395      Venezuela      70
10430      Austria      70
10503      Ireland      70
10658      Germany      70
10605      Canada      70
10618      Canada      70
10543      Venezuela      70
10563      Brazil      70
10514      Austria      70
10351      Austria      77
11064      USA      77
10962      Germany      77
10979      Austria      80
10897      Ireland      80
10941      USA      80
10865      Germany      80
10847      USA      80
10765      Germany      80
11031      USA      80
10345      Germany      80
10359      UK      80
10373      Ireland      80
10324      USA      80
10472      UK      80
10442      Austria      80
10516      Ireland      80
10612      USA      80
10633      Austria      80
10983      USA      84
10991      Germany      90
11008      Austria      90
10694      Germany      90
10440      USA      90
10452      USA      100
10286      Germany      100
10607      USA      100
10588      Germany      100
10549      Germany      100
10854      Austria      100
11030      USA      100
11017      Austria      110
10895      Austria      110
10713      USA      110
10711      USA      120
10678      USA      120
10515      Germany      120
10595      Austria      120
10451      Germany      120
10398      USA      120
10894      USA      120
10776      Austria      120
10764      Austria      130
11072      Austria      130


Hope that will sort out your problem...

Regards,
Chetan Sachdeva
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is about my first experience with programming Arduino.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Loops Section Overview

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question