in Education by
I have a table containing the last comments posted on the website, and I'd like to join a different table depending on the comment type. Comments Table is similar to this structure: id | type | ressource_id | ---+------+--------------+ 1 | 1 | 10 | 2 | 3 | 7 | 3 | 3 | 12 | 4 | 1 | 22 | 5 | 4 | 22 | 6 | 5 | 23 | News Table: news_id | notes| date | --------+------+--------------+ 10 | | 2015-08-12 | 22 | | 2015-07-12 | Tutorial Table: tuto_id | notes| date | --------+------+--------------+ 7 | | 2015-06-15 | 12 | | 2015-05-14 | ... Similar table for type = 4, 5, 6 Now in order to get specific comments I am doing a left join on the two tables. SELECT co.* FROM Comments co LEFT JOIN News n ON co.id = n.news_id AND co.type = 1 LEFT JOIN Tutorial t ON co.id = t.tuto_id AND co.type = 3 WHERE (co.type IN (1,3)) I am interested in getting the date from the left table. How can I include that column in output list. Result desired: (date from joining table) id | type | ressource_id | date | ---+------+--------------+--------------+ 1 | 1 | 10 | 2015-08-12 | 2 | 3 | 7 | 2015-06-15 | 3 | 3 | 12 | 2015-05-14 | 4 | 1 | 22 | 2015-07-12 | Thanks. JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by
Since you will never get a date from News and Tutorial for the same comment you might go withCOALESCE`: SELECT co.*, COALESCE(n.date,t.date) FROM Comments co LEFT JOIN News n ON co.id = n.news_id AND co.type = 1 LEFT JOIN Tutorial t ON co.id = t.tuto_id AND co.type = 3 WHERE (co.type IN (1,3)) COALESCE will return the first argument that is not null, so if there is a matching news it will return the date from News and if there is no matching news but a matching tutorial it will return the date from Tutorial.

Related questions

0 votes
    I have a table containing the last comments posted on the website, and I'd like to join a ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jul 8, 2022 in Education by JackTerrance
0 votes
    I have a table containing the last comments posted on the website, and I'd like to join a ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jul 3, 2022 in Education by JackTerrance
0 votes
    I have a table containing the last comments posted on the website, and I'd like to join a ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jun 16, 2022 in Education by JackTerrance
0 votes
    Is there any way to convert following SQL statement into LINQ? select ve.EntityID , fin1.FinanceStat as ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 23, 2022 in Education by JackTerrance
0 votes
    I have two tables containing Tasks and Notes, and want to retrieve a list of tasks with the number ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 12, 2022 in Education by JackTerrance
0 votes
    How to achieve Left Excluding JOIN using LINQ? In SQL: SELECT FROM Table_A A LEFT JOIN Table_B B ON ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 27, 2022 in Education by JackTerrance
0 votes
    Here are two data frames : df1 = data.frame(CId = a(1:6), Item = b(rep("TV", 3), rep("Book", 3 ... I do a SQL style select statement Select the correct answer from above options...
asked Jan 20, 2022 in Education by JackTerrance
0 votes
    Which join refers to join records from the right table that have no matching key in the left table are ... , Database Interview Questions and Answers for Freshers and Experience...
asked Oct 11, 2021 in Education by JackTerrance
0 votes
    Which join refers to join records from the write table that have no matching key in the left table ... Join Operations in chapter Query Processing Techniques of Database Management...
asked Oct 10, 2021 in Education by JackTerrance
0 votes
    ____________ category of operators are used to join to simple condition Select the correct answer from above options...
asked Nov 27, 2021 in Education by JackTerrance
0 votes
    Which is a join condition contains an equality operator: (a) Equijoins (b) Cartesian (c) Natural (d ... , Database Interview Questions and Answers for Freshers and Experience...
asked Oct 11, 2021 in Education by JackTerrance
0 votes
    How many join types in join condition: (a) 2 (b) 3 (c) 4 (d) 5 This question was addressed ... and Answers, Database Interview Questions and Answers for Freshers and Experience...
asked Oct 11, 2021 in Education by JackTerrance
0 votes
    Which oracle is the join condition is specified using the WHERE clause: (a) Oracle 9i (b) Oracle ... Answers, Database Interview Questions and Answers for Freshers and Experience...
asked Oct 11, 2021 in Education by JackTerrance
0 votes
    Which are the join types in join condition: (a) Cross join (b) Natural join (c) Join with USING ... Answers, Database Interview Questions and Answers for Freshers and Experience...
asked Oct 11, 2021 in Education by JackTerrance
0 votes
    Which product is returned in a join query have no join condition: (a) Equijoins (b) Cartesian (c) ... Answers, Database Interview Questions and Answers for Freshers and Experience...
asked Oct 11, 2021 in Education by JackTerrance
...