How to get missing values in sql?

I need help.

I have a sql table t2 related to two other tables t1 and t3.

t2 has fields:

idFromt3 idFromt1 Value

开发者_StackOverflow中文版1        14        text1
2        14        text2
1        44        text1
2        44        text2
3        44        text3

I'm searching for values, where ifFromt3 is missing. I want to fint in this example, the value ifFromt3 = 3, because of it's not present.

I'm doing it like this example, but it doesn't work correctly.

SELECT t3.idFromt3, t3.idFromt1
FROM t3 
  INNER JOIN t2 
    ON t3.LanguageMessageCodeID <> t2.idFromt2

This are the 3 tables.

CREATE TABLE [dbo].[t3](
    [t3ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
)

CREATE TABLE [dbo].[t2](
    [t2ID] [int] IDENTITY(1,1) NOT NULL,
    [t3ID] [int] NOT NULL,
    [t1ID] [int] NOT NULL,
)


CREATE TABLE [dbo].[t1](
    [t1ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
)

UPDATE:

Tables with data: http://www.2shared.com/photo/40yY6FC-/Untitled.html

And I need a query, that returns all missing combinations in table LangugageMessageCodes.

In this case:

LanguageMessageCodeID  LanguageID
3                      14
1                      47
2                      47
3                      47

please. help.

regards.

SELECT  *
FROM    t2
WHERE   t2.idFromt3 NOT IN
        (
        SELECT  LanguageMessageCodeID
        FROM    t3
        )

or

SELECT  *
FROM    t2
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    t3
        WHERE   t3.LanguageMessageCodeID = t2.id
        )

or

SELECT  t2.*
FROM    t2
LEFT JOIN
        t3
ON      t3.LanguageMessageCodeID = t2.id
WHERE   t3.LanguageMessageCodeID IS NULL

Update:

Try this:

SET NOCOUNT ON
DECLARE @t1 TABLE (id INT NOT NULL PRIMARY KEY)
DECLARE @t2 TABLE (t3id INT NOT NULL, t1id INT NOT NULL, PRIMARY KEY (t1id, t3id))
DECLARE @t3 TABLE (id INT NOT NULL)

INSERT
INTO    @t1
VALUES  (14)
INSERT
INTO    @t1
VALUES  (44)

INSERT
INTO    @t2
VALUES  (1, 14)
INSERT
INTO    @t2
VALUES  (2, 14)
INSERT
INTO    @t2
VALUES  (1, 44)
INSERT
INTO    @t2
VALUES  (2, 44)
INSERT
INTO    @t2
VALUES  (3, 44)

INSERT
INTO    @t3
VALUES  (1)
INSERT
INTO    @t3
VALUES  (2)
INSERT
INTO    @t3
VALUES  (3)

SELECT  t1.id, t3.id
FROM    @t1 t1
CROSS JOIN
        @t3 t3
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    @t2 t2
        WHERE   t2.t1id = t1.id
                AND t2.t3id = t3.id
        )

You need to use a LEFT OUTER JOINs if you want to find rows that do not exist in the join table.

An outer join will result in all rows of the left table, whether there is a match on the right one or not.

In order to filter by those that do not exist, you can add a WHERE clause checking for NULL values for the right table.

SELECT t3.idFromt3, t3.idFromt1
FROM t3 
  LEFT OUTER JOIN t2 
    ON t3.LanguageMessageCodeID <> t2.idFromt2
WHERE t2.idFromts IS NULL

Solved this issue by going this approach:

WITH Nums
AS
(
    SELECT 
        1 AS Number

    UNION ALL

    SELECT 
        Number + 1
    FROM
        Nums
    WHERE 
        Number < 99 --- Max number for Transactions it will count up to
), AccountLog (Account, TransNumber, NextTransNumber)
AS
(  
    SELECT   --- Let's Generate some Sample Data
    Account,  --Pretend an Account with Transactions
    TransNumber,  --- Transaction Numbers which should be in sequence
    LEAD(TransNumber,1,0) OVER (PARTITION BY Account ORDER BY TransNumber) NextCheckNumber
    FROM (VALUES(1,2),
    (100,1),---Pretend first column is the Account and then the Transaction Number (Which was Meant to be in Sequence)
    (100,3),---As you can see we're missing numbers in sequence (1,3,7 and not 1,2,3,4,5,6,7)
    (100,7),
    (100,10),
    (200,1),
    (200,3),
    (200,11),
    (200,15)) a(Account,TransNumber)
),
MissingTrans (Account,TransNumber,TransDifference)
AS(
    SELECT 
    Account,
    TransNumber,
    NextTransNumber-TransNumber as TransDifference
    FROM AccountLog
    WHERE 
    NextTransNumber<>0
    AND
    NextTransNumber-TransNumber<>1
)
SELECT 
Account,
TransNumber+al.Number AS MissingTransNumber
FROM MissingTrans mt
CROSS JOIN Nums al
WHERE 
al.Number<mt.TransDifference
AND
mt.TransNumber+al.Number>1