-1

I need to select all rows from table 1 and join data from table 2 by t1_id=t1id (with filtering by year, for example - 2020, and sorting by data, for example ASC), but if there is no corresponding row in table 2, then set some default value (for example - null). Desired result:

t1id title t2id t1_id year data
4 DDD 103 4 2020 142
1 AAA 101 1 2020 574
2 BBB NULL NULL NULL NULL
3 CCC NULL NULL NULL NULL

When I tried to use some kind of JOIN the rows BBB and CCC are removed from the results:

create table table1 (t1id integer, title varchar(100));
insert into table1 (t1id, title) values (1, 'AAA');
insert into table1 (t1id, title) values (2, 'BBB');
insert into table1 (t1id, title) values (3, 'CCC');
insert into table1 (t1id, title) values (4, 'DDD');

create table table2 (t2id integer, t1_id integer, year integer, data integer);
insert into table2 (t2id, t1_id, year, data) values (100, 1, 2019, 333);
insert into table2 (t2id, t1_id, year, data) values (101, 1, 2020, 574);
insert into table2 (t2id, t1_id, year, data) values (102, 3, 2019, 235);
insert into table2 (t2id, t1_id, year, data) values (103, 4, 2020, 142);

SELECT * FROM table1 
LEFT OUTER JOIN table2 ON table2.t1_id=table1.t1id 
WHERE table2.year=2020 
ORDER BY table2.data ASC

Example in sqlfiddle - http://sqlfiddle.com/#!9/ba99b1/1/0

The result of this code is:

t1id title t2id t1_id year data
4 DDD 103 4 2020 142
1 AAA 101 1 2020 574

What should I add to the code to get desired result (with rows BBB and CCC too)?

philipxy
  • 14,416
  • 5
  • 32
  • 77
Sergey
  • 1
  • 1
  • 2
    Please add the SQL that you used. because that' more clear than your description (and avoids _guessing_ ) But this seems to be a simple LEFT OUTER JOIN ? – Luuk May 22 '22 at 07:43
  • There is no question in this post. PS This is not a code writing service. Either give a [mre] re bad code whose result you don't expect or show what parts of your overall goal you can do & explain re being stuck getting further to your goal. [ask] [Help] Research before considering asking & reflect research in a question. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) – philipxy May 22 '22 at 07:46
  • 1
    Sin you did put some effort in properly formatting your question (), you might also take some time, and add a properly formatted SQL statement . – Luuk May 22 '22 at 07:48
  • 1
    A [mre] includes cut & paste & runnable code & example input; 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 May 22 '22 at 07:49
  • Duplicate of [Left Join With Where Clause](https://stackoverflow.com/q/4752455/3404097) PS LEFT JOIN returns INNER JOIN rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After a LEFT JOIN a WHERE, INNER JOIN or HAVING that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS This is a faq. But one must write many clear, concise & precise phrasings of one's question/problem/goal to search. – philipxy May 22 '22 at 08:16
  • Please use text, not images/links, for text--including tables & ERDs. Put text in your post, not just at a link. But a MRE is not just text & it includes input as code & it is minimal, not just a lot of code that is wrong--per my MRE comment. Please act on everything in the comments. PS See [How do comment replies work?](https://meta.stackexchange.com/q/43019/266284) to learn to use `@x` to notify one non-sole non-poster commenter `x` re a comment. PS Re your unexpected result, I just gave you a Q&A & comment explaining it. – philipxy May 22 '22 at 08:46
  • "All records from table1 will be in the result, and only the records from table2 that meet the condition in the ON-clause." see: http://sqlfiddle.com/#!9/ba99b1/8 – Luuk May 22 '22 at 09:21
  • and.... Welcome to stackoverflow ! – Luuk May 22 '22 at 09:28
  • @philipxy Thank you! And I'll tried to be more correct in the future! – Sergey May 22 '22 at 09:33

0 Answers0