|
Hy. I'm trying to do a select from multiple databases and tables for an occurence.
let's say i have databases db1, db2 and db3. Each have the following tables t1, t2, t3. And in each table there is a field named id which i want to select and a field and p_id which i want to use as a condition. To give a better idea this is an example of what results i want to get:
---------------------------------------
|id_t1 | id_t2 | id_t3 | database_name|
---------------------------------------
| 100 | 123 | 356 | db1 |
| 252 | 156 | 566 | db2 |
|... | ... | ... | db3 |
---------------------------------------
How can i pass the p_id condition to each table and each database. I tried this:
select t1.id as id_t1, t2.id as id_t2, t3.id as id_t3, (select database()) as database_name from db1.t1, db1.t2, db1.t3 where ****can't make the condition work because if i only say p_id = 100 it says it's ambiguous****
union select *** all the above *** from db2 and db3
Thanks
|
|
|
|
|
A couple of relevant assumptions, you are using SQL Server and all the databases are on the same server.
If you are getting the database name from a variable then you need to use dynamic sql.
Select idt1,idt2,idt3
from db1.dbo.tablename
Select idt1,idt2,idt3
from db2.dbo.tablename
Select idt1,idt2,idt3
from db3.dbo.tablename
Now insert UNION between the 3 queries to get them in 1 result set
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hey. Thanks for the reply, but what you suggested does not apply for me. I'm trying to select from 3 different tables from each of the 3 databases the rows which contain a certain value. In the mean time i have completed the query selecting the rows from one table, union them with the other tables in one database, then union the whole select with the other two databases.
Here is what i did
SELECT * FROM (SELECT id, "t1", "db1" FROM db1.t1 WHERE p_id = ""
UNION SELECT id, "t2", "db1" FROM db1.t2 WHERE p_id = ""
UNION SELECT id, "t3", "db1" FROM db1.t3 WHERE p_id = "") s1
UNION
SELECT * FROM (SELECT id, "t1", "db2" FROM db1.t1 WHERE p_id = ""
UNION SELECT id, "t2", "db2" FROM db2.t2 WHERE p_id = ""
UNION SELECT id, "t3", "db2" FROM db2.t3 WHERE p_id = "") s2
UNION
SELECT * FROM (SELECT id, "t1", "db3" FROM db1.t1 WHERE p_id = ""
UNION SELECT id, "t2", "db3" FROM db3.t2 WHERE p_id = ""
UNION SELECT id, "t3", "db3" FROM db3.t3 WHERE p_id = "") s3
If you have a better option for this I'm looking forward to see it, because this gets really crowded when i have more tables and databases. And yes, I am using MySql and all the databases are on the same server. The db-s are the same with db1 being main and the other are backups which differ only in the content not in fields and tables
|
|
|
|
|
To be clear - your database is MySQL.
To use different databases in the same SQL requires that the SQL that the database uses provides a way to specify the database itself as part of the name for the table.
Far as I can tell MySQL does not allow that.
The following is the documentation for what a 'table' can be.
http://dev.mysql.com/doc/refman/5.0/en/join.html[^]
This doesn't totally eliminate the possibility since this might just not specify it. Finding the similar information for MS SQL Server is difficult.
If it doesn't allow it then you must query each database independently and then merge them together yourself.
|
|
|
|
|
of course you can use the database.table.field syntax on mysql. the query i wrote works i only asked if someone has a better approach because the query gets difficult to follow once i use more databases and tables.
The join operation joins tables on certain values. I don't need the joined. I just need to see where in those tables (the row id) a value is found.
|
|
|
|
|
Hi,
I'm running SQL Server and I have an application that is creating a new database. However I'm not sure what directory I should store the database. Any suggestions?
Thank you,
Glenn
|
|
|
|
|
If the database is not server based then I would put it in either the users appdata folder or the applications install folder, although this may no longer be valid b/c of the UAC requirements.
I think the appdata folder is where it should be.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I like the idea of using the application's install directory, somewhere far from the Windows default structures. That way, when Windows Update crashes the system and Windows has to be reinstalled, it isn't erased and replaced by empty folders. 
Will Rogers never met me.
|
|
|
|
|
I used to be one of those people who would not accept the default installation locations for anyhting, I figured I can design a better file structure than any jumped up softie so I would always change the default. Partly b/c my file system was then completely non standard and no miserable robot/sniffer/@#$@# prick could find anything on MY system.
This lasted till about the mid 90s when there were so many systems coming out of MS and other vendors that simply did not work if they could not find the information in the default folders. Yep I wimped out, I now treat the default folder like the EULA, accept it and move on.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I know what you mean... I try to studiously change the defaults whenever I install new software, but then comes along a product written by idjits that won't let me change them. Microsoft is the single worst offender in that respect, but there are a host of others. Eventually I get lazy, and as the advice to rape victims goes, I lie back and accept the inevitable. But with each new system I try again, having just had to rebuild thousands of files because reinstalling Windows erased them. 
Will Rogers never met me.
|
|
|
|
|
 I also joined that club in the 90s.
|
|
|
|
|
As Mycroft said, it's more of a matter of opinion if this isn't a server installation. Personally I like to have the database files in an easy path so I use C:\Databases\... and a tree structure for different versions, databases etc.
If you're going to create multiple databases you can change the default location for the new databases in the Management Studio / Server Properties / Database Settings and type a path you want. However, note that the Management Studio won't create a new folder for you so you have to create it separately and assign proper privileges.
|
|
|
|
|
Anywhere but under Program files.
All Users App Data should work.
|
|
|
|
|
You cannot vote on your own post
0
i want to run windows form program in more than one PC and the database to be in one PC and other PCs connect to it
DB (sql server)
>>>>>>>>
how can i control
*in setup of sql server
*or on connection string
*and what the requirements to make this is need to windows server or sql express
________________________________
tell me the steps and requirements please 
|
|
|
|
|
Mohamed Nabawy wrote: You cannot vote on your own post
Which I believe makes sense.
Mohamed Nabawy wrote: i want to run windows form program in more than one PC and the database to be in
one PC and other PCs connect to it
DB (sql
server)
>>>>>>>>
how can i control
*in setup of
sql server
*or on connection string
*and what the requirements to make
this is need to windows server or sql express
Your question is a bit large...
Have you tried or planned of trying something? If yes and you got stuck in a specific question then posting a targeted question would likeliy give you many helpful answers. Especially if you present some effort you have already made and want some help to get it running etc.
If you're still in the early phase of learning the concepts, I suggest that you read the literature available and google for specifics.
|
|
|
|
|
See if xp_cmdshell is what you are looking for. It allows you to run a windows command from within a T-SQL stored procedure.
|
|
|
|
|
Hello Everybody,
As we find the Sum of Salary from Emp Table.
SQL Query : Select Sum(Emp) from Emp;
Now I want to get the product of Salary : Product means (first sala * Second Sal * Third Salary * ...... * Last salary);
How can i use with the help of SQL Command.
I Do't want to USE PL/SQL Command.
Thanks
If you can think then I Can.
|
|
|
|
|
assuming as you haven't given a table structure, that there is a column that has the first salary etc in.
select salary_type, sum(emp) from emp group by salary_type
if this doesn't help you, you will need to provide more information i.e. table structures etc.
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
I think u are not find the meaning of my question.
I want to Get the Multiplcation of All Employee Salary.
For Example :
Ename Sal
----- ----
Anu 10
Minu 20
Tusar 20
Piter 10
Now I want to get the Multiplication of All Salary : (10 * 20 * 20 * 10)
With the help of SQL. I Do't want to use PL/SQL Commands.
Thank
If you can think then I Can.
|
|
|
|
|
|
This Is Just for Knowing Concept to How To Multiply Fields Values.
And I Say that i do't want to use PL/SQL.
If you can think then I Can.
|
|
|
|
|
Did you try using Cursor, I think that can solve your problem.
Hope this helps.
All the best.
|
|
|
|
|
No, But How can i use cursor for this requirement...
If you can think then I Can.
|
|
|
|
|
You can use select exp(sum(ln(sal))) from emp which of course will throw an exception for negative numbers and zero.
But that you can easily fix with a case expression.
The best solution is of course to create a function to do the job.
|
|
|
|
|
Can u please explain with the form of SQL Query.
If you can think then I Can.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
