CODEWITHSUNDEEP
mysql
mester
mester

Reputation: 25

I want to get not existed data from two tables

I have this MySQL query which links two tables.I want to join theme where to get not existed data from the other table.

this here get the registered date for users

SELECT
t2.name ,phone 
FROM
  (SELECT name,tid,date_d,class_time  AS 'absent' 
      FROM absent where date_d = '2016-12-04' )  t1 
JOIN
  (SELECT  name, id,phone  AS 'phone' 
      FROM users  )  t2
  ON t1.tid = t2.id 
  group by id

I want the users who are not registered in table t1 from table two I used the same above query but with something like this ON t1.tid != t2.idit works only with unduplicated date 

    SELECT
    t2.name ,phone 
    FROM
      (SELECT name,tid,date_d,class_time  AS 'absent' 
          FROM absent where date_d = '2016-12-04' )  t1 
JOIN
      (SELECT  name, id,phone  AS 'phone' 
          FROM users  )  t2
      ON t1.tid != t2.id 
      group by id

Upvotes: 0

Views: 48

Answers (1)

Stephan Lechner
Stephan Lechner

Reputation: 35154

A condition like ON t1.tid != t2.id used in a join will very likely give you a result if either t1 or t2 contain more than one row (because then, for some of the rows in t1 and t2, tid and id will be different). What you are looking for is "those users that have NOT been absent at a specific day, i.e. where no entry in absent-table exists", right?

Try the following:

SELECT name,id,phone AS 'phone' 
FROM users t2
WHERE t2.id not in 
  (SELECT tid  
   FROM absent where date_d = '2016-12-04')

Upvotes: 1

Related Questions