CODEWITHSUNDEEP
mysqlsql
Ravikanth
Ravikanth

Reputation: 83

mysql query returns erroneous results

I have three tables namely A,B and C.

Schema and Values for Table A:

UID CITY      NAME
0   Bangalore UserA
1   Hyderabad UserB

Schema and values for table B:

UID JID DETAILS 
0    1  Some Text
0    2  Some Text
1    3  Some Text

Schema and values for table C:

UID JID Applied
0    3   Yes
1    1   Yes

Now,I need to get the details of all the three tables such that UID=0 and retreive the JID from the third table C.

I have written a below mysql query to do so:

select
  a.uid,
  a.city,
  a.name,
  b.jid,
  b.details
from 
  tableB b
  INNER JOIN tableA a on b.uid=a.uid
where
  jid in(
    select c.jid
    from tableC,tableB 
    where tableC.uid=0 and tableC.jid=tableB.jid
  );

Which gives me following output:

UID CITY       NAME  DETAILS APPLIED
0   BANGALORE UserA sometext   yes

But now if I want the records which are not present in third tableC,that is in this case JID 2 and 3,I am unable to get the correct result from the below query.

select
  a.uid,
  a.city,
  a.name,
  b.jid,
  b.details
from
  tableB b
  INNER JOIN tableA a on b.uid=a.uid
where jid in(
  select c.jid 
  from tableC,tableB 
  where tableC.uid=0 and tableC.jid!=tableB.jid
);

Basically what I am trying to achieve here is the following scenario.TableA=Users protfolio.TableB=Jobs posted by users.TableC=Users applied for which jobs(identified via JID(JobIDs)).Now a single user can apply for many jobs.Whenever a user applies for job,a entry is made to the third TABLEC which records which user has applied for which job.Now,I have data for applied jobs and I need query for not-applied jobs.

Could you please guide me on where exactly I am going wrong.

Upvotes: 1

Views: 74

Answers (1)

Strawberry
Strawberry

Reputation: 33935

Shouldn't that first query be...

SELECT a.uid
     , a.city
     , a.name
     , b.jid
     , b.details
  FROM tableB b
  JOIN tableA a 
    ON b.uid = a.uid
  JOIN tableC c
    ON c.jid = b.jid 
 WHERE tableC.uid = 0;

and so to the exclusion of c...

SELECT a.uid
     , a.city
     , a.name
     , b.jid
     , b.details
  FROM tableB b
  JOIN tableA a 
    ON b.uid = a.uid
  LEFT
  JOIN tableC c
    ON c.jid = b.jid 
   AND tableC.uid = 0
 WHERE c.jid IS NULL;

Upvotes: 1

Related Questions