CODEWITHSUNDEEP
sqlpostgresqljoin
Abu Sulaiman
Abu Sulaiman

Reputation: 1507

SQL Query joining two tables similar to left outer join

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

Upvotes: 2

Views: 46

Answers (1)

The Impaler
The Impaler

Reputation: 48770

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);

Upvotes: 2

Related Questions