SQLTeam.com | Weblogs | Forums

Co-relation in SQL


#1

Respected Techie,

Please suggest me how to achieve the result for below scenario.

DECLARE @PRODUCT TABLE
(
[Base Part] VARCHAR (50),
[REP PART] VARCHAR (50),
[Base Line] VARCHAR (50),
[REP LINE] VARCHAR (50)

)

INSERT @PRODUCT

SELECT 'EXT8765', 'EXT8765-35', 'Cold Air Intake', 'TNNEAUCVRL' UNION ALL
SELECT 'EXT40655', 'EXT7205-1', 'Cold Air Intake', 'TONORLCRNR' UNION ALL
SELECT 'AFE54-11602','AFE24-60505', 'Cold Air Intake', 'STEERING' UNION ALL
SELECT 'UNDUC4070', 'UNDRSIH1212GS','Cold Air Intake', 'Air Filter Big' UNION ALL
SELECT 'EXT44720', 'EXT43720B', 'Cold Air Intake', 'Air Filter Small'

SELECT * FROM @PRODUCT

DECLARE @DRIVER TABLE
(
[Base Line] VARCHAR (50),
[REP LINE] VARCHAR (50)
)
INSERT @DRIVER

SELECT 'Cold Air Intake','Air Filter%'

SELECT * FROM @DRIVER

I NEED TO CHECK the below CONDITION ON PRODUCT TABLE

WHEN [BASE PART] Contain [Base Line] of driver table than [rep part] should also contain [REP LINE] OF DRIVER TABLE
if it is not same than appear in the output

so in this case output is

Thank You Very much


#2

Not 100% sure what you need, but maybe this:

SELECT p.*
FROM @PRODUCT p
WHERE NOT EXISTS(
    SELECT 1
    FROM @DRIVER d
    WHERE p.[Base Line] LIKE d.[Base Line] AND
        p.[REP LINE] LIKE d.[REP LINE]
    )