How to query multiple tables where only 1 table may contain data

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)

How to query multiple tables where only 1 table may contain data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-23 : 12:56:41

Casey Shobe writes "My question is as follows. I have 2 tables, as shown:

Table1
------
alert_id
attack_type

Table2
------
alert_id
ip_protocol

Now, I want to get results such as those which could be retrieved with a:
select a.attack_type, b.ip_protocol
from table1 a, table2 b
where a.alert_id=b.alert_id

However, the problem is that while Table1 always has an entry for the given alert_ID, Table2 does not. Meaning that if it does, I want the query to return attack_type and ip_protocol, and if not, then the query should just return the data from the first table, attack_type, only.

I'm using SQL Server 2000.

Is this possible? Thank you very much for your great site!"

ToddV
Posting Yak Master

218 Posts

Posted - 2002-04-23 : 13:24:40

This is the situation for which you would use OUTER JOINs.

select a.attack_type, b.ip_protocol
from table1 a
LEFT OUTER JOIN table2 b
ON a.alert_id=b.alert_id

Radhika
Starting Member

15 Posts

Posted - 2002-04-23 : 13:31:18

The solution to your query can be solved by using the table JOINs. From your problem i guess i got it right - Table1 has the base Data, and the associated record in Table2 is optional. To solve this issue the same query can be rewritten as
select a.attack_type, b.ip_protocol
from table1 a LEFT OUTER JOIN table2 b ON a.alert_id=b.alert_id
This statement will return the Record from Table1 even though there is no associated record in Table2.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources