Database

PIEBALDconsult wrote:

so I want 20

SELECT * FROM TableA A INNER JOIN TableB B ON A.Field1=B.Field1
UNION ALL
SELECT * FROM TableA A INNER JOIN TableB B ON A.Field2=B.Field2
WHERE NOT A.ID IN (SELECT A.ID FROM TableA A INNER JOIN TableB B ON A.Field1=B.Field1)

WITH CTE AS
    (
    SELECT  A.ID aID
           ,A.Field1 aField1
           ,A.Field2 aField2
           ,CASE WHEN L.ID IS NULL THEN R.ID ELSE L.ID end bID
           ,CASE WHEN L.Field1 IS NULL THEN R.field1 ELSE L.Field1 END bField1
           ,CASE WHEN L.Field2 IS NULL THEN R.Field2 ELSE L.Field2 END bField2
    FROM    TableB L
    right OUTER JOIN TableA A
    ON  l.field1 = a.field1
    left OUTER JOIN TableB R
    ON a.field2 = r.field2
    )
SELECT  aID
       ,aField1
       ,aField2
       ,bID
       ,bField1
       ,bField2
FROM    CTE
WHERE   bid IS NOT null

SELECT  A.ID aID
       ,A.Field1 aField1
       ,A.Field2 aField2
       ,CASE WHEN L.ID IS NULL THEN R.ID ELSE L.ID end bID
       ,CASE WHEN L.ID IS NULL THEN R.field1 ELSE L.Field1 END bField1
       ,CASE WHEN L.ID IS NULL THEN R.Field2 ELSE L.Field2 END bField2
FROM TableB L
RIGHT OUTER JOIN TableA A ON L.Field1 = A.Field1
LEFT  OUTER JOIN TableB R ON A.Field2 = R.Field2
WHERE L.ID IS NOT NULL OR  R.ID IS NOT NULL

SELECT  commonfields,
        Ing1Name AS fieldanme,
        Ing1Size,
        Ing1PRM,
        Ing1AcceptedUnderTol,
        Ing1SPCSample,
        Ing1TargetWgt,
        Ing1ActualWgt,
        Ing1PartialActualWgt,
        Ing1PreactWgt,
        Ing1SlowFeedTime,
        Ing1FastFeedTime
FROM    SomTable
UNION ALL
SELECT  commonfields,
        Ing2Name,
        Ing2Size,
        Ing2PRM,
        Ing2AcceptedUnderTol,
        Ing2SPCSample,
        Ing2TargetWgt,
        Ing2ActualWgt,
        Ing2PartialActualWgt,
        Ing2PreactWgt,
        Ing2SlowFeedTime,
        Ing2FastFeedTime
FROM    SomTable

Chris Meech wrote:

I'm sorry I only rob banks. Can't help you out.

thatraja

FREE Code Conversion VB6 ASP VB.NET C# ASP.NET C++ JAVA PHP DELPHI ColdFusion
HTML Marquee & its alternatives

Nobody remains a virgin, Life screws everyone