CODEWITHSUNDEEP
mysql
mbaula
mbaula

Reputation: 37

Compare two columns in different tables in MySQL

I have two tables

Table vehicles

regno     make      status

KBT567K   ISUZU     operating
KAD897L   DOGDE     operating
KAT876K   JAGUAR    grounded
KAW564H   FERRARI   operating

Table contributions

regno      amount    timestamp

KBT567K     200      2015-03-24 18:10:13 
KAD897L     100      2015-03-24 12:32:16
KBT567K     150      2015-03-25 11:06:32

I am trying to pull a query where I only get a list of regno from table vehicles that have not contributed on 2015-03-25 while their status is 'operating'

I have tried this query below but doesn't work. Any help will be highly appreciated

select 
  regno
from
  vehicles
where
  regno not in (
    select
      contributions.regno
    from
      contributions,vehicles
    WHERE
      DATE_FORMAT(contributions.timestamp,'%Y-%m-%d') > '2015-03-25' AND 
      DATE_FORMAT(contributions.timestamp,'%Y-%m-%d') > '2015-03-24'
  ) AND
  vehicles.status = 'operating' ORDER BY vehicles.regno asc

Upvotes: 1

Views: 859

Answers (3)

mbaula
mbaula

Reputation: 37

I worked out the solution for this

 Select DISTINCT regno from vehicles where status = 'operating' AND vehicles.regno not in (select regno from contributions WHERE DATE_FORMAT(timestamp,'%Y-%m-%d') > '2015-03-24') and DATE_FORMAT(timestamp,'%Y-%m-%d') != '2015-03-25' ORDER BY regno asc

Thanks all for your time. Cheers

Upvotes: 0

Parag Tyagi
Parag Tyagi

Reputation: 8960

You can use JOIN

select vehicles.regno
from vehicles
LEFT JOIN contributions ON vehicles.regno = contributions.regno
WHERE DATE_FORMAT(contributions.timestamp,'%Y-%m-%d') != '2015-03-25'
AND WHERE vehicles.status = "operating"
ORDER BY vehicles.regno asc

Upvotes: 0

sayani
sayani

Reputation: 148

You can try this SQL:

select DISTINCT regno 
from vehicles
where status = 'operating' AND regno in (select regno from contributions WHERE DATE_FORMAT(timestamp,'%Y-%m-%d') != '2015-03-25') 
ORDER BY regno asc

Upvotes: 1

Related Questions