query to get missing date from tables

Question

1.00/5 (1 vote)

See more:

hi all..
i have two tables like one is:

HTML

s.no  name     
1      sam
2      jhon
3      sonu
4      rams

another table like

VB

name    date
sam     1/12/2011
jhon    1/13/2011
rams    1/13/2012
sonu    1/14/2012
rams    1/15/2012

when i giving from date as 1/11/2012 and to date as 1/15/2012 and name as `rams`
Here i need to get result as (the name,date witch is not there in second table)

HTML

rams  1/11/2012
rams  1/12/2012
rams  1/14/2012

i was tried like

SQL

SELECT name FROM tbl1  WHERE (name NOT IN (select name from tbl2 where (` date` between '" + d1 + "' AND '"+ d2 +"'))) order by name;

this returns names but not display date missing in second table
can any one please help me for this much complex query..
thanks in advance..

Posted 18-Jun-12 8:52am

tulasiram3975

Updated 18-Jun-12 9:20am

v5

Add a Solution

2 solutions

Solution 2

I think u want to display the name in first table which does not have specified date in table2 if so...

try this..

SQL

select name from First where Name not in (

select name from Second where date  between '2012-01-10' and '2012-01-14') ;

Posted 20-Jun-12 19:39pm

dharan1990

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

idle63 · Accepted Answer · 2012-06-18T09:29:00

Solution 1

Try this, pseudo SQL only:

SQL

SELECT
   tbl1.name,
   tbl2.date
FROM tbl1
LEFT JOIN tbl2 ON tbl1.name = tbl2.name
WHERE tbl2.date NOT between d1  AND d2 order by tbl1.name;

Posted 18-Jun-12 9:29am

idle63

Comments

tulasiram3975 18-Jun-12 22:25pm

sir it returns empty result set...

[no name] 19-Jun-12 1:53am

between or NOT between ...?

tulasiram3975 19-Jun-12 2:53am

both sir..
it wont giving dates i think i need to use mysql case condition

[no name] 19-Jun-12 3:47am

Try it first witout WHERE and then step by step