Combine Queries to Produce One Table

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)

Combine Queries to Produce One Table

Author

Topic

Sparxx
Starting Member

23 Posts

Posted - 2009-02-02 : 12:21:09

Ok, first off, HELLO ALL!!! My first post could be a doozy for all who care to partake in the assistance to a solution:

Currently I am trying to use SQL to produce a table based on search criteria. Normally this isn't an issue if the data is in the same database because I can easily use AND, OR and other boolean-type commands to narrow a search result. However, this data I am trying to compile has criteria located on different databases.

As of right now, I have 2 strong queries that are really helpful:

Select * from [DBName1].[schema].[TableName1] where [ColumnName] = '[Value]'
Select * from [DBName2].[schema].[TableName2] where [ColumnName] = '[Value]'
- - - - - - -
As of right now, each query run produces 2 separate sets of information. However, these 2 sets of results share x amount of data. I need to find out how to produce 1 set of data where it only shows the common data between the 2 search criteria.
- - - - - - -
I work for a software company and our software frequently calls to databases to produce information. Our clients use both SQL 2000 (Analyzer & Enterprise Manager package) and SQL Server 2005 Enterprise. Because I am new to SQL, I may be making this harder than it is, but any help in the matter is appreciated.

Thanks!

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-02 : 12:27:23

Maybe I missed something in your description but this seems like a simple join across databases doesn't it?
select d1.*,d2.*
from [DBName1].[schema].[TableName1] D1
INNER JOIN [DBName2].[schema].[TableName2] D2
ON D1.KeyColumn=D2.KeyColumn
WHERE D1.ColumnName='[Value]'
AND D2.ColumnName='[Value]'

Mike
"oh, that monkey is going to pay"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 12:28:41


Select * from [DBName1].[schema].[TableName1] t1 where [ColumnName] = '[Value]'
WHERE EXISTS (SELECT 1 FROM [DBName2].[schema].[TableName2] WHERE PK=t1.PK)
UNION ALL
Select * from [DBName2].[schema].[TableName2] t2 where [ColumnName] = '[Value]'
WHERE EXISTS (SELECT 1 FROM [DBName1].[schema].[TableName1] WHERE PK=t2.PK)

PK is primary key of two tables

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 12:35:28

or is it this?


SELECT PK,MIN(field1),MIN(field2),...
FROM
(
Select *,1 AS Cat from [DBName1].[schema].[TableName1] where [ColumnName] = '[Value]'
UNION ALL
Select *,2 from [DBName2].[schema].[TableName2]  where [ColumnName] = '[Value]'
)t
GROUP BY PK
HAVING MAX(Cat) <> MIN(Cat)

Sparxx
Starting Member

23 Posts

Posted - 2009-02-02 : 13:18:08

@mfemenel:
I tried that method you had suggested and replaced all the '[]' terms with what I had provided as my example, and I believe I was supposed to replace d1, d2, D1 and D2 with the database names I provided. Is this correct? If so, I was not able to produce results, as I received the following error:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name '[TableName1]'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '[TableName2]'.

@ visakh16
I tried your first suggestion and received the following error:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'WHERE'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WHERE'.

I have yet to try the 2nd query string as I am not sure what is required for (field1) or (field2) in the MIN string. Also, do I just type in (or copy/paste) the PK string or replace PK with something.

-------------
I appreciate the help thus far.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 13:22:41

modify 1st suggestion like this



Select * from [DBName1].[schema].[TableName1] t1 where [ColumnName] = '[Value]'
AND EXISTS (SELECT 1 FROM [DBName2].[schema].[TableName2] WHERE PK=t1.PK)
UNION ALL
Select * from [DBName2].[schema].[TableName2] t2 where [ColumnName] = '[Value]'
AND EXISTS (SELECT 1 FROM [DBName1].[schema].[TableName1] WHERE PK=t2.PK)

PK is primary key of your tables

in 2nd suggestion, field1,field2,... represent your table fields. replace them with actual field names and PK with primary key

Sparxx
Starting Member

23 Posts

Posted - 2009-02-02 : 13:36:09

Stupid question.....

Are the numbers listed down the left side of my Query Analyzer screen results (at the bottom) the PK's?

For example, when I run my first query listed above for DBName1, it gives me 164 results, numbering 1 through 164. Are each of those numbers PK's? If so, does just using "1" allow the query to search all 164 available records?

I'm slightly confused.

Sparxx
Starting Member

23 Posts

Posted - 2009-02-02 : 13:53:51

Well I was able to find and run the following script to determine the PK for each table:

SELECT tblcons.TABLE_NAME, keycoluse.CONSTRAINT_NAME, keycoluse.COLUMN_NAME, keycoluse.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblcons
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE keycoluse ON (tblcons.CONSTRAINT_CATALOG = keycoluse.CONSTRAINT_CATALOG) AND
(tblcons.CONSTRAINT_SCHEMA = keycoluse.CONSTRAINT_SCHEMA) AND (tblcons.TABLE_CATALOG = keycoluse.TABLE_CATALOG) AND (tblcons.TABLE_SCHEMA = keycoluse.TABLE_SCHEMA) AND
(tblcons.TABLE_NAME = keycoluse.TABLE_NAME) AND (tblcons.CONSTRAINT_NAME = keycoluse.CONSTRAINT_NAME)
WHERE tblcons.CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY tblcons.TABLE_NAME, keycoluse.CONSTRAINT_NAME, keycoluse.ORDINAL_POSITION

Unfortunately only TableName1 of DBName1 calls to a PK, and not TableName2 of DBName2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 10:15:35

what? do you mean you dont have pk in dbname2 table? then how will you distingusih each record of table?

Sparxx
Starting Member

23 Posts

Posted - 2009-02-03 : 10:25:59

I don't know. After reviewing the processes here and scouring the net, PK is very important when calling to some sort of organization.

It seems that the database table I am trying to access for merging/sorting/reporting does not call to a PK. I have found that all of the most common tables I would need in any instance has their own PK, but this one. FIGURES!!! And to make it even as a "OMG that is so DUH!" moment, each PK that I would need starts as PK_[TableName].... except for the table I immediately need

Now, being a lowly Tech Support Consultant and not the "father" of our illustrious db setup, I cannot go in and start applying a PK to client databases at random (as much as I would love to, to help my immediate problem LOL)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 10:30:09

ok...at least are you able to identify the set of columns in table that will uniquely define row for you?

Sparxx
Starting Member

23 Posts

Posted - 2009-02-03 : 10:39:50

Yes.

But how would I include only one PK? I thought to find a "common ground", wouldn't I need both?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 12:40:52

then use all the columns which form unique value of row in join

Sparxx
Starting Member

23 Posts

Posted - 2009-02-03 : 15:03:28

W00T!!!

You guys rock!

I went back to check on how I was entering in the script that mfemenel had originally posted (btw @ mfemenel, THANK YOU SO MUCH

) and started tinkering with the functionality of the script.

At some point I was being too logical? by assuming (I know, we all know what assume does) that plugging in the DBName for d1, d2, D1 & D2 would be the answer; instead, I found by simplifying the problem by just leaving those "place holders" in the string and literally placing my data in the [] fields that the scripts ran fine.

Wow - being new to the SQL world and applying logic to a situation was only getting me so far (not to mention my local resources were getting a little thin); but seeing that script run the way I had envisioned is so gratifying....

ok... /end emotional response

But wow, THANKS again!

Sparxx
Starting Member

23 Posts

Posted - 2009-02-03 : 20:30:44

Now that I have had a successful run with this script, if I wanted to add one more (or more than one more) search criteria, how would I add additional script to the INNER JOIN combo in that script? Say, for example, I had additional data like [DBName3][TableName3] and [DBName4][TableName4], etc?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources