CODEWITHSUNDEEP
mysql
Vikash Tanwar
Vikash Tanwar

Reputation: 1

get distinct value from two table in mysql

Blockquote table homework

sr_no  homework_date  homework
1        2019-02-01   essay
2        2019-02-03   some stuff
2        2019-02-06   some stuff

table classwork

sr_no  classwork_date  classwork
1        2019-02-01   essay
1        2019-02-02   essay
2        2019-02-04   some stuff
2        2019-02-05  some stuff

result will be like this date

2019-02-01
2019-02-02
2019-02-03
2019-02-04
2019-02-05
2019-02-06

Upvotes: 0

Views: 37

Answers (4)

Guss
Guss

Reputation: 32355

If I understand correctly (and explaining your questions in more words would have probably helper) What you are looking for is to retrieve the date columns from both tables and show them order by date.

A classic solution to retrieve data from two or more tables is the UNION operator. The UNION operator by itself will not complete the task because you also want the results order by date. You can add an ORDER BY clause to the last SELECT, using the first table field name, because UNION will fit the data from the column of the additional table into the correct column in the initial result set (by specification order), so you can just sort on the column names from the first select, and it will sort the entire result set.

UNION does eliminate duplicate results, so you should not need the DISTINCT keyword in your select query.

Upvotes: 0

Palak Jadav
Palak Jadav

Reputation: 1294

try this

SELECT DISTINCT
homework_date
FROM
homework 
UNION 
SELECT DISTINCT
classwork_date
FROM
classwork
ORDER BY homework_date;

Upvotes: 0

jithin giri
jithin giri

Reputation: 743

Try this

 SELECT 
    homework_date
FROM
    homework 
UNION SELECT 
    classwork_date
FROM
    classwork
ORDER BY homework_date;

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try below using left and right join and merge two dataset by UNION

select coalesce(classwork_date,homework_date) as date
from classwork left join homework on classwork_date=homework_date
union 
select coalesce(classwork_date,homework_date)
from classwork right join homework on classwork_date=homework_date

Upvotes: 1

Related Questions