-1

I am trying to join three tables - one parent table and two children table. However, joining creates multiple combinations appear in result set hence increasing the number of rows exponentially.

parent_operations_table:

id name
1 Operation_101
2 Operation_102

child_task_table:

id name parent_id
1 Task_1_Operation_101 1
2 Task_2_Operation_101 1

child_review_table:

id name parent_id
1 Review_1_Operation_101 1
2 Review_2_Operation_101 1

My desired table is like this

id name task_id review_id
1 Operation_101 1 1
1 Operation_101 2 2
2 Operation_102 null null

And the table which I am getting is like this:

id name task_id review_id
1 Operation_101 1 1
1 Operation_101 1 2
1 Operation_101 2 1
1 Operation_101 2 2
2 Operation_102 null null

Currently I am just joining and not aggregating over anything and selecting distinct, but as we can see every row is actually a distinct row by itself. I am using left joins as we can see for Operation #2 that operations may not have any tasks or reviews defined at some given point of time. I felt using aggregation such as MAX to ignore the nulls is not possible as well, as it will also take max of the child IDs grouping by parent_id. And I don't want to turn it into a comma separated aggregation as well. If anyone has faced this issue and knows a workaround any help would be greatly appreciated. Thanks!

Edit: adding query used - just 2 left joins

SELECT op.id, ts.task_id, rvw.review_id
FROM operations op
LEFT JOIN tasks ts ON op.id=ts.parent_id
LEFT JOIN reviews rvw ON op.id=rvw.parent_id

http://sqlfiddle.com/#!18/4cb2f5/2 - Please find the sample at this link

Someshwar Roychowdhury
  • 29
  • 1
  • 9
  • 1
    What should happen if you have more reviews than tasks or vice versa? Something like http://sqlfiddle.com/#!18/4cb2f5/4/0 gives you your desired output, but I have no idea if that meets your requirements for more complicated data. – EdmCoff Mar 30 '23 at 17:38
  • 1
    Or possibly you need a full join between the second and third tables http://sqlfiddle.com/#!18/4cb2f5/41 – Charlieface Mar 30 '23 at 19:52
  • You don't say how any desired result is a function of input. So we don't know what would be a query to do it except to guess from 1 example. [ask] [Help] PS [What is the difference between “INNER JOIN” and “OUTER JOIN”?](https://stackoverflow.com/a/46091641/3404097) [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/q/12464037/3404097) [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) – philipxy Mar 30 '23 at 22:57
  • Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Mar 30 '23 at 22:58
  • @philipxy Please look at the sqlfiddle for the code! – Someshwar Roychowdhury Apr 01 '23 at 08:22
  • You didn't follow my comments or linked documentation. Please put what is needed to ask your question in your post., not just at a link. PS There is no question in this post. PS And where it seems to be trying to ask it refers to what precedes it but it's not clear what "this issue" is, there's just a long disorganized list of statements. PS Please don't insert "EDIT"s/"UPDATE"s, just make your post the best presentation as of edit time. Please avoid social & meta commentary in posts. [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/q/284236/3404097) – philipxy Apr 01 '23 at 08:35
  • Please ask 1 specific researched non-duplicate question. Please either ask re 1 bad query/function with obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & a [mre]. Then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. [ask] [Help] Basic questions are faqs. – philipxy Apr 01 '23 at 08:40

2 Answers2

-2
SELECT op.operation_id, ts.task_id, rvw.review_id
FROM operations op
LEFT JOIN tasks ts ON op.id=ts.parent_id
LEFT JOIN reviews rvw ON ts.parent_id=rvw.parent_id

In unions you must have on the left side what you know against what you don't know.

SELECT op.operation_id, ts.task_id, rvw.review_id
FROM operations op
LEFT JOIN tasks ts ON op.id=ts.parent_id
LEFT JOIN reviews rvw ON op.id=rvw.parent_id
philipxy
  • 14,867
  • 6
  • 39
  • 83
Jose Donaldo Luna
  • 1
  • 1
  • Because reviews is not dependent on Tasks table at all. There might be 0 tasks but 10 reviews or vice versa. For this sample, even if it works, it doesn't suffice my requirement. Thanks. – Someshwar Roychowdhury Mar 30 '23 at 17:29
  • Please before you publish look at the formatted version of your post. See the edit help & advanced help re block & inline formats for code (etc). PS This does not address how the code answers the question(s) (or what question(s) it thinks it is answering). Repeating or describing code in prose is not helpful, we can read the code, explain why that code was written. Also see my comments on your other answer. PS Why are you talking about unions? What does it have to do with the rest of the post or answering? Also, the sentence is unintelligible. – philipxy Apr 01 '23 at 08:59
-2
SELECT
  op.id
  , op.name
  , rv.id
  , rv.name
  , rv.parent_op
FROM
  operation op
left join reviews rv 
  on op.id=rv.parent_op
left join tasks ts 
  on op.id <> ts.parent_op
group by 
  op.id
  , op.name
  , rv.id
  , rv.name
  , rv.parent_op
philipxy
  • 14,867
  • 6
  • 39
  • 83
Jose Donaldo Luna
  • 1
  • 1
  • 3
    Code-only answers are poor. Explain why the code answers the question. But do not post guesses at unclear posts, comment to ask for clarity when you have enough reputation. [answer] [Help] Please avoid social & meta commentary in posts. – philipxy Apr 01 '23 at 08:48
  • You have posted 2 answers on this question. Did you mean to do that? To edit an answer click on 'edit'. To delete an answer click on 'delete'. But please don't delete & repost variations of the same answer, edit the answer. PS Poorly received posts count towards posting limits, deleted or not. – philipxy Apr 01 '23 at 09:07