eryan
asked on
Transact-SQL Statement.
I have the following structure of 3 tables with the key fields shown.
TableA TableB TableC
TA_Key <----> TA_Key <------> TA_Key
TB_Year TB_Year
with the tables linked as shown.
In order to set up a text file with details from the three
tables I have set up a stored proc with the following statement.
SELECT * from TableA, TableB, TableC
WHERE TableA.TA_Key = @p1 AND TableA.TA_Key = TableB_TA_Key
AND TableA.TA_Key = TableC.TA_Key.
This returns me resultsets perfectly fine except in the
situations where a row exists on TableA and TableB but not
on TableC or some combination of this.
In this situation I would like the resultset to return me
the TableA and TableB rows and the TableC row as blank.
Whats the best way to write my SQL Statement ?.
ED.
TableA TableB TableC
TA_Key <----> TA_Key <------> TA_Key
TB_Year TB_Year
with the tables linked as shown.
In order to set up a text file with details from the three
tables I have set up a stored proc with the following statement.
SELECT * from TableA, TableB, TableC
WHERE TableA.TA_Key = @p1 AND TableA.TA_Key = TableB_TA_Key
AND TableA.TA_Key = TableC.TA_Key.
This returns me resultsets perfectly fine except in the
situations where a row exists on TableA and TableB but not
on TableC or some combination of this.
In this situation I would like the resultset to return me
the TableA and TableB rows and the TableC row as blank.
Whats the best way to write my SQL Statement ?.
ED.
ASKER
Odessa,
That still only returns me a result set if tablesA,B,C all have
a matching Key.
I.E If there is a TableA row with key = 12
a TableB row with key = 12, 1998
a TableC row with key = 12, 1998 its fine
but if the tableC row does not exist, then a blank resultsset
is returned.
ED
That still only returns me a result set if tablesA,B,C all have
a matching Key.
I.E If there is a TableA row with key = 12
a TableB row with key = 12, 1998
a TableC row with key = 12, 1998 its fine
but if the tableC row does not exist, then a blank resultsset
is returned.
ED
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tschill,
Thanks for that, exactly what I needed.
ED.
Thanks for that, exactly what I needed.
ED.
SELECT * from TableA, TableB, TableC
WHERE ((TableA.TA_Key = @p1) or (TableA.TA_Key = NULL)) and
((TableB.TA_Key = @p1) or (TableB.TA_Key = NULL)) and
((TableC.TA_Key = @p1) or (TableC.TA_Key = NULL))