SQL Query joining two tables similar to left outer join

Question

Table 1: question

id  |  question
----------------
1   |  Name
2   |  Age
3   |  Gender
4   |  Position

Table 2: answer

qid  |  ans  |  record
-----------------------
1    |  Jay  |  1 
2    |  24   |  1 
3    |  M    |  1 
2    |  23   |  2

I want to come up with a join query that results in the following table:

record  |  question  |  ans
-----------------------------
1       |  Name      |  Jay
1       |  Age       |  24
1       |  Gender    |  M
1       |  Position  |  null
2       |  Name      |  null
2       |  Age       |  23
2       |  Gender    |  null
2       |  Position  |  null

The closest I can come up with is this join:

select a.record, q.question, a.ans 
from 
question q left outer join answer a
on q.id = a.qid order by a.record,q.id;

However, this query only produces this, but I want all the questions to show twice

record  |  question  |  ans
-----------------------------
1       |  Name      |  Jay
1       |  Age       |  24
1       |  Gender    |  M
1       |  Position  |  null
2       |  Age       |  23

I think you are searching for Oracle Partitioned Outer JOIN equivalent - Fill in missing rows when aggregating over multiple fields in Postgres - just skip the aggregation part — Lukasz Szozda
– Lukasz Szozda, Commented Jul 26, 2019 at 19:41

The Impaler · Accepted Answer · 2019-07-26 19:51:27Z

2

You need a cross join to produce all the combinations you need, paired with a left join to retrieve the answers, as in:

select
  r.record,
  q.question,
  a.ans
from question q
cross join (select distinct record from answer) r
left join answer a on a.record = r.record and a.qid = q.id
order by r.record, q.id

Result:

record  question  ans   
------  --------  ------
1       Name      Jay   
1       Age       24    
1       Gender    M     
1       Position  <null>
2       Name      <null>
2       Age       23    
2       Gender    <null>
2       Position  <null>

For reference, this is the test script I used to verify the case:

create table question (
  id int,
  question varchar(10)
);

insert into question (id, question) values
  (1, 'Name'), 
  (2, 'Age'),
  (3, 'Gender'),
  (4, 'Position');

create table answer (
  qid int,
  ans varchar(10),
  record int
);

insert into answer (qid, ans, record) values
  (1, 'Jay', 1),
  (2, '24', 1),
  (3, 'M', 1),
  (2, '23', 2);

edited Jul 26, 2019 at 19:51

answered Jul 26, 2019 at 19:40

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Abu Sulaiman Over a year ago

This worked perfectly and is exactly what I needed. Thanks so much.

Collectives™ on Stack Overflow

SQL Query joining two tables similar to left outer join

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related