SQL Query to retrive the values not present in the other table

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

I have two datatables I want a Linq query which will select all the rows from tables 1 excluding the dates present in the table 2.

Table 1
13/08/2012 Manju
13/08/2012 Vinay
13/08/2012 selva
13/08/2012 natrajan
15/08/2012 Manju
15/08/2012 Vinay
15/08/2012 selva
15/08/2012 leela
15/08/2012 natrajan
20/08/2012 Manju
20/08/2012 Vinay
20/08/2012 selva
20/08/2012 leela
20/08/2012 natrajan
22/08/2012 leela
23/08/2012 leela
24/08/2012 leela
25/08/2012 Manju
25/08/2012 Vinay
25/08/2012 selva
25/08/2012 leela
25/08/2012 natrajan
28/08/2012 Manju
28/08/2012 Vinay
28/08/2012 selva
28/08/2012 leela
28/08/2012 natrajan
29/08/2012 Manju
29/08/2012 Vinay
29/08/2012 selva
29/08/2012 leela
29/08/2012 natrajan

Table 2
ID FDate
2 06/02/2012
3 06/03/2012
4 06/09/2012
5 06/10/2012
6 06/16/2012
7 06/17/2012
8 06/23/2012
9 06/24/2012
10 06/30/2012
11 07/01/2012
12 07/07/2012
13 07/08/2012
14 07/14/2012
15 07/15/2012
16 07/21/2012
17 07/22/2012
18 07/28/2012
19 07/29/2012
20 08/04/2012
21 08/05/2012
22 08/11/2012

Thanks in advance.

Posted 26-Aug-12 20:34pm

BheemG

Updated 29-Aug-12 1:19am

v5

Add a Solution

Comments

Simon_Whale 29-Aug-12 7:03am

apart from the date how are you joining leela's records in table 1 to table 2.

also you have different date formats table one uses dd/MM/yyyy and table 2 uses MM/dd/yyyy

Santhosh Kumar Jayaraman 29-Aug-12 7:08am

Its very hard to understand this question. WHy cant you make it as simple and clear?

Santhosh Kumar Jayaraman 29-Aug-12 7:09am

ALso there is no link between table 1 and table2

3 solutions

Solution 1

This is a very basic question. I would recommend you to try this on your own.
If you are not sure on how joins work in SQL you can refer the below link.

Join fundamentals[^]

SQL SERVER – Introduction to JOINs – Basic of JOINs[^]

if you are new to SQL you can go through some tutorials here[^]

If you still don't get the result, you can post your SQL query and we will be happy to assist you.

Posted 26-Aug-12 20:53pm

__TR__

Updated 26-Aug-12 20:59pm

v2

Comments

BheemG 27-Aug-12 4:24am

I think my question is not properly formulated i am sorry for that. for instance we will take only table 1 and table 3 . we can see the ID 9 Leela from table one has entered only one record in table 3 that is (22 9 08/13/2012) I want her ID with the remaining dates that are not entered using her ID.

__TR__ 27-Aug-12 4:30am

I did not understand "I want her ID with the remaining dates that are not entered using her ID."
What dates are you referring to here?

BheemG 28-Aug-12 0:02am

This is time sheet software i want a query to get the person name from table 1 and the dates they have not entered a record in the table 3. The dates will anyhow not be in the database. The query should return those date in the output.

__TR__ 28-Aug-12 13:19pm

I am still having a hard time understanding the requirement.
Lets take userID = 9 (Leela). This user has a record in Table 3 with Tdate = 08/13/2012.
So what should your query return, in other words how should the result look like?
What is the significance of table 2 ?
If you have attempted something then post your query so we can take a look.

BheemG 29-Aug-12 1:45am

Output should be like

leela 08/13/2012
leela 08/20/2012
leela 08/15/2012
leela 08/24/2012
leela 08/23/2012
leela 08/22/2012

that is the person leela has not entered records on these dates in to the table 3.

__TR__ 29-Aug-12 4:12am

Still have a problem understanding how the dates are selected. I would suggest you to update your question. Others may be able to give you a solution.

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)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-08-29T01:24:00

try this

XML

var q =
    from d in dates
    select d.Field<DateTime>("date");
return
    (from r in records
     where !q.Contains(r.Field<DateTime>("date"))
     select r).CopyToDataTable();

http://stackoverflow.com/questions/2259400/remove-duplicates-by-field-from-one-table-using-another-using-linq[^]

Aarti Meswania · Accepted Answer · 2012-08-29T01:10:00

Solution 2

if you mean to say
you want record from table1 except dates exist in table2 then,
use below Query

SQL

select * from table1 
where date not in (select date from table2)

Happy Coding!
:)

Posted 29-Aug-12 1:10am

Aarti Meswania

Comments

BheemG 29-Aug-12 7:18am

I am aware of SQL Query I need Linq Query not SQL.

Thank you.

Aarti Meswania 29-Aug-12 7:23am

ok, check this link may it be worked
http://gorgando.com/blog/technology/asp-net/not-in-subquery-with-asp-net-linq-in-vb

SQL Query to retrive the values not present in the other table

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us