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 ALLSelect * 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 ALLSelect *,2 from [DBName2].[schema].[TableName2] where [ColumnName] = '[Value]')tGROUP BY PKHAVING 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 1Invalid column name '[TableName1]'.Server: Msg 207, Level 16, State 1, Line 1Invalid column name '[TableName2]'.@ visakh16I tried your first suggestion and received the following error:Server: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'WHERE'.Server: Msg 156, Level 15, State 1, Line 5Incorrect 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 thisSelect * from [DBName1].[schema].[TableName1] t1 where [ColumnName] = '[Value]'AND EXISTS (SELECT 1 FROM [DBName2].[schema].[TableName2] WHERE PK=t1.PK)UNION ALLSelect * 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 tablesin 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 needNow, 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? |
|
|
|