mysql query returns erroneous results

Question

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.

But JID = 3 is present in the third table. Do you mean to return only JID = 2? — Michael Berkowski
– Michael Berkowski, Commented Oct 28, 2013 at 15:04
Yes.return details with JID 1 and 2.Because 1 and 2 JID is not present in tableC against tableC. — Ravikanth
– Ravikanth, Commented Oct 28, 2013 at 15:21

Strawberry · Accepted Answer · 2013-10-28 15:34:12Z

1

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;

edited Oct 28, 2013 at 15:34

answered Oct 28, 2013 at 15:05

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Berkowski Over a year ago

It should, but the first query works correctly as written. It's the second query the OP is questioning...

Strawberry Over a year ago

Yes - but it you write out the queries properly, then the whole thing becomes a lot simpler...

Ravikanth Over a year ago

I thought of giving the queries which was working.Will keep in mind the next time I post a question.Its working.Thanks alot :)

Collectives™ on Stack Overflow

mysql query returns erroneous results

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related