• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 609
  • Last Modified:

MSSQL Multiple Select Statement

How can a select statement be included inside another select statement?

I would like to query the results from the first query by using a second query.

I currently have 3 databases which include the same table names. Now, I need to grab a table which contains data that is missing from a specific column, I want to check which sites are missing data from a specified table.

How can this be accomplished?


 
0
supportrp
Asked:
supportrp
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT * FROM db1.dbo.Table1 a WHERE NOT EXISTS (SELECT 1 from db2.dbo.Table1 b where a.col = b.col )
0
 
YZlatCommented:
first you'll need to establish linked servers between 3 databases and use OPENQUERY to get data from other 2 databases

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as it's not 100% clear what you need, you must check out either:

UNION / UNION ALL  => to append results of 2 selects (can also be done in a subquery)

INNER JOIN  / LEFT JOIN  => to combine 2 selects, with left join returning the results of the first table even if there is no match in the second table

EXISTS  / NOT EXISTS => condition (in WHERE clause, usually)  to check if there is a matching record (or not)
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
tsqlguyCommented:
Use Left Joins to accomplish this. Here is a template.

SELECT a.ID, b.ID, c.ID FROM [DatabaseName1].[TableName1] a
LEFT JOIN [DatabaseName2].[TableName2] b
LEFT JOIN [DatabaseName3].[TableName3] c
WHERE a.ID IS NULL OR b.ID IS NULL OR c.ID IS NULL

This will give you all id's that are in one table, but not in one of the other two.  :-)

Have a great rest of your day!
0
 
supportrpAuthor Commented:
Using EXISTS and NOT Exists is getting some of the results that I need.
How can I display the Values that exists and display the non existing values as NULL or False all at once, instead of just showing the not existing only or existing only?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you have to do a LEFT JOIN among those two columns
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
sorry

you have to do a LEFT JOIN among those two tables using the common key
0
 
HainKurtSr. System AnalystCommented:
use FULL JOIN

select t1.*, t2.*
from table1 t1 full join table2 t2 on t1.id=t2.id

you will get like this


ID  COL1  COL2 ID_1 COL1_1 COL2_1
 1  AAAA  BBBB    1 AAAAAA BBBBBB
 2  BBBB  CCCC
                  3 CCCCCC DDDDDD

2 is only in table 1, 3 is only in table 2
1 is in both, columns may have different values since we joined by id only...

Open in new window

0
 
Anthony PerkinsCommented:
YZlat,

>>first you'll need to establish linked servers between 3 databases and use OPENQUERY to get data from other 2 databases<<
You may want to double check this:  Unless the databases are on different servers you do not need linked servers, let alone OPENQUERY().
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now