SQL for reporting on missing rows

Question

Post reply

SQL for reporting on missing rows

Alergy

Mr or Mrs. 500

Points: 508
More actions
October 17, 2007 at 4:37 am

#62473

I should be able to do this simply, but my head is really hurting today.
Say I have two tables:
[font="Courier New"]
Table A :: MYCARS
CARCOLOUR
==================================
Ferrari Red
FerrariSilver
PorscheWhite
PorscheRed
Lambo Yellow
Table B :: MASTERCOLOURS
COLOUR
===============
Red
Silver
White
Yellow
[/font]
If Table B lists all possible colours, how can I find out which car-colour combinations are not present in Table A.
The result set I would expect to see would be:
[font="Courier New"]
Result :: COLOURANDCARDONOTMATCH
CARCOLOUR
==================================
Ferrari White
FerrariYellow
PorscheSilver
PorscheYellow
Lambo Red
Lambo Silver
Lambo White
[/font]
Sorry if this is a simple thing - I'm certain it is, but I keep coming up with the wrong result.
Many thanks in advance,
Ali

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Here is what i got...

I think there's still some room for improvement....:D:D

IF OBJECT_ID( 'tempdb..#Cars' ) IS NOT NULL

DROP TABLE #Cars

IF OBJECT_ID( 'tempdb..#Colours' ) IS NOT NULL

DROP TABLE #Colours

CREATE TABLE #Cars( Car VARCHAR(50) NOT NULL, Colour VARCHAR(10) NOT NULL )

CREATE TABLE #Colours( Colour VARCHAR(10) NOT NULL )

INSERT #Cars( Car, Colour )

SELECT 'Ferrari', 'Red'

UNION ALL

SELECT'Ferrari', 'Silver'

UNION ALL

SELECT'Porsche', 'White'

UNION ALL

SELECT'Porsche', 'Red'

UNION ALL

SELECT'Lambo', 'Yellow'

INSERT #Colours( Colour )

SELECT 'Red'

UNION ALL

SELECT 'Silver'

UNION ALL

SELECT 'White'

UNION ALL

SELECT 'Yellow'

SELECTC1.*

FROM(

SELECTDISTINCT Car.Car, Col.Colour

FROM#Cars Car

CROSS JOIN #Colours Col

) C1

LEFT JOIN #Cars C2 ON C1.Car = C2.Car AND C1.Colour = C2.Colour

WHEREC2.Colour IS NULL

ORDER BY C1.Car

IF OBJECT_ID( 'tempdb..#Cars' ) IS NOT NULL

DROP TABLE #Cars

IF OBJECT_ID( 'tempdb..#Colours' ) IS NOT NULL

DROP TABLE #Colours

--Ramesh

Alergy Mr or Mrs. 500 Points: 508 More actions · Answer 2

Ramesh - thank you, that is exactly what I wanted! Many thanks - I've not come across CROSS JOIN before :unsure: - I'd always assumed that there was a plain OUTER JOIN (which there's not of course), and CROSS is the answer to my questions!

Ali