Member Avatar for jovillanuev

Guys,

Can you help please to compare or check the records from Mytable1 to MyTable2 if exist or found.
and i have to count the records with not null.
Any idea. you can modify my script.

elect 
	a.ESN
    b.ESN
FROM MyTable1 as a
Left outer join Mytable2 as b
on a.esn = b.esn

Create table MyTable1(ESN varchar(10)
Insert into MyTable1
Value('120')
Value('121')
Value('122')
Value('123')
Value('124')

Create table MyTable2(ESN varchar(10)
Value('120')
Value('121')
Value('122')

The result should be like this:
MyTable
ESN
---------
120--120
121--120
122--120
123--NULL
124--NULL

Thank you guys

Jonel

  • 3 Contributors
  • 2 Replies
  • 98 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by adam_k

Dani AI

Generated

A clear path forward for this task: use a left (outer) join to keep every ESN from MyTable1 and then handle two things explicitly — how each row is displayed when no match exists, and how to count matches versus non‑matches. was right about the join; correctly flagged the NULL/concatenation issue.

For row output, return MyTable1.ESN and a safe representation of the matching value from MyTable2. Do not rely on implicit concatenation behavior — use a null-safe function (for example SQL Server has CONCAT and you can use COALESCE to substitute a literal like 'NULL' or an empty string) so missing matches display as you intend instead of making the whole expression null. If you want a single formatted column you can perform that formatting on the resultset or in the client UI.

To count non‑null matches: aggregate against the joined column. COUNT(joined_column) counts only non‑null values, so it gives the number of successful matches. The number of misses is COUNT(*) - COUNT(joined_column) or use SUM(CASE WHEN joined_column IS NULL THEN 1 ELSE 0 END) for the unmatched count. If you need per‑ESN counts (to detect duplicates in MyTable2), group by the MyTable1 key.

Troubleshooting and performance tips: ensure the ESN columns share the same data type and collation and are trimmed of stray spaces; otherwise equality can fail. If MyTable2 can contain duplicates, consider joining to a distinct list or aggregating first. Index the ESN columns for faster joins. For existence-only checks at scale, EXISTS/NOT EXISTS patterns can be more efficient than joins.

Recommended Answers

All 2 Replies

Member Avatar for debasisdas

Outer join should work for you.

Member Avatar for adam_k

Do you want to count as part of the same select ? Your example result doesn't count.
Anyway, you can count using an outer join.

Since you already have the join written, I'm guessing that you want the results to be in 1 field (like your example result). Then do a select like this:

select table1.ESN + '--' + table2.ESN 
from table1 ....

If you are having problems with NULL (which in the above example will null the whole line) then either use isnull() or SET CONCAT_NULL_YIELDS_NULL OFF (keep in ming that the SET CONCAT_NULL_YIELDS_NULL is being phased out)

Edited by adam_k because: n/a
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.