Mixing
How to display rows in nested format when matches multiple rows with linking table oracle
1
Table 1: Income
st_id, st_date, st_sl, income_amount
-------------------------------------
12 11/11/2018 1 100
12 11/11/2018 2 10
13 11/11/2018 1 50
Table 2: Util
util_date, serial_no, st_id, st_date, st_sl, util_amount
----------------------------------------------------------
12/11/2018 1 12 11/11/2018 1 20
12/11/2018 2 12 11/11/2018 1 50
13/11/2018 1 12 11/11/2018 1 30
12/11/2018 1 13 11/11/2018 1 50
I want the result as:
st_id, st_date, st_sl, income_amount, util_date, util_amount
---------------------------------------------------------------
12 11/11/2018 1 100 12/11/2018 20
12/11/2018 50
12/11/2018 30
12 11/11/2018 2 10 null null
13 11/11/2018 1 50 12/11/2018 50
I want a single row from table 1 when the linking table has multiple rows matching. like above.
What could be query to achieve this type of result? Please help.
The below query is normal sql query for linking 2 tables. It gives duplicate like records from Income table (Means single row repeats due to the Income table ref present in two rows in Util table). But I want single row from Income table & multiple rows from Util table like above example.
select st_id, st_date, st_sl, income_amount, util_date, util_amount
from income i, util u
where i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
sql oracle
asked Nov 20 '18 at 9:12
Chandramohan DivyaChandramohan Divya
155
add a comment |
1
Table 1: Income
st_id, st_date, st_sl, income_amount
-------------------------------------
12 11/11/2018 1 100
12 11/11/2018 2 10
13 11/11/2018 1 50
Table 2: Util
util_date, serial_no, st_id, st_date, st_sl, util_amount
----------------------------------------------------------
12/11/2018 1 12 11/11/2018 1 20
12/11/2018 2 12 11/11/2018 1 50
13/11/2018 1 12 11/11/2018 1 30
12/11/2018 1 13 11/11/2018 1 50
I want the result as:
st_id, st_date, st_sl, income_amount, util_date, util_amount
---------------------------------------------------------------
12 11/11/2018 1 100 12/11/2018 20
12/11/2018 50
12/11/2018 30
12 11/11/2018 2 10 null null
13 11/11/2018 1 50 12/11/2018 50
I want a single row from table 1 when the linking table has multiple rows matching. like above.
What could be query to achieve this type of result? Please help.
The below query is normal sql query for linking 2 tables. It gives duplicate like records from Income table (Means single row repeats due to the Income table ref present in two rows in Util table). But I want single row from Income table & multiple rows from Util table like above example.
select st_id, st_date, st_sl, income_amount, util_date, util_amount
from income i, util u
where i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
sql oracle
asked Nov 20 '18 at 9:12
Chandramohan DivyaChandramohan Divya
155
1
What did you try so far?
– ETO
Nov 20 '18 at 9:40
can you add the query to the quesion?
– ezain
Nov 20 '18 at 10:21
"I want a single row from table 1 when the linking table has multiple rows matching." Your sample data has three rows for the first matching row. Hence, I don't understand what you are asking for.
– Gordon Linoff
Nov 20 '18 at 12:11
Exactly. The first row of income table is matching with 3 rows in util table. But in the output the income table column should be displayed once & util table values should be displayed in 3 rows. ( Income table values suppressed in 2nd & 3rd rows)
– Chandramohan Divya
Nov 21 '18 at 4:19
add a comment |
1
1
1
Table 1: Income
st_id, st_date, st_sl, income_amount
-------------------------------------
12 11/11/2018 1 100
12 11/11/2018 2 10
13 11/11/2018 1 50
Table 2: Util
util_date, serial_no, st_id, st_date, st_sl, util_amount
----------------------------------------------------------
12/11/2018 1 12 11/11/2018 1 20
12/11/2018 2 12 11/11/2018 1 50
13/11/2018 1 12 11/11/2018 1 30
12/11/2018 1 13 11/11/2018 1 50
I want the result as:
st_id, st_date, st_sl, income_amount, util_date, util_amount
---------------------------------------------------------------
12 11/11/2018 1 100 12/11/2018 20
12/11/2018 50
12/11/2018 30
12 11/11/2018 2 10 null null
13 11/11/2018 1 50 12/11/2018 50
I want a single row from table 1 when the linking table has multiple rows matching. like above.
What could be query to achieve this type of result? Please help.
The below query is normal sql query for linking 2 tables. It gives duplicate like records from Income table (Means single row repeats due to the Income table ref present in two rows in Util table). But I want single row from Income table & multiple rows from Util table like above example.
select st_id, st_date, st_sl, income_amount, util_date, util_amount
from income i, util u
where i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
sql oracle
asked Nov 20 '18 at 9:12
Chandramohan DivyaChandramohan Divya
155
Table 1: Income
st_id, st_date, st_sl, income_amount
-------------------------------------
12 11/11/2018 1 100
12 11/11/2018 2 10
13 11/11/2018 1 50
Table 2: Util
util_date, serial_no, st_id, st_date, st_sl, util_amount
----------------------------------------------------------
12/11/2018 1 12 11/11/2018 1 20
12/11/2018 2 12 11/11/2018 1 50
13/11/2018 1 12 11/11/2018 1 30
12/11/2018 1 13 11/11/2018 1 50
I want the result as:
st_id, st_date, st_sl, income_amount, util_date, util_amount
---------------------------------------------------------------
12 11/11/2018 1 100 12/11/2018 20
12/11/2018 50
12/11/2018 30
12 11/11/2018 2 10 null null
13 11/11/2018 1 50 12/11/2018 50
I want a single row from table 1 when the linking table has multiple rows matching. like above.
What could be query to achieve this type of result? Please help.
The below query is normal sql query for linking 2 tables. It gives duplicate like records from Income table (Means single row repeats due to the Income table ref present in two rows in Util table). But I want single row from Income table & multiple rows from Util table like above example.
select st_id, st_date, st_sl, income_amount, util_date, util_amount
from income i, util u
where i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
sql oracle
sql oracle
asked Nov 20 '18 at 9:12
Chandramohan DivyaChandramohan Divya
155
asked Nov 20 '18 at 9:12
Chandramohan DivyaChandramohan Divya
155
edited Nov 22 '18 at 5:47
Chandramohan Divya
asked Nov 20 '18 at 9:12
Chandramohan DivyaChandramohan Divya
155
asked Nov 20 '18 at 9:12
Chandramohan DivyaChandramohan Divya
155
asked Nov 20 '18 at 9:12
Chandramohan DivyaChandramohan Divya
155
155
1
What did you try so far?
– ETO
Nov 20 '18 at 9:40
can you add the query to the quesion?
– ezain
Nov 20 '18 at 10:21
"I want a single row from table 1 when the linking table has multiple rows matching." Your sample data has three rows for the first matching row. Hence, I don't understand what you are asking for.
– Gordon Linoff
Nov 20 '18 at 12:11
Exactly. The first row of income table is matching with 3 rows in util table. But in the output the income table column should be displayed once & util table values should be displayed in 3 rows. ( Income table values suppressed in 2nd & 3rd rows)
– Chandramohan Divya
Nov 21 '18 at 4:19
add a comment |
1
What did you try so far?
– ETO
Nov 20 '18 at 9:40
can you add the query to the quesion?
– ezain
Nov 20 '18 at 10:21
"I want a single row from table 1 when the linking table has multiple rows matching." Your sample data has three rows for the first matching row. Hence, I don't understand what you are asking for.
– Gordon Linoff
Nov 20 '18 at 12:11
Exactly. The first row of income table is matching with 3 rows in util table. But in the output the income table column should be displayed once & util table values should be displayed in 3 rows. ( Income table values suppressed in 2nd & 3rd rows)
– Chandramohan Divya
Nov 21 '18 at 4:19
1
1
What did you try so far?
– ETO
Nov 20 '18 at 9:40
What did you try so far?
– ETO
Nov 20 '18 at 9:40
can you add the query to the quesion?
– ezain
Nov 20 '18 at 10:21
can you add the query to the quesion?
– ezain
Nov 20 '18 at 10:21
"I want a single row from table 1 when the linking table has multiple rows matching." Your sample data has three rows for the first matching row. Hence, I don't understand what you are asking for.
– Gordon Linoff
Nov 20 '18 at 12:11
"I want a single row from table 1 when the linking table has multiple rows matching." Your sample data has three rows for the first matching row. Hence, I don't understand what you are asking for.
– Gordon Linoff
Nov 20 '18 at 12:11
Exactly. The first row of income table is matching with 3 rows in util table. But in the output the income table column should be displayed once & util table values should be displayed in 3 rows. ( Income table values suppressed in 2nd & 3rd rows)
– Chandramohan Divya
Nov 21 '18 at 4:19
Exactly. The first row of income table is matching with 3 rows in util table. But in the output the income table column should be displayed once & util table values should be displayed in 3 rows. ( Income table values suppressed in 2nd & 3rd rows)
– Chandramohan Divya
Nov 21 '18 at 4:19
add a comment |
1 Answer
1
active
oldest
votes
1
A correct query to show the data you want should be
select st_id, st_date, st_sl, income_amount, util_date, util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
Please note the left in the join condition.
But you want that "duplicate" income rows to be suppressed to a single row.
This can be done in multiple ways but one ideea would be:
select case when util.serial_no = 1 then i.st_id end as st_id,
case when util.serial_no = 1 then i.st_date end as st_date,
case when util.serial_no = 1 then i.st_sl end as st_sl,
income_amount,
util_date,
util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
UPDATE1:
As the sample data and explanation changed, another query to comply with the request would be:
select
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_id
end as st_id,
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_date
end as st_date,
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_sl
end as st_sl,
i.income_amount,
u.util_date,
u.serial_no,
u.util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
So, the first three columns get values only for the first rows in the group of std_id, st_sl, util_date.
See a fiddle for demonstration. (also it would be really nice if you give a fiddle with test data in the question next time.)
answered Nov 20 '18 at 11:56
Florin GhitaFlorin Ghita
15.7k44063
If the part income amount is utilized on different days, then the util.serial_no will be 1 on 12/11/2018 & again 1 for 13/11/2018. Ex: See the util table 3rd row, this was happened on the same day (12/11/2018) hence i got the util.serial no as 3. In case this utilization is done on next day, again i'll get the util.serial no as 1 & util_date as 13/11/2018. So above query again gives me a "duplicate" rows!!! Is it possible to get output without hard coding like serial_no?
– Chandramohan Divya
Nov 21 '18 at 4:16
I don't get it. My code gives the desired output for the given input in the question? Do you have another case that my query doesn't match the request? pls edit the question with that edge case. I don't know what serial no and utilization are, I just gave the query for given input.
– Florin Ghita
Nov 21 '18 at 11:52
I've modified the Util table data. Now you can check. The first row from Income table coming twice from your code.
– Chandramohan Divya
Nov 23 '18 at 4:06
Ah, ok, you've changed the test data. I'll come back with a possible query.
– Florin Ghita
Nov 23 '18 at 7:16
Forin Ghita, thanks for answering, It works very well for my requirement.
– Chandramohan Divya
Nov 27 '18 at 4:19
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53389641%2fhow-to-display-rows-in-nested-format-when-matches-multiple-rows-with-linking-tab%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
A correct query to show the data you want should be
select st_id, st_date, st_sl, income_amount, util_date, util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
Please note the left in the join condition.
But you want that "duplicate" income rows to be suppressed to a single row.
This can be done in multiple ways but one ideea would be:
select case when util.serial_no = 1 then i.st_id end as st_id,
case when util.serial_no = 1 then i.st_date end as st_date,
case when util.serial_no = 1 then i.st_sl end as st_sl,
income_amount,
util_date,
util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
UPDATE1:
As the sample data and explanation changed, another query to comply with the request would be:
select
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_id
end as st_id,
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_date
end as st_date,
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_sl
end as st_sl,
i.income_amount,
u.util_date,
u.serial_no,
u.util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
So, the first three columns get values only for the first rows in the group of std_id, st_sl, util_date.
See a fiddle for demonstration. (also it would be really nice if you give a fiddle with test data in the question next time.)
answered Nov 20 '18 at 11:56
Florin GhitaFlorin Ghita
15.7k44063
If the part income amount is utilized on different days, then the util.serial_no will be 1 on 12/11/2018 & again 1 for 13/11/2018. Ex: See the util table 3rd row, this was happened on the same day (12/11/2018) hence i got the util.serial no as 3. In case this utilization is done on next day, again i'll get the util.serial no as 1 & util_date as 13/11/2018. So above query again gives me a "duplicate" rows!!! Is it possible to get output without hard coding like serial_no?
– Chandramohan Divya
Nov 21 '18 at 4:16
I don't get it. My code gives the desired output for the given input in the question? Do you have another case that my query doesn't match the request? pls edit the question with that edge case. I don't know what serial no and utilization are, I just gave the query for given input.
– Florin Ghita
Nov 21 '18 at 11:52
I've modified the Util table data. Now you can check. The first row from Income table coming twice from your code.
– Chandramohan Divya
Nov 23 '18 at 4:06
Ah, ok, you've changed the test data. I'll come back with a possible query.
– Florin Ghita
Nov 23 '18 at 7:16
Forin Ghita, thanks for answering, It works very well for my requirement.
– Chandramohan Divya
Nov 27 '18 at 4:19
add a comment |
1
A correct query to show the data you want should be
select st_id, st_date, st_sl, income_amount, util_date, util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
Please note the left in the join condition.
But you want that "duplicate" income rows to be suppressed to a single row.
This can be done in multiple ways but one ideea would be:
select case when util.serial_no = 1 then i.st_id end as st_id,
case when util.serial_no = 1 then i.st_date end as st_date,
case when util.serial_no = 1 then i.st_sl end as st_sl,
income_amount,
util_date,
util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
UPDATE1:
As the sample data and explanation changed, another query to comply with the request would be:
select
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_id
end as st_id,
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_date
end as st_date,
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_sl
end as st_sl,
i.income_amount,
u.util_date,
u.serial_no,
u.util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
So, the first three columns get values only for the first rows in the group of std_id, st_sl, util_date.
See a fiddle for demonstration. (also it would be really nice if you give a fiddle with test data in the question next time.)
answered Nov 20 '18 at 11:56
Florin GhitaFlorin Ghita
15.7k44063
If the part income amount is utilized on different days, then the util.serial_no will be 1 on 12/11/2018 & again 1 for 13/11/2018. Ex: See the util table 3rd row, this was happened on the same day (12/11/2018) hence i got the util.serial no as 3. In case this utilization is done on next day, again i'll get the util.serial no as 1 & util_date as 13/11/2018. So above query again gives me a "duplicate" rows!!! Is it possible to get output without hard coding like serial_no?
– Chandramohan Divya
Nov 21 '18 at 4:16
I don't get it. My code gives the desired output for the given input in the question? Do you have another case that my query doesn't match the request? pls edit the question with that edge case. I don't know what serial no and utilization are, I just gave the query for given input.
– Florin Ghita
Nov 21 '18 at 11:52
I've modified the Util table data. Now you can check. The first row from Income table coming twice from your code.
– Chandramohan Divya
Nov 23 '18 at 4:06
Ah, ok, you've changed the test data. I'll come back with a possible query.
– Florin Ghita
Nov 23 '18 at 7:16
Forin Ghita, thanks for answering, It works very well for my requirement.
– Chandramohan Divya
Nov 27 '18 at 4:19
add a comment |
1
1
1
A correct query to show the data you want should be
select st_id, st_date, st_sl, income_amount, util_date, util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
Please note the left in the join condition.
But you want that "duplicate" income rows to be suppressed to a single row.
This can be done in multiple ways but one ideea would be:
select case when util.serial_no = 1 then i.st_id end as st_id,
case when util.serial_no = 1 then i.st_date end as st_date,
case when util.serial_no = 1 then i.st_sl end as st_sl,
income_amount,
util_date,
util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
UPDATE1:
As the sample data and explanation changed, another query to comply with the request would be:
select
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_id
end as st_id,
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_date
end as st_date,
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_sl
end as st_sl,
i.income_amount,
u.util_date,
u.serial_no,
u.util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
So, the first three columns get values only for the first rows in the group of std_id, st_sl, util_date.
See a fiddle for demonstration. (also it would be really nice if you give a fiddle with test data in the question next time.)
answered Nov 20 '18 at 11:56
Florin GhitaFlorin Ghita
15.7k44063
A correct query to show the data you want should be
select st_id, st_date, st_sl, income_amount, util_date, util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
Please note the left in the join condition.
But you want that "duplicate" income rows to be suppressed to a single row.
This can be done in multiple ways but one ideea would be:
select case when util.serial_no = 1 then i.st_id end as st_id,
case when util.serial_no = 1 then i.st_date end as st_date,
case when util.serial_no = 1 then i.st_sl end as st_sl,
income_amount,
util_date,
util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
UPDATE1:
As the sample data and explanation changed, another query to comply with the request would be:
select
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_id
end as st_id,
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_date
end as st_date,
case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1
then i.st_sl
end as st_sl,
i.income_amount,
u.util_date,
u.serial_no,
u.util_amount
from income i
left join util u on
i.st_id = u.st_id
and i.st_date = u.st_date
and i.st_sl = u.st_sl;
So, the first three columns get values only for the first rows in the group of std_id, st_sl, util_date.
See a fiddle for demonstration. (also it would be really nice if you give a fiddle with test data in the question next time.)
answered Nov 20 '18 at 11:56
Florin GhitaFlorin Ghita
15.7k44063
edited Nov 23 '18 at 7:43
answered Nov 20 '18 at 11:56
Florin GhitaFlorin Ghita
15.7k44063
answered Nov 20 '18 at 11:56
Florin GhitaFlorin Ghita
15.7k44063
answered Nov 20 '18 at 11:56
Florin GhitaFlorin Ghita
15.7k44063
15.7k44063
If the part income amount is utilized on different days, then the util.serial_no will be 1 on 12/11/2018 & again 1 for 13/11/2018. Ex: See the util table 3rd row, this was happened on the same day (12/11/2018) hence i got the util.serial no as 3. In case this utilization is done on next day, again i'll get the util.serial no as 1 & util_date as 13/11/2018. So above query again gives me a "duplicate" rows!!! Is it possible to get output without hard coding like serial_no?
– Chandramohan Divya
Nov 21 '18 at 4:16
I don't get it. My code gives the desired output for the given input in the question? Do you have another case that my query doesn't match the request? pls edit the question with that edge case. I don't know what serial no and utilization are, I just gave the query for given input.
– Florin Ghita
Nov 21 '18 at 11:52
I've modified the Util table data. Now you can check. The first row from Income table coming twice from your code.
– Chandramohan Divya
Nov 23 '18 at 4:06
Ah, ok, you've changed the test data. I'll come back with a possible query.
– Florin Ghita
Nov 23 '18 at 7:16
Forin Ghita, thanks for answering, It works very well for my requirement.
– Chandramohan Divya
Nov 27 '18 at 4:19
add a comment |
If the part income amount is utilized on different days, then the util.serial_no will be 1 on 12/11/2018 & again 1 for 13/11/2018. Ex: See the util table 3rd row, this was happened on the same day (12/11/2018) hence i got the util.serial no as 3. In case this utilization is done on next day, again i'll get the util.serial no as 1 & util_date as 13/11/2018. So above query again gives me a "duplicate" rows!!! Is it possible to get output without hard coding like serial_no?
– Chandramohan Divya
Nov 21 '18 at 4:16
I don't get it. My code gives the desired output for the given input in the question? Do you have another case that my query doesn't match the request? pls edit the question with that edge case. I don't know what serial no and utilization are, I just gave the query for given input.
– Florin Ghita
Nov 21 '18 at 11:52
I've modified the Util table data. Now you can check. The first row from Income table coming twice from your code.
– Chandramohan Divya
Nov 23 '18 at 4:06
Ah, ok, you've changed the test data. I'll come back with a possible query.
– Florin Ghita
Nov 23 '18 at 7:16
Forin Ghita, thanks for answering, It works very well for my requirement.
– Chandramohan Divya
Nov 27 '18 at 4:19
If the part income amount is utilized on different days, then the util.serial_no will be 1 on 12/11/2018 & again 1 for 13/11/2018. Ex: See the util table 3rd row, this was happened on the same day (12/11/2018) hence i got the util.serial no as 3. In case this utilization is done on next day, again i'll get the util.serial no as 1 & util_date as 13/11/2018. So above query again gives me a "duplicate" rows!!! Is it possible to get output without hard coding like serial_no?
– Chandramohan Divya
Nov 21 '18 at 4:16
If the part income amount is utilized on different days, then the util.serial_no will be 1 on 12/11/2018 & again 1 for 13/11/2018. Ex: See the util table 3rd row, this was happened on the same day (12/11/2018) hence i got the util.serial no as 3. In case this utilization is done on next day, again i'll get the util.serial no as 1 & util_date as 13/11/2018. So above query again gives me a "duplicate" rows!!! Is it possible to get output without hard coding like serial_no?
– Chandramohan Divya
Nov 21 '18 at 4:16
I don't get it. My code gives the desired output for the given input in the question? Do you have another case that my query doesn't match the request? pls edit the question with that edge case. I don't know what serial no and utilization are, I just gave the query for given input.
– Florin Ghita
Nov 21 '18 at 11:52
I don't get it. My code gives the desired output for the given input in the question? Do you have another case that my query doesn't match the request? pls edit the question with that edge case. I don't know what serial no and utilization are, I just gave the query for given input.
– Florin Ghita
Nov 21 '18 at 11:52
I've modified the Util table data. Now you can check. The first row from Income table coming twice from your code.
– Chandramohan Divya
Nov 23 '18 at 4:06
I've modified the Util table data. Now you can check. The first row from Income table coming twice from your code.
– Chandramohan Divya
Nov 23 '18 at 4:06
Ah, ok, you've changed the test data. I'll come back with a possible query.
– Florin Ghita
Nov 23 '18 at 7:16
Ah, ok, you've changed the test data. I'll come back with a possible query.
– Florin Ghita
Nov 23 '18 at 7:16
Forin Ghita, thanks for answering, It works very well for my requirement.
– Chandramohan Divya
Nov 27 '18 at 4:19
Forin Ghita, thanks for answering, It works very well for my requirement.
– Chandramohan Divya
Nov 27 '18 at 4:19
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53389641%2fhow-to-display-rows-in-nested-format-when-matches-multiple-rows-with-linking-tab%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
What did you try so far?
– ETO
Nov 20 '18 at 9:40
can you add the query to the quesion?
– ezain
Nov 20 '18 at 10:21
"I want a single row from table 1 when the linking table has multiple rows matching." Your sample data has three rows for the first matching row. Hence, I don't understand what you are asking for.
– Gordon Linoff
Nov 20 '18 at 12:11
Exactly. The first row of income table is matching with 3 rows in util table. But in the output the income table column should be displayed once & util table values should be displayed in 3 rows. ( Income table values suppressed in 2nd & 3rd rows)
– Chandramohan Divya
Nov 21 '18 at 4:19