Member Avatar for Sinha's

Hello Experts,

I have following tables:

Table_1:

C_ID Inst_No Paid_Dt
201 2 12/06/2010
202 2 13/06/2010
203 3 12/06/2010
202 3 16/06/2010


Table_2:

C_ID Inst_No Today_Dt
201 2 12/06/2010
202 2 12/06/2010
203 3 12/06/2010
202 3 16/06/2010


Now, from the above two tables I want to display only the records where Paid_Dt and Today_Dt are not equal.

Like the Result should be
202 2 12/06/2010

I tried it by inner join and <> (Not Equal operator) but, it shows all data instead of a particular.

  • 2 Contributors
  • 2 Replies
  • 188 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Sinha's

Dani AI

Generated

Good call by — joining on the two key columns and filtering where the date columns differ is the right idea. A few common pitfalls that explain why an INNER JOIN + <> sometimes "returns everything" or the wrong rows for datasets like :

  • datatype and time-of-day differences: datetime values with different time parts will compare as unequal even if the calendar date is the same.
  • storage as text: dates stored in varchar can have different formats or stray spaces.
  • NULLs: <> excludes NULL comparisons (NULL <> something evaluates to unknown).
  • duplicate key rows: multiple rows per (C_ID, Inst_No) produce extra matched rows.

Typical fixes and checks (keeps the intent of but avoids the pitfalls):

  • Normalize to date-only (SQL Server 2008+):

    SELECT t1.C_ID, t1.Inst_No, t1.Paid_Dt, t2.Today_Dt
FROM dbo.Table_1 AS t1
JOIN dbo.Table_2 AS t2
  ON t1.C_ID = t2.C_ID AND t1.Inst_No = t2.Inst_No
WHERE CAST(t1.Paid_Dt AS DATE) <> CAST(t2.Today_Dt AS DATE);

  • If on older engines, use DATEDIFF to ignore time:

    WHERE DATEDIFF(day, t1.Paid_Dt, t2.Today_Dt) <> 0

  • To preserve an index on a datetime column, compare ranges instead of wrapping the column in a function:

    WHERE NOT (
  t1.Paid_Dt >= CAST(t2.Today_Dt AS DATETIME)
  AND t1.Paid_Dt < DATEADD(day, 1, CAST(t2.Today_Dt AS DATETIME))
)

  • Include NULL-mismatch cases explicitly if needed:

    WHERE (t1.Paid_Dt IS NULL AND t2.Today_Dt IS NOT NULL)
   OR (t1.Paid_Dt IS NOT NULL AND t2.Today_Dt IS NULL)
   OR CAST(t1.Paid_Dt AS DATE) <> CAST(t2.Today_Dt AS DATE)

Quick troubleshooting checklist: confirm column datatypes in INFORMATION_SCHEMA, check for duplicates with GROUP BY/COUNT, and decide whether the result should show Table_1.Paid_Dt or Table_2.Today_Dt (the sample expected row is ambiguous). Also note the sample answer from omitted commas in the SELECT list — ensure proper syntax when copying queries.

Recommended Answers

All 2 Replies

Member Avatar for rch1231

Hello,

What you need to do is to select your records and do the join with the C_ID and Inst_No and then use the WHERE to select a subset of the data where the fields are not equal. Something like:

SELECT Table_1.C_ID, 
Table_1.Inst_No,  
Table_1.Paid_Dt, 
Table_2.C_ID 
Table_2.Inst_No 
Table_2.Today_Dt
From Table_1 Inner Join Table_2 on Table_1.C_ID = Table_2.C_ID 
AND
Table_1.Inst_No = Table_2.Inst_No
WHERE 
Table_1.Paid_Dt <> Table_2.Today_Dt
Member Avatar for Sinha's

Thanks a lot, My problem is solved. Now I am getting the result what i was looking for.

Regards!!

Hello,

What you need to do is to select your records and do the join with the C_ID and Inst_No and then use the WHERE to select a subset of the data where the fields are not equal. Something like:

SELECT Table_1.C_ID, 
Table_1.Inst_No,  
Table_1.Paid_Dt, 
Table_2.C_ID 
Table_2.Inst_No 
Table_2.Today_Dt
From Table_1 Inner Join Table_2 on Table_1.C_ID = Table_2.C_ID 
AND
Table_1.Inst_No = Table_2.Inst_No
WHERE 
Table_1.Paid_Dt <> Table_2.Today_Dt
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.