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.*,ReportDataTa ble.Variab leID 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.
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.*,ReportDataTa
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.
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
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.*,ReportDataTa ble.Variab leID 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...
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.*,ReportDataTa
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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... 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
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
ASKER
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 ???.
ASKER
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...
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.
for the rest, take a plat in stone, and write down:
NEVER assume a order WITHOUT a ORDER BY.
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