How to get missing values in sql?

Question

I need help.

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

t2 has fields:

idFromt3 idFromt1 Value

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.

Answer 1

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
        )