need a sql query to select the date from another table

Question

I have quite a bit of knowledge about SQL queries.

I'm trying to get dates, and I have 2 tables ohrm_leave and ohrm holiday. i need to select the dates of ohrm_leave which are present in ohrm_holiday. if its present then msg will show "Its present".

ohrm_leave http://s22.postimg.org/5wfklw6gt/ohrm_leave.jpg

ohrm_holiday http://s10.postimg.org/fs255n5ah/ohrm_holiday.jpg

Answer 1

You could INNER JOIN both tables on the 'date' column. This will return joined rows where the date exists in both tables. Afterwards, you could select the date column from this 'new' table and iterate over it.

Answer 2

You can use simply JOIN, or WHERE

SELECT "it's present" FROM t1 JOIN t2 USING(`date`).

this JOIN is type of inner join which requires values to be present in both tables that are joined.

more on http://dev.mysql.com/doc/refman/5.0/en/join.html

if you want to have null values you can use LEFT JOIN

Answer 3

Try this:

SELECT
  l.date,
  IF(h.date IS NULL , 'Its not present','Its present')
FROM 
  ohrm_leave as l
LEFT JOIN ohrm_holiday as h
  ON l.date=h.date

Answer 4

SELECT 
  d1.date 
FROM 
  ohrm_leave as d1 
INNER JOIN  ohrm_holiday as d2 
  ON D1.date=d2.date

Answer 5

Try

SELECT date FROM ohrm_leave
INTERSECT
SELECT date FROM ohrm_holiday;