SQL select statement combining with different tables

Question

say I have three tables: TableA, TableB, and TableC. Each of these tables have a column: ColA.

TableA is my main table and TableA.ColA has a value.

I wish to check either TableB or TableC has also a value in their ColA that corresponds with my TableA.ColA.

So my statement is:

select count(*) 
 from TableA, TableB, TableC
where ( TableA.ColA = TableB.ColA AND TableA.ColA = "ABC")  
   OR ( TableA.ColA = TableC.ColA AND TableA.ColA = "ABC" )

But this does not work.

If my TableB.ColA doesn't have a ColA value and TableC.ColA has a value, the result returned is still 0. I should get a count of 1 back.

What is wrong with my select statement?

My TableA data:

ColA
-----
ABC

My TableB data:

ColA
----
NULL

My TableC data:

ColA
----
ABC

Because TableC.ColA and TableA.ColA has the same value, it should return me a count of 1. But it is not doing so. My database is SAP ASE

thanks

Answer 1

You are doing INNER JOIN and you need OUTER JOIN or EXISTS. Syntax will vary for RDBMS you are using.

Try this:

SELECT    COUNT(*)
    FROM
        TableA A
    WHERE
        A.ColA = "Value"
        AND (
            EXISTS(
                SELECT * FROM TableB 
                    WHERE
                        ColA = "Value"
            )
            OR EXISTS(
                SELECT * FROM TableC
                    WHERE
                        ColA = "Value"            
            )
        )