?
Solved

MS SQL Left outer join output problem

Posted on 2009-02-19
9
Medium Priority
?
603 Views
Last Modified: 2013-11-29
Hi Experts,

I am using MS Access 97 and I have two tables ReportTable and ReportData table

ReportTable have the fields
   ReportID
  ReportName
and
ReportDataTable contains
   ReportID
   VaraiableID

I have used a query as follows to get all the data from both the tables

SELECT ReportTable.*,ReportDataTable.VariableID FROM ReportTable LEFT OUTER JOIN ReportDataTable ON ReportTable.ReportID  = ReportDataTable.ReportID

I have given left outer join to show  all the value of ReportTable  even there is no corresponding entry in ReportDataTable&
 
The issue is that when left outer join query is used the order of VariableID changes.
 
I will give u an example

ReportTable
ReportID    ReportName
 444          Report444
 555          Report555
 666          Report666
 

ReportDataTable
 ReportID   VariableID
 555           34
 555           82
555            87
 555          12
 555           67
 555           55
 555            23
 555            1
 666             34
 666            82
 666             87
 666            12
 

When we give left outer join the result will be as follows&


ReportID   ReportName  VariableID
 555         Report555       1
 555         Report555      23
 555         Report555      55
 555         Report555      67
 555         Report555      12
 555         Report555      87
 555          Report555     82
 555          Report555     34
 666          Report666     12
 666          Report666     87
 666          Report666     82
 666          Report666     34
 444          Report444

the issue is that here the order is changed i.e the order of VariableID is not as in ReportDataTable. Here the variable id has been sorted in for each ReportID.
I want the result as follows

ReportID   ReportName  VariableID
 555         Report555      34
 555         Report555      82
 555         Report555      87
 555         Report555      12
 555         Report555      67
 555         Report555      55
 555          Report555     23
 555          Report555     1
 666          Report666     34
 666          Report666     82
 666          Report666     87
 666          Report666     12
 444          Report444

How can I modify the query to get the result as above.
0
Comment
Question by:Aneesmhd
  • 4
  • 4
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23680250
there is no "order" for the rows in a table.
only the output of the SELECT given a ORDER BY will give you a guaranteed "order".

so, if you need a certain order, you need to have a value/column in the table(s) that you can use to ORDER BY
0
 
LVL 16

Expert Comment

by:duncancumming
ID: 23684176
what angelIII says.  You'll need an OrderBy column on your ReportData table, and possibly on your Report table too.  Alternatively, the best you can hope for just now is to do some numeric sorting on the VariableID, which won't give you the order you're asking for, but it will put it into some semblance of order



SELECT ReportTable.*,ReportDataTable.VariableID FROM ReportTable LEFT OUTER JOIN ReportDataTable ON ReportTable.ReportID  = ReportDataTable.ReportID
ORDER BY ReportTable.ReportID, ReportDataTable.VariableID

Open in new window

0
 

Author Comment

by:Aneesmhd
ID: 23689448
Hi angelIII,
Hi duncancumming,
Thank you for your reply, when i have given ORDER BY it has been sorted in  numeric order of VariableID and ReportID like below..
ReportID      ReportName      VariableID
444      Report444      
555      Report555      1
555      Report555      12
555      Report555      23
555      Report555      34
555      Report555      55
555      Report555      67
555      Report555      82
555      Report555      87
666      Report666      12
666      Report666      34
666      Report666      82
666      Report666      87

But i dont expect the sorting in numeric order, I just want the result of the query in the order as in VariableID in ReportDataTable.

i.e the result one as in the question i have given, if I am giving inner join i will get the expected result,
i.e  if the query is given as follows
SELECT ReportTable.*,ReportDataTable.VariableID FROM ReportTable INNER  JOIN ReportDataTable ON ReportTable.ReportID  = ReportDataTable.ReportID
i am getting the result as
ReportID     ReportName      VariableID
555             Report555            34
 555           Report555             82
 555           Report555             87
 555           Report555             12
 555           Report555             67
 555           Report555             55
 555           Report555            23
 555           Report555            1
 666           Report666            34
 666           Report666            82
 666           Report666            87
 666           Report666            12

But here i will not get the entry
444          Report444   from Report table, since the query is not left outer. but if i am giving left outer join the order changes...... i.e VariableID 1 of Report 555 comes first(here i want to maintain the order of ReportDataTable.

I want to maintain the order because...
My application uses variable IDs in the order which is added to ReportDataTable. Run time it keeps the values in some lists that will work fine, when i restarts the application and in initialization sequence  i am using query to populate the list. that time the order of variable ID changes...

Hope this clear the scenario... please help me...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 23689584
>here i want to maintain the order of ReportDataTable

again, if you don't have a column that you can use to rely on to make a ORDER BY, without ORDER BY you won't get a guaranteed "order" of the rows.

full stop.

so, what you need is something like this:
ReportDataTable
 ReportID   VariableID OrderValue
 555           34          1
 555           82          2
555            87          3
 555          12           4 
 555           67          5
 555           55          6
 555            23         7
 555            1          8
 666             34        1
 666            82         2
 666             87        3
 666            12         4

Open in new window

0
 

Author Closing Comment

by:Aneesmhd
ID: 31548711
Thanks angelIII, this works fine, but i have to modify the table schema :(
Could you please explain why i am getting the order as it is in when i am giving inner join and the order is changed when left outer join... is there any other way to do without adding the column OrderValue ???.
Any way thanks a lot for your help...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23690477
>Could you please explain why i am getting the order as it is in when i am giving inner join and the order is changed when left outer join...
you "expect" something that you should not: a order without ORDER BY.
trying to "explain" does not make any sense, if you know how a db works for storing records.
yes, the rows are stored in a certain order, but the fetching of them is not necessarily in "that" order.

>is there any other way to do without adding the column OrderValue
no
0
 

Author Comment

by:Aneesmhd
ID: 23690542
Could you please explain why i am getting the order as it is in when i am giving inner join and the order is changed when left outer join... is there any other way to do without adding the column OrderValue ???.
0
 

Author Comment

by:Aneesmhd
ID: 23690571
Hi angelIII,
But for inner join it is getting as in the inserted order, but when i changed the query to left outer join the order has been changed...
The problem is my application is delivered, i was planning to change the query in my application and do the fix, but now i have to modify the database tooo.... no problem i hope i can convince my customer..

Any way thanks a lot for your assistance...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23690582
it's a result of how db works internally. to "explain", take books like "inside sql server" or "sql unleashed", and you will find some information about it.

for the rest, take a plat in stone, and write down:

NEVER assume a order WITHOUT a ORDER BY.

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

839 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