Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query

Author

Topic

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2007-06-07 : 08:48:26

Hi,

I have a table as follows:

Column A | Column B | Column C
------------------------------
CityA RegionA CarA
CityA RegionA CarB
CityA RegionA CarC
CityB RegionB CarD
CityB RegionB CarE
CityB RegionB CarF
CityC Null CarG
CityC Null CarH
CityD RegionA CarA

etc....

I also have a table as follows:

Column B | Column C
---------------------
RegionA CarA
RegionA CarB
RegionA CarC
RegionA CarD

etc...

I am trying to output all the records from the first table where a ColumnC value is missing from the second Table

e.g. In the above example I want the following record to be output
CityA RegionA CarD "Removed"

since CityA in the first table has a RegionA but does not have a CarD despite the fact that CarD exists in the second table fro RegionA.

I have been trying all sorts of things but keep getting into a knot.

Thanks in advance,
Kabir

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-07 : 08:51:46

[code]
select t1.ColumnA, t1.ColumnB, t1.ColumnC, status = 'Removed'
from table1 left join table2
on t1.ColumnB = t2.ColumnB
and t1.ColumnC = t2.ColumnC
where t2.ColumnB is null
[/code]

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2007-06-07 : 09:41:42

Thanks for that.

It doesn't actually work as it didnt pick up CityA, RegionA, CarD.

I believe the left join didn't work as its using 2 columns for the join.

Do you have any other ideas?

PS. I ran the following scripts:
-----------------------------------------------

CREATE TABLE table1
(
COL1 VARCHAR(255)
,COL2 VARCHAR(255)
,COL3 VARCHAR(255)
)

INSERT INTO table1
SELECT 'CityA', 'RegionA', 'CarA'
UNION
SELECT 'CityA', 'RegionA', 'CarB'
UNION
SELECT 'CityA', 'RegionA', 'CarC'
UNION
SELECT 'CityB', 'RegionB', 'CarD'
UNION
SELECT 'CityB', 'RegionB', 'CarE'
UNION
SELECT 'CityB', 'RegionB', 'CarF'
UNION
SELECT 'CityC', NULL, 'CarG'
UNION
SELECT 'CityC', NULL, 'CarH'
UNION
SELECT 'CityD', 'RegionA', 'CarA'

CREATE TABLE table2
(
COL2 VARCHAR(255)
,COL3 VARCHAR(255)
)

Insert into table2
select 'RegionA', 'CarA'
union
select 'RegionA', 'CarB'
union
select 'RegionA', 'CarC'
union
select 'RegionA', 'CarD'

select t1.Col1, t1.Col2, t1.Col3, status = 'Removed'
from table1 t1 left join table2 t2
on t1.Col2 = t2.Col2
and t1.Col3 = t2.Col3
where t2.Col2 is null

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-07 : 10:47:25

The left join is the wrong way round. Also, to get the city, a derived table will be needed.

-- *** Test Data ***
DECLARE @t1 TABLE
(
    City varchar(255) NOT NULL
    ,Region varchar(255) NULL
    ,Car varchar(255) NOT NULL
)
INSERT INTO @t1
SELECT 'CityA', 'RegionA', 'CarA' UNION ALL
SELECT 'CityA', 'RegionA', 'CarB' UNION ALL
SELECT 'CityA', 'RegionA', 'CarC' UNION ALL
SELECT 'CityB', 'RegionB', 'CarD' UNION ALL
SELECT 'CityB', 'RegionB', 'CarE' UNION ALL
SELECT 'CityB', 'RegionB', 'CarF' UNION ALL
SELECT 'CityC', NULL, 'CarG' UNION ALL
SELECT 'CityC', NULL, 'CarH' UNION ALL
SELECT 'CityD', 'RegionA', 'CarA'

DECLARE @t2 TABLE
(
    Region varchar(255) NOT NULL
    ,Car varchar(255) NOT NULL

)
INSERT INTO @t2
SELECT 'RegionA', 'CarA' UNION ALL
SELECT 'RegionA', 'CarB' UNION ALL
SELECT 'RegionA', 'CarC' UNION ALL
SELECT 'RegionA', 'CarD'
-- *** End Test Data ***

SELECT D.City, T2.Region, T2.Car, 'Removed' AS Status
FROM @t2 T2
    JOIN (
            SELECT T3.Region, MIN(T3.City) AS City
            FROM @t1 T3
            GROUP BY T3.Region
        ) D
        ON T2.Region = D.Region
    LEFT JOIN @t1 T1
        ON T2.Region = T1.Region
            AND T2.Car = T1.Car
WHERE T1.City IS NULL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-07 : 10:53:32

try


SELECT	A.COL1, A.COL2, T2.COL3
FROM
(
	SELECT	COL1, COL2
	FROM	TABLE1
	GROUP BY COL1, COL2
) A INNER JOIN TABLE2 T2
ON	A.COL2	= T2.COL2
LEFT JOIN TABLE1 T1
ON	A.COL1	= T1.COL1
AND	A.COL2	= T1.COL2
AND	T2.COL3	= T1.COL3
WHERE	T1.COL3	IS NULL
ORDER BY A.COL1, A.COL2, T2.COL3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources