Link to home
Start Free TrialLog in
Avatar of Aneesmhd
Aneesmhd

asked on

MS SQL Left outer join output problem

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.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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
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

Avatar of Aneesmhd
Aneesmhd

ASKER

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...
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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...
>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
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 ???.
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...
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.