supportrp
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
YZlat
first you'll need to establish linked servers between 3 databases and use OPENQUERY to get data from other 2 databases
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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)
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)
Use Left Joins to accomplish this. Here is a template.
SELECT a.ID, b.ID, c.ID FROM [DatabaseName1].[TableName
LEFT JOIN [DatabaseName2].[TableName
LEFT JOIN [DatabaseName3].[TableName
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!
SELECT a.ID, b.ID, c.ID FROM [DatabaseName1].[TableName
LEFT JOIN [DatabaseName2].[TableName
LEFT JOIN [DatabaseName3].[TableName
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!
ASKER
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?
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?
you have to do a LEFT JOIN among those two columns
sorry
you have to do a LEFT JOIN among those two tables using the common key
you have to do a LEFT JOIN among those two tables using the common key
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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().
>>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().