Finding Unmatched Records with SQL

Finding unmatched records with SQL

SELECT
    *
FROM
    Table2 T2
WHERE
    NOT EXISTS (SELECT *
        FROM
           Table1 T1
        WHERE
           T1.State = T2.State AND
           T1.Product = T2.Product AND
           T1.Distributor = 'X')

This should be ANSI compliant.

How to find unmatched records in a single table?

This is a very common type of query. You can try aggregating over the ID values in your table using GROUP BY, then retaining those ID which appear only once.

SELECT ID
FROM yourTable
GROUP BY ID
HAVING COUNT(*) = 1

If you also want to return the entire records for those ID occurring only once, you could try this:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT ID FROM yourTable GROUP BY ID HAVING COUNT(*) = 1
) t2
    ON t1.ID = t2.ID

Finding unmatched records in mysql having 3 tables

As per your description I think below solution work for you. here is the sqlfiddle

SELECT 
    fname,
    lname,
    phone,
    email
FROM student s

JOIN pay_confirm pc
on s.s_id = pc.s_id

WHERE status = 1
and NOT EXISTS (select 
                  s_id 
                from company_name c
                where c.s_id = pc.s_id
               )

Output:

*------------------------------------------*
| fname lname      phone      email        |
*------------------------------------------*
| clay   jen    4534566677  clay@gmail.com |
*------------------------------------------*

Getting result with unmatched records as NULL

This is more likely a job for PIVOT:

;with cteData as (
    SELECT t.datetime, t.TagName, t.value
    FROM [INSQL].[Runtime].[dbo].[History] t
    WHERE t.datetime>=@StartDate and t.datetime<=@EndDate
    AND t.TagName IN ('Weightdata2.uiID', 'Weightdata2.uiWater')
)
SELECT 
  d.dtCreated,
  NULLIF(p.[Weightdata2.uiID], 0) as uiID,
  p.[Weightdata2.uiWater] as uiWater
FROM (
  SELECT 
    cast(d.datetime as datetime2(0)) as dtCreated,
    d.TagName, 
    d.value
  FROM cteData d
) d
PIVOT (
  MAX(d.value) for d.TagName in ([Weightdata2.uiID], [Weightdata2.uiWater])
) p

Which will return data in all cases: when there is uiID row but no uiWater, when both exist, when no uiID but uiWater is present.

And is easily adjusted for longer tag list.

How to join tables selecting both matched and unmatched records?

I think you need a full join and coalesce:

SELECT COALESCE(t1.pl, t2.pl) AS pl
    , COALESCE(t1.sta, t2.sta) AS sta
    , COALESCE(t1.count_1, 0) AS count_1
    , COALESCE(t2.count_2, 0) AS count_2
FROM t1
FULL JOIN t2 ON t1.pl = t2.pl AND COALESCE(t1.sta, -99) = COALESCE(t2.sta, -99)

In order to account for cases where sta is NULL, replace sta with -99 or some other value that is otherwise invalid.

---

Related Topics