|
Hi,
i have the table lab_inestigations which has created_by, processed_by and closed_by and all three columns are integer holding a user id (systm_user_id) from a table sysem_users but of course each created_by, processed_by and closed_by will have different systm_user_id value.. How can I make such sql statement to rertive the system_user_name for all three columns from system_users?
Thanks,
Jassim
|
|
|
|
|
You need to make 3 joins to the user table, one for each of your user fields in lab_inestigations, something like
SELECT *
FROM lab_inestigations I
INNER JOIN sysem_users S1 ON SI.UserID = I.CreatedBy
INNER JOIN sysem_users S2 ON S2.UserID = I.ProcessedBy
INNER JOIN sysem_users S3 ON S3.UserID = I.ClosedBy
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I Have Table with columns as 1,2,3,4,5
I need to write query like
SELECT * FROM Table where columnName = 2. How can I specify column header in where clause
|
|
|
|
|
Hope this will help you
SELECT *<br />
FROM information_schema.columns<br />
WHERE COLUMN_NAME='YourColumnName'
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
It gives column details but not data for that column in specified table.
|
|
|
|
|
You don't specify the column in the where clause, you do it in the select clause as:
SELECT <COLUMNNAME>
FROM <TABLE>;
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Forgot to say, in SQLServer you can put squarebrackets [] around stupid columnnames as a temporary fix.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Andersson, did u understand what I meant. I have Table like
Srno col1 col2 col3
1 aaaa bbb vvv
2 hjgj yui jhk
3 hkjdf hgjk jkjlk
I need to get data for specified column.
suppose i sent parameter
col2 then bbb,yui,hgjk
col3 then vvv,jhk,jkjlk
|
|
|
|
|
Have you tried SELECT COL2 FROM TABLE?
You would get the content from that column like:
COL2
bbb
yui
hgjk
If this is not what you need, then you need to elaborate more.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Andersson 'SELECT COL2 FROM TABLE' this is basic select query if i know the column name. In my case I don't the column names in table.
so if table have column name with col2 then return result otherwise nothing.
|
|
|
|
|
Then you need to use dynamic sql as suggested by Estys.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
|
Thanks Estys but its dynamic query right. Is there any way to use built in key words like column_Name in where clause
|
|
|
|
|
a WHERE clause determines which rows are being returned.
|
|
|
|
|
Sorry... I couldn't get u?
|
|
|
|
|
The WHERE clause isn't used to specify which columns you want to retrieve, but to limit the amount of records returned. It's a filter
What you want to do can only be done using dynamic SQL. You'll have to know what columns you want to retrieve, or use a wildcard (*) to retrieve them all.
I are Troll 
|
|
|
|
|
I had a hard time trying to decide whether I should post this in the database side of things or the vb.net.
This is coming from a vb.net 3.5 application connecting to a SQL Server 2008 database. We currently have this application on 50 different SQL Servers though all of these other ones are SQL Server 2000/2005.
This section of a code comes from a routine where we are validating that the database exists and makes sure that it is up to date. By this point we have already validated that the connection string (strADOConnString) is valid and that we can connect to the server. When we run this section of code where we check for the database name in the server the only difference between this and our normal connection string is that we are looking at the "master" database instead of our database. This section of code blows up with the "Failed to connect to server" error message.
I've validated that the user does have the proper permissions on this server and this is further verified by our ErrHandler routine which writes the error to the database using the strADOConnString. I've tested this locally using SQL Server 2008 and have had no issues. They have tested this using the SA account leading me to suspect that it is not a simple permissions issue. I've made sure that they can logon to SQL Server Management Studio with this user with no pop ups and I've made sure that they can connect to the server through an ODBC with this particular SQL user.
What else could be causing this error?
Dim strADOConnStringMaster As String = strADOConnString
strADOConnStringMaster = strADOConnStringMaster.ToString.Replace("Initial Catalog=" & objSystem.DatabaseName & ";", "Initial Catalog=master;")
Try
Dim connection As New SqlConnection(strADOConnStringMaster)
server = New Server(New ServerConnection(connection))
bDbExists = server.Databases.Contains(objSystem.DatabaseName)
connection.Close()
Catch ex As Exception
strStep = ".STEP01"
Call ErrHandler(_moduleName & strStep, _className, String.Format("Server defined in database configuration does not exist. Message:{1}.", lDBVersion.ToString, Err.Description), Not bCmdLine)
If Not bCmdLine Then
MsgBox("Unable to locate server defined in database configuration.", MsgBoxStyle.Critical)
End If
Exit Sub
End Try
Some people sail through life on a bed of roses like a knife slicing through butter.
|
|
|
|
|
Hi,
1.
IMO this is the right forum to ask.
2.
I'm no DB specialist at all, however I'll give you my ideas anyway.
3.
I suspect the line
strADOConnStringMaster = strADOConnStringMaster.ToString.Replace("Initial Catalog=" & objSystem.DatabaseName & ";", "Initial Catalog=master;")
for a couple of reasons:
a. I fail to see why ToString is used there
b. if the search fails, strADOConnStringMaster remains unchanged and nothing gets signaled. This could be caused by a difference in white space, in casing, in any minor detail.
So I'd suggest you clean up and code more defensively, if the replace has to happen, make sure it does, e.g. by storing the result in a different variable.
4.
I'm not familiar with the Err object (I use C# most of the time), however I doubt Err.ToString() is as informative as Exception.ToString() is, so I suggest you show ex.ToString() in a MessageBox.Show when an exception gets caught.
Hope this helps.
|
|
|
|
|
Without changing your connection string, you should be able execute this SQL statement:
SELECT name
FROM master..sysdatabases
You could then determine whether or not your database is installed on that server.
This of course will only work if the user you are connecting with has the proper permissions to access the master tables.
Give it a shot. 
|
|
|
|
|
Hi
Database: SQL Server 2005
select 589/5 produces 117
How can I make it return 117.8?
I am doing the division operation on 2 int columns and want the output in decimals. Do I have to make the columns themselves decimal?
Thanks
Shreekar
|
|
|
|
|
OK - I have tried the below:
declare @d decimal(10,2)
select @d = 859 * 1/5
select @d
This returns 171.00
and
declare @d decimal(10,2)
select @d = 859.00 * 1/5.00
select @d
This returns 171.80
This tells me that the 2 numbers participating in the operation have to be decimals by definition.
Is there any way to work around this as I do not want to change the column types to decimals as I do not expect them to hold fraction data any time.
It is only the result that may have fraction data.
Any ideas?
Shreekar
|
|
|
|
|
Cast[^] it;
SELECT CAST(859 AS DECIMAL) / CAST(5 AS DECIMAL)
I are Troll
|
|
|
|
|
Just missed your post. Realised the same solution as you suggested.
Thanks for the confirmation!
Shreekar
|
|
|
|
|
You're welcome. Thinking out loud helps, even on a messageboard
I are Troll
|
|
|
|
|
Figured it out myself, I cast the operands themselves to decimals and the product was decimal.
Sorry about that - turned out to be loud thinking than a genuine question.
Shreekar
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
