Conventional Join (or something like that)

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

SQL Server 2000 Forums

SQL Server Development (2000)

Conventional Join (or something like that)

Author

Topic

Nemphys
Starting Member

1 Post

Posted - 2005-07-15 : 08:00:40

Let's make thing simple:
Let's say I have 2 tables, Table1 containing the data (thousands of rows) and table2 contaninig the filter values of a filed in Table1.
I want to make a simple query that returns all records from Table1 that match the records in Table2 (table1 inner join table2 on table1.field1 = table2.field1) IF table2 DOES contain records, and just all the records from table1 IF table2 does not contain any records (no join, or maybe left outer join). The problem is that I want this to be accomplished by a single select statement and not by a storedproc or anything else.
Any hints would be appreciated...

Kristen
Test

22859 Posts

Posted - 2005-07-15 : 08:34:00

[code]
SELECT *
FROM table1
INNER JOIN table2
ON table1.field1 = table2.field1
UNION ALL
SELECT *
FROM table1
WHERE 0 = (SELECT COUNT(*) FROM table2)
[/code]
You might be able to just change the first query to:
[code]
INNER JOIN table2
ON table1.field1 = table2.field1
OR 0 = (SELECT COUNT(*) FROM table2)
[/code]
Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources