Joining With Potentially Empty Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joining With Potentially Empty Table

Author

Topic

jdpellegrino
Starting Member

2 Posts

Posted - 2010-11-03 : 12:42:07

I have been considering this problem for some time now and figure I should turn to help from those who have worked with this and can point me to the solution I am sure I am over looking.

The problem specifically is that I am trying to join three tables together, the third could potentially be empty. When the table is empty the select operating on it (presumably) returns no results and thus the overall select returns no results. What I would like is that if a select on a table returns no results (that is the table is defined but has no data) then I'd like empty or null columns to be put in place for the column that could not be retrieved because the table contains no data.

For example the query:
SELECT * FROM Table1 AS a LEFT JOIN Table2, Table3 AS c WHERE ((a.Key = c.parentTable) AND (c.TableData = "TextString"));

Will return a result as I expected presuming that the Table3 exists which is a parent of Table1 and in TableData contains the string "TextString". The database I am working on does contain these tables meeting these conditions so I get a result:

3|Data1|Data2|Data3|...|Some Text From Table 3

If I change the query such that the condition for the third table can't be met (as it would be if the table had no data):

SELECT * FROM Table1 AS a LEFT JOIN Table2, Table3 AS c WHERE ((a.Key = c.parentTable) AND (c.TableData = "TextStrin"));

I get nothing at all, I'd prefer to get something more like:
3|Data1|Data2|Data3|...||

where the data that would have been provided by Table3 is just appended to the result as NULL or empty.

Any thoughts?

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-03 : 12:56:01

SELECT
*
FROM
Table1 AS a
LEFT OUTER JOIN
(
SELECT
*
FROM
Table3
WHERE
(Column = "SomeValue")
) AS c
ON a.Column = c.Column

Is that what you're trying to do?

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-03 : 13:09:07

>> The problem specifically is that I am trying to join three tables together, the third could potentially be empty. When the table is empty the SELECT operating on it (presumably) returns no results and thus the overall SELECT returns no results.<<

That depends on the OUTER/INNER JOIN used.

>> What I would like is that if a SELECT on a table returns no results (that is the table is defined but has no data) then I'd like empty [sic] or NULL columns to be put in place for the column that could not be retrieved because the table contains no data. <<

Empty? That is a spreadsheet concept that does not exist in SQL.

Let's fix your examples so the syntax is right and we know what you are talking about. Get rid of the screwed names that show meta-data, translate the code into infixed notation, etc.

SELECT *
FROM (Table1
LEFT OUTER JOIN
Table2
ON ????????????) -- fix it
INNER JOIN
Table3
ON Table1.foo_key = Table3.foo_key
AND Table3.foobar_txt = "TextString";

>> Will return a result as I expected presuming that the Table3 exists which is a parent of Table1 and in TableData contains the string "TextString".<<

In spite of a missing ON clause? I don't think so. Parent? There is no such concept in SQL. Your mindset is still stuck in Network and hierarchical databases. We have referenced and referencing tables. This is totally different.

Now, looking at your DDL -- mmm, no DDL to look at! Try again with some DDL, please.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

jdpellegrino
Starting Member

2 Posts

Posted - 2010-11-04 : 10:07:30

quote:
Originally posted by TimSman
Is that what you're trying to do?

Thanks for your suggestion Tim, I'll take a look at it but I am not sure it's exactly what I am thinking about.

quote:
Originally posted by jcelko
Empty? That is a spreadsheet concept that does not exist in SQL.

What would I then call a table which has been defined but does not have any data yet? Being relatively new to the subject I understand my parlance may be off, I appreciate the correction.

quote:
Originally posted by jcelko
Now, looking at your DDL -- mmm, no DDL to look at! Try again with some DDL, please.

I will try and come up with a simple example to illustrate my question when I can, thanks for the reply though.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2010-11-04 : 10:27:21

This simple SQL Code should give you the results you want for Table1 and Table3:


SELECT	*
FROM	Table1
	left outer join Table 3
		on Table1.Key = Table3.parentTable
		and Table3.TableData = "TextString"

But it is not apparent from your sample code how you intend to join Table2 to this result set. Or were you looking for a cross-join?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources