Reputation: 4818
I have two table. One table contains graduation records and the second table contains post graduation records. A candidate must have graduation, but it is not necessarily to have post graduation.
My question is to select the post graduation record if the candidate has post graduation else only graduation.
table 1 graduation_table
rollno | degree | division
--------------------------
001 | B.tech | 1st
002 | B.sc | 1st
003 | BA | 1st
table 2 postgraduation_table
rollno | degree | division
--------------------------
002 | M.sc | 1st
the result must be
rollno | degree | division
--------------------------
001 | B.tech | 1st
002 | M.sc | 1st
003 | BA | 1st
Upvotes: 0
Views: 51
Reputation: 125574
select
rollno,
case when p.degree is null then g.degree else p.degree end as degree,
case when p.division is null then g.division else p.division end as division
from
grad g
left join
post p using (rollno)
Or better as suggested in the comments:
select
rollno,
coalesce (p.degree, g.degree) as degree,
coalesce (p.division, g.division) as division
from
grad g
left join
post p using (rollno)
Upvotes: 1
Reputation:
You want all rows from graduation_table
which do not have a row in postgraduation_table
plus those in postgraduation_table
. This can be expressed with a not exists
and union
query:
select gt.rollno, gt.degree, gt.division
from graduation_table gt
where not exists (select *
from postgraduation_table pg
where pg.rollno = gt.rollno)
union all
select rollno, degree, division
from postgraduation_table
order by rollno;
Online example: http://rextester.com/IFCQR67320
Upvotes: 1
Reputation: 798
This should make your needs :
SELECT dg.rollno, CASE WHEN pg IS NOT NULL THEN pg.degree ELSE gd.degree END AS degree, dg.division
FROM graduation_table AS dg
LEFT OUTER JOIN postgraduation_table AS pg USING (rollno)
GROUP BY dg.rollno, dg.division;
Hope this help.
Upvotes: 0
Reputation: 522817
Take a union of both tables, and introduce a position column, to rank the relative importance of the two tables. The postgraduate table has a pos
value of 1, and the graduate table has a value of 2. Then, apply ROW_NUMBER()
over this union query and assign a row number to each rollno
group of records (presumed to be either one or at most two records). Finally, perform one more outer subquery to retain the most important record, postgraduate first, graduate second.
SELECT rollno, degree, division
FROM
(
SELECT
rollno, degree, division,
ROW_NUMBER() OVER (PARTITION BY rollno ORDER BY pos) rn
FROM
(
SELECT p.*, 1 AS pos p FROM postgraduation_table
UNION ALL
SELECT p.*, 2 FROM graduation_table p
) t
) t
WHERE t.rn = 1;
Upvotes: 0