Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MSSQL Multiple Select Statement

Posted on 2009-12-29
9
Medium Priority
?
597 Views
Last Modified: 2012-05-08
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
Comment
Question by:supportrp
9 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 26138706
SELECT * FROM db1.dbo.Table1 a WHERE NOT EXISTS (SELECT 1 from db2.dbo.Table1 b where a.col = b.col )
0
 
LVL 35

Expert Comment

by:YZlat
ID: 26138708
first you'll need to establish linked servers between 3 databases and use OPENQUERY to get data from other 2 databases

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26138714
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:tsqlguy
ID: 26139479
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
 
LVL 1

Author Comment

by:supportrp
ID: 26139626
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26139665
you have to do a LEFT JOIN among those two columns
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26139674
sorry

you have to do a LEFT JOIN among those two tables using the common key
0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 1000 total points
ID: 26143137
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26151328
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question