MS SQL Server :: Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Case Does Not Work In My Select Statement
Hello everybody
I have problem with CASE statement. Here is select it

Code Block
select
mev.Id
,mev.MetaElementId
,mev.ElementValue
,mev.DocumentId
,me.ElementTypeId
,castedValue =
case
when me.ElementTypeId =3 then cast(mev.ElementValue as integer)
when me.ElementTypeId =4 then cast(mev.ElementValue as datetime)
end
from dbo.tbMetaElementValue mev
inner join dbo.tbMetaElement me
on mev.MetaElementId = me.Id
where mev.MetaElementId =7

it returns

Id
MetaElementId
ElementValue
DocumentId
ElementTypeId
castedValue

49
7
2006
28
3
6/30/1905 0:00

53
7
2004
30
3
6/28/1905 0:00

61
7
2006
36
3
6/30/1905 0:00

67
7
2005
38
3
6/29/1905 0:00

70
7
2004
39
3
6/28/1905 0:00

105
7
2003
63
3
6/27/1905 0:00

166
7
2006
109
3
6/30/1905 0:00

195
7
2005
129
3
6/29/1905 0:00

220
7
2005
150
3
6/29/1905 0:00

223
7
2006
151
3
6/30/1905 0:00

As you can see it should return castedValue as integer but it cast to datetime which is wrong. If I commented line

Code Block
when me.ElementTypeId =4 then cast(mev.ElementValue as datetime)

it casts everything normal, but as soon as it has more than one condition in CASE it will choose anything but not right casting

Looks like I am missing something really fundamental. Any help is apreciated !

Thanks

View Replies !

Help: Why IN-Operator With Select-Statement It Doesn't Work? But With Given Values It Works
Hello to all,
i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me.
I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418:
declare @IDM int;
declare @IDO varchar(8000);
set @IDM = 3418;
set @IDO = '3430'
select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ))
but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM))
I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks
I use MS SQL 2005 Server Management Stadio Express
Thanks a million and Best regards
Sha

View Replies !

Multiple Update Statement
If I want to update 2 columns at 1 time with a where clause
like

update table a
set column1 = 8 and
set column2 = 9
where id = 10

I know you can't use the and statement, what is the correct syntax?

View Replies !

Multiple Column Update Statement
Hi,

I'm new to SQL Server but not new to SQL because I used it with Oracle. I wonder if it is possible to do this kind of statement on SQL Server:

UPDATE TableX M
SET (M.column1, M.column2, M.column3)=
     (SELECT T.column1, T.column2, G.column3)
             FROM Table_Y T,
                       Table_Z G
             WHERE join condition))
WHERE EXISTS (join condition for TableX)

Basically, I'd like to update multiple columns in one statement but for some reason I can not get it to work.

What would be the equivalent on SQL Server?

Thanks for the help,

Laszlo M

View Replies !

IF Statement To Update Multiple Rows

Hi,

I am writting a bit of SQL that takes data from one table then inserts it into another one. There is a field that can be any value (and is usually null), but when I insert the value in the new table then I want to execute:

IF table.field>0 then tabl2.field='400'. In other words for every row in the selection that has a field that is greater than 0 then '400' will be put into the new table.

I am not sure if the IF stamement can loop through a number of rows and execute depending on the value of a field in that row??

Thanks

View Replies !

UPDATE Statement With Multiple Criteria
I am trying write a query to update a column of data in my xLegHdr table however the update is based on multiple criteria. I was trying to use "IF..ELSE" statements but that is not working.

I would like to update the "SMiles" column based on the data in the "Dist" column. If the number in the "Dist" column is less than 250 then subtract 25 and multiply it by 1.15 the result should go in the "SMiles" column. If the number is grater than 250 then subtract 40 and multiply by 1.15 and place the result in the "SMiles" column; like so:

UPDATE xLegHdr
SET SMiles =
IF Dist<250 THEN Round(Dist-25)*1.15)
ELSE Round(Dist-40)*1.15)
END IF

Any ideas?

View Replies !

How To Use Select Statement During Update
How to Use select statement when using Update statement.

Eg: Update program_video set program_id=(select id from program where recordname='122'), stopnumber=stopnumber*-1 where stopnumber < 0

Thanks & Regards

Jagadeesh

View Replies !

Update Two Tables With The Same Statement
hi,does any know how to update two tables with the same statement?is it possible?here is my unsuccessful try:UPDATE A,BSET A.VAL='BLUH1', B.VAL='BLUH2'from #T A, #T2 BWHERE A.LOANID=B.LOANID

View Replies !

Update 2 Tables In One Statement
this code is not working.
I canâ€™t update 2 tables in one statement?

update pd, p
set pd.show = 0 , pd.seashow = 0
FROM ProductDetail pd
join Products p on p.itemid = pd.ItemID
where pd.show = 1
and pd.site in (1,4,6)

View Replies !

SELECT Statement For 2 Tables
Hi
How do I make a SELECT statement to use in WebMatrix to access data that selects from 2 tables:
based on LastnameID it selects from table Contacts
and based on first name and Lastname it selects from student tables
regards
daniel

View Replies !

SELECT Statement Between Two Tables
I have two tables

Absent
==========
absent_id
employee

Employee
==========
emp_id
employee
department

In plain english I want to select * from absent where the employee's department (in the employee table) equals 'whatever'

Can anyone help please.

View Replies !

How Can I Run Select Statement For Two Tables That
Hi all,
How can I run select statement for two tables that are on two different databases?

Let's say I have a SQL server 2005 on Windows 2003 server.
On this SQL server 2000 I have two databases.
Letâ€™s say DB1 and DB2. On db1 I have Tbale1 and on DB2 I have Table2.

I like to run a select statment:
Select Table1.*, Table2.* from Table1, Table2
Where table1.id = Table2.id
And Table1.user_name like '%jim%'

Both tables have same architecture.

Thanks for any help.

Abrahim

View Replies !

Select And UPDATE Statement Help! (Using SQL Server)
I have a table which I need to obtain data from but am having a problem with select statement.
Specifically, I have a table that has multiple records for a particular hostName where the name of the host is either a shortname (say "Larry") or a long name (say "Larry's").
       I need to display only the long names (NOT THE SHORT NAME records with the similar hostName).
      Select winsHostName, len(winsHostName) from winsData where winsClientIPAddress IN                     (SELECT winsClientIPAddress                      from winsData                     Group By winsClientIPAddress                     Having count(winsClientIpAddress) > 1)                     Order by winsHostName
   Which returns data
                    Name               Length
                    ATVDDR         6                   ATVDDR1        7
This is a s far as I can get but,
Now, I need to list all remaining table fields based on the Name with the longer length and this is where I run into trouble.
               The output should read per below with the remaining table fields which I cannot seem to extract with nested select statement
                            Name              IP Addr     Location
                            ATVDDR1       1.1.1.1     2ndFloor
I tried adding another GROUP BY by this gets me no where because I do not need to execute another aggregate in select statement. Maybe I need to use INNER JOIN
Please advise any assistance.


View Replies !

Imbedding A Select Statement Within An Update
I have the following list of fields in a table named call.CallID, CallGroupID, PersonID, ParentID,I have two records.23, 45, John, Null26, <Incorrect Value>, Jane, 23Now, I'm wanting to set the CallGroupID of record two, to that of theCallGroupID of record one.This is what I came up with, but it doesn't work.update call set CallGroupID = (Select CallGroupID from call whereCallID = ParentID) where ParentID is not nullAs you know the Select statment I have embedded is trying to pull up alist CallGroupIDs from records where the CallID = ParentID. What Iwant it to do is pull up the CallGroupID of the record where the CallID= ParentID.The CallID is a unique field, so there is only one such record in thetable.Any help?Thanks

View Replies !

How Do I Use Select Statement In Update Query
hi myself avii am developing one appliacaion in which i am using vb 6 as front end,adodb as database library and sql sever 7 as backend.i want to update one table for which i required data from other table. andiretrive data from second table by giving some condition. when i get data,then to update first table i need to use do while loop. instead of that iwant to use select statement directly in update query.plz give me some help.following is the my queries and its out putStrSql = ""StrSql = "Select * From SalesVchMaterialDesc where TransactionID=" &txtTransactionID.text & ""rsMName.Open StrSql, Conn, adOpenKeysetDo While Not rsMName.EOFStrSql = ""StrSql = "Update StockTable Set Outward=Outward - " &rsMName("Netweight") & ",OutwardQty=OutwardQty - " & rsMName("Qty") & "Where MaterialId=" & rsMName("Material_Name") & " and VoucherDate='" &Format(rsMName("VoucherDate"), "mm/dd/yyyy") & "'RsAdd.Open StrSql, Conn, adOpenStaticrsMName.MoveNextLooprsMName.Closeout put***main querySelect * From SalesVchMaterialDesc where TransactionID=848do while not loopUpdate StockTable Set Outward=Outward - 8.06,OutwardQty=OutwardQty - 1Where MaterialId=221 and VoucherDate='04/01/2004' and SMID=0loop

View Replies !

Dynamic Select/Update Statement Possible?
Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?

For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...

ALTER PROCEDURE u_sp_x
@semester int
AS
Select Semester@semester
From ThisTable

Just curious.

Thanks,
Steve Hanzelman

View Replies !

How To Convert SELECT Into UPDATE SQL Statement
Hello!

I am trying to write an update t-SQL statement from the following select statement:

SELECT     EduNextContacts.ssn
FROM         EduNextContacts Left JOIN
                      EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND
                      EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code
GROUP BY EduNextContacts.ssn
HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) +
SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2)

I tried many versions of writing my update statement with no luck. My most recent version is as follows:

UPDATE EduNextContacts

SET EduNextContacts.overLMLimit = 'Y'

FROM EduNextContacts Left JOIN

EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND

EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code

WHERE EduNextContacts.ssn = (SELECT DISTINCT EduContactsAuditChanges.ssn

FROM EduNextContacts Left JOIN

EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND

EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code

GROUP BY EduContactsAuditChanges.ssn

HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) +

SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2))

And gives the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Can anyone help shed some light on how to make an update statement from my SELECT query above?

Thanks in advance,
Harry

View Replies !

Multiple Select Statement In A Stored-proc?
I am using SQL sever 2k and C#.

There is a stored-procedure that it has multiple select statements as returned result set. How can I use SqlCommand.ExecuteReader to get all result set?

What if the multiple select statements is " FOR XML", how can I set all xml using ExecuteXmlReader?

I tried to use ExecuteReader or ExecuteXmlReader, but seems that I can only get back the result set of the first select statement, all others are messed up.

stored procedure example: NorthWind database:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create PROCEDURE dbo.getShippersAndEmployeesXML
AS

select * from Shippers for xml auto, elements
select * from Employees for xml auto, elements

RETURN @@ERROR

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

C# code example:

//set connect, build sqlcommand etc

XmlTextReader reader = (XmlTextReader)command.ExecuteXmlReader();
StringBuilder sb = new StringBuilder();
while(reader.Read()) sb.Append(reader.ReadOuterXml());

Thanks for your help.

View Replies !

Insert Statement With Multiple Select Statements
hi

first of all is it possible? if so, what am i doing wrong with this

INSERT into TB2

(

ClientCode,
EngagementCode,
EngagementDescription

)

SELECT
(SELECT dbo.tarCustomer.CustID
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),

NULL,

SELECT
(SELECT dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)

the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.

this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks

tibor

View Replies !

Basic SQL: Multiple AND/OR Nightmare In SELECT Statement
This is more of a basic SQL question rather than anything specific for T-SQL, but I assume someone here can help me...

I have a problem with properly combining a lot of AND and ORs in a SELECT statement in a stored procedure in order to get the desired results. The problem is that I want to have all results that fullfill all of the supplied conditions: InstitutionCode, CollectionCode, ScientificName, Locality (unless they are null, hence 'coalesce') and the Parentid, that can be in one of eight columns.

SELECT * FROM QueryView WHERE InstitutionCode = COALESCE(@museum, InstitutionCode) AND CollectionCode = COALESCE(@collection, CollectionCode) AND ScientificName LIKE '%' + @binomen + '%' AND Locality LIKE '%' + @locality + '%' AND ParentID1 = COALESCE(@taxparent, ParentID3) OR ParentID2 = COALESCE(@taxparent, ParentID2) OR ParentID3 = COALESCE(@taxparent, ParentID3) OR ParentID4 = COALESCE(@taxparent, ParentID4) OR ParentID5 = COALESCE(@taxparent, ParentID5) OR ParentID6 = COALESCE(@taxparent, ParentID6) OR ParentID7 = COALESCE(@taxparent, ParentID7) OR ParentID8 = COALESCE(@taxparent, ParentID8)

The current construction, however, gives me all results that fullfill either on of the four conditions, or the parentid in one of the columns. putting parentheses around parentid part gives me zero query results. I understand that the ORs should be restricted to the parentids and not the rest, but putting parentheses around parentid part gives me zero query results.

Has anyone got a good tip to help me resolve this puzzle?

View Replies !

Update 2 Tables In Single Statement
Is it possible to update 2 tables in a single t-sql statement?

If Yes, whats the syntax?

Update Table1,Table2 ... is not working

View Replies !

Update Multiple Varbinary Records With Single Sql Statement
I am renovating an existing application and am converting the existing passwords into hashed values using SHA1. I know how to compute the hashed values as a byte array for each record. What I don't know how to do easily is update all of the records i a single call to the database. Normally, I would just do the following:UPDATE HashedPassword = someValue WHERE UserID = 101;
UPDATE HashedPassword = someOtherValue WHERE UserID = 102;
...

What I don't know is what someValue and someOtherValue should be. How do I convert my byte array into string representation that SQL will accept? I usually execute multiple statements using Dim oCmd as New SqlCommand(sSQL, MyConn) and then call oCmd.ExecuteNonQuery().
Alternatively, I found the following code that uses the byte array directly but only shows a single statement. How could I use it to execute multiple statements as shown above?'FROM http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx

'2. Create a command object for the query
Dim strSQL as String = _
"INSERT INTO UserAccount(Username,Password) " & _
"VALUES(@Username, @Password)"
Dim objCmd as New SqlCommand(strSQL, objConn)

'3. Create parameters
Dim paramUsername as SqlParameter
paramUsername = New SqlParameter("@Username", SqlDbType.VarChar, 25)
paramUsername.Value = txtUsername.Text
objCmd.Parameters.Add(paramUsername)

Dim paramPwd as SqlParameter
paramPwd = New SqlParameter("@Password", SqlDbType.Binary, 16)
paramPwd.Value = hashedBytes
objCmd.Parameters.Add(paramPwd)

'Insert the records into the database
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()

View Replies !

Update Statement To Include Multiple Parameterised Input
I want to do an update query like the following:UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE (EmployeeNumber = @selectedusersparam)Is there some simple way to add the @selectedusersparam as value1,value2,value3 etc. or do I have to input it with this type of syntax:UPDATE dbo_tblUserDetails SET dbo_tblUserDetails.DeploymentNameID = 102WHERE (((dbo_tblUserDetails.EmployeeNumber)=value1 Or (dbo_tblUserDetails.EmployeeNumber)=value2));Help appreciated.Many thanks.

View Replies !

Updating The Same Column Multiple Times In One Update Statement
I have a single update statement that updates the same column multipletimes in the same update statement. Basically i have a column thatlooks like .1.2.3.4. which are id references that need to be updatedwhen a group of items is copied. I can successfully do this withcursors, but am experimenting with a way to do it with a single updatestatement.I have verified that each row being returned to the Update statement(in an Update..From) is correct, but that after the first update to acolumn, the next row that does an update to that same row/column combois not using the updated data from the first update to that column.Does anybody know of a switch or setting that can make this work, or doI need to stick with the cursors?Schema detail:if exists( select * from sysobjects where id = object_id('dbo.ScheduleTask') and type = 'U')drop table dbo.ScheduleTaskgocreate table dbo.ScheduleTask (Id int not null identity(1,1),IdHierarchy varchar(200) not null,CopyTaskId int null,constraint PK_ScheduleTask primary key nonclustered (Id))goUpdate query:Update ScheduleTask SetScheduleTask.IdHierarchy = Replace(ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.')FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0Query used to verify that data going into update is correct:selectScheduleTask.Id, TaskCopyData.Id, ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.'FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0

View Replies !

Select Statement Help:How Do I Query Two Tables
How do I Query two tables and minus the result to be displayed in a gridview. I will appreciate all the help that I can get in this regard. Find below my two select statement
1st Select StatementDim SelectString As String = "SELECT DISTINCT [Course_Code], [Course_Description], [Credit_Hr], [Course_Type], [Course_Method] FROM [MSISCourses] WHERE (([Course_Type] = Core) OR ([Course_Type] = Information Integration Project) "If radBtnView.Checked = True ThenSelectString = SelectString & " OR ([Course_Type] = 'Knowledge')"End IfIf chkGView.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Data Management')"End IfIf chkGView2.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'General')"End IfIf chkGView1.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Electronic Commerce')"End IfIf chkGView3.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Network Administration and Security')"End IfIf chkGView4.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Healthcare Information Systems')"End IfSqlDataSource3.SelectCommand = SelectString
2nd Select Statement"SELECT DISTINCT [Co_Code], [Co_Description], [Cr_Hr], [Co_Type], [Co_Method] FROM [StudentCourses] WHERE ([Co_Code] = StdIDLabel)"
my gridview<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="Course_Code" DataSourceID="SqlDataSource3" GridLines="Horizontal"><Columns><asp:BoundField DataField="Course_Code" HeaderText="Course_Code" ReadOnly="True" SortExpression="Course_Code" /> <asp:BoundField DataField="Course_Description" HeaderText="Course_Description" SortExpression="Course_Description" /> <asp:BoundField DataField="Credit_Hr" HeaderText="Credit_Hr" SortExpression="Credit_Hr" /> <asp:BoundField DataField="Course_Type" HeaderText="Course_Type" SortExpression="Course_Type" /> <asp:BoundField DataField="Course_Method" HeaderText="Course_Method" SortExpression="Course_Method" /> </Columns></asp:GridView>

View Replies !

Can You Have Multiple Output Parameters? Difference Between A Select Statement?
I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible?

Do I need just a select statement to do this? What is the difference between the output and select statements?

Thanks in advance.

View Replies !

UPDATE Statement Involving Tables In Different Databases
I want to create an UPDATE statement that updates a value in a table indatabase A based on the results of an inner join between tables eachresiding in deifferent databases.What is the correct syntax for doing this?The following should give you an idea of what I'm trying to do:UPDATE A.dbo.tblCarsSET A.dbo.tblCars.Car = 'Ferrari'FROMA.dbo.tblCars INNER JOIN B.dbo.tblHouses ONA.dbo.tblCars.RecID = B.dbo.tblHouses.RecIDWHERE (B.dbo.tblHouses = 'Mansion')

View Replies !

How To Join Tables From Different Databases In SQL Select Statement?
I have a basic sql statement, where I have a usersID, and I want to joing that usersID to another table in another database to get the users first and last names. How do I join across databases... each with a different connection string?
Here's what I want..
Select usersID from tableA in databaseA, and usersFirstName, usersLastName from table B in database B where the usersID from tableA = the usersID in tableb.

View Replies !

Creating A Select Statement With Subqueries To 3 Other Tables...
I have four total tables.Table One (Documents)- List of Documents. Each record has two fieldsrelated to this issue. First field (Document_ID) is the ID of thedocument, second field is the ID of the record (Task_ID) it isassociated to in Table Two.Table Two (Activities)- List of activities. Each record has two fieldsrelated to this issue. First field (Activity_ID) is the ID of theactivity, the second field (Group_ID) is the ID of the record it isassociated to in Table Three.Table Three (Groups) - List of groups. Each record has two fieldsrelated to this issue. First field (Group_ID) is the ID of the group,the second field (Stage_ID) is the ID of the record it is associated toin Table four.Table Four (Stages)- List of Event Stages. Each record has two fieldsthat is related to this issue. The first field (Stage_ID) is the ID ofthe stage of an event, the second record is the ID number associated tothe event. This last ID is a known value.20000024 = the Event IDI'm trying to come up with a list of Documents from the first tablethat is associated to an Event in the Fourth table.Query Analyzer shows no errors within the script. It just doesn'treturn any data. I know that it should, if it does what I'm wanting itto do.SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROMStages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C WHERE(C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE (B.Activity_ID= A.Activity_ID))))))= '20000024')

View Replies !

Joining Tables In A VIEW SELECT Statement
Wuddup,

I am trying to create a view that encapsulates some specific info from many different tables. I have about 30 tables all with exactly the same field names and field types. I want to take 3 of the fields from every table and put them together into one 'VIEW' so I can run more efficient queries. SQL however, doesn't let you 'combine' 2 columns from different tables into one column in the view. (making sense?)
I tried running a 'UNION' but you are specifically NOT allowed to run a union in a create view statement. Anyone have any ideas?

View Replies !

Dynamic Select Statement Using Parameterized SqlCommand Or Multiple Possible .CommandTexts
I'm trying to create an interface for filtering profiles from an SQLServer 2005 database using an html form. The form allows filtering based on a minimum level required in between one and four different columns. The first (and only mandatory) column to be filtered on has its name hard-coded into the base query. In trying to get the other three possible criteria to work, I've taken several approaches, all of which failed.The other three potential criteria are selected from a drop down menu on the form and ideally these choices are passed into a query to be used as column names. My first attempt looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" .... if ReqSkill1 <> "" then level1 = Convert.ToInt32(Request.form("minskilllvl1")) query = query & pickclmleft & ReqSkill1 & pickclmright1 cmd.Parameters.Add("@ReqSkill1", SqlDBtype.text) cmd.Parameters("@ReqSkill1").value = ReqSkill1 cmd.Parameters.Add("@level1", SqlDBtype.int) cmd.Parameters("@level1").value = level1 end if above If statement was repeated for 2nd and 3rd optionsSecond approach was to remove all parameters from sections of the query that were appended onto the original statement. This involved lots of strings containing AND clauses with hard-coded column names which were appended on when the corresponding option was selected in the form. Code looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" ASPqry = " AND (ASPlevel >= " try con = new SqlConnection() con.ConnectionString = â€œ**************string was correct****************â€? cmd = new SqlCommand() cmd.Parameters.Add("@english", SqlDBtype.int) cmd.Parameters("@english").value = english if ReqSkill1 <> "" then if ReqSkill1 = "ASPlevel" then query = query + " AND (ASPlevel >= " level1 = Convert.ToInt32(Request.form("minskilllvl1")) if level1 = 0 then query = query + "0)" end if if level1 = 1 then query = query + "1)" end if if level1 = 2 then query = query + "2)" end if if level1 = 3 then query = query + "3)" end if end if end ifFinally when this too failed, I created four entirely separate queries, detected how many criteria were used, and used the appropriate query, passing necessary skill level in as a parameter. I'll provide code if needed here. Queries were written as strings and then used to set the CommandText property for an SqlCommand variable. I think it's important to note that in all cases the most basic version of the query worked. In the first, if only the first criteria was used the statement executed fine. Same in the second. In the third, whatever query could be assigned first (even though only one could be assigned because of logical structure of if statements) worked and none of the others would. This last case was tested even with completely hard-coded queries that SQL Server 2005 validated as correct and would run. Any help is greatly appreciated. Will post as much code as people want/need, and if I can get any one of these methods working I'll be thrilled. I have no need for all three. A.S. Moser

View Replies !

SELECT Statement With Multiple Criteria That Returns The Criterion Matched
Hello:
I need assistance writing a SELECT statement. I need data from a table that matches one (or more) of multiple criteria, and I need to know which of those criteria it matched. For instance, looking at the Orders table in the Northwind database, I might want all the rows with an OrderDate after Jan 1, 1997 and all the rows with a ShippedDate after June 1, 1997. Depending on which of those criteria the row matches, it should include a field stating whether it is in the result set because of its OrderDate, or its ShippedDate. One way of doing this that I've already tried is:
SELECT 'OrderDate' AS [ChosenReason], Orders.*FROM OrdersWHERE OrderDate > '1-1-1997'UNIONSELECT 'ShippedDate' AS [ChosenReason], Orders.*FROM OrdersWHERE ShippedDate > '6-1-1997'
In my application, scanning a table with thousands of records for five sets of criteria takes a few seconds to run, which is not acceptable to my boss. Is there a better way of doing this than with the UNION operator?
Thank you

View Replies !

My Update Statement Isn't Working But Select And Insert Are. What's Wrong?

here is my code:

Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())

cn.Open()

Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()

adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products
set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" &
textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)

cn.Close()

Response.Redirect("database.aspx")

it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?

View Replies !

Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View Replies !

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys,
I have the query below (running okay):

Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:

Field01           Field02

-------------          ----------------------

192473           8461760

192474           22810

Because other reasons. I need to modify that query to:

Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

----------------------

22810
8461760

And what I need is (without showing any other field):

Field02

----------------------

8461760
22810

Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View Replies !

Select Statement (Advvance Button Insert, Update, Deltete
Hello All
I have had asked the same question in another post, i didnt get answer to it i might have had asked it wrongfully

Soo the question is: When creating a SQLDataSource in the wizard you get to the pont where you select the option . It says that by using this datasource you can select to update delete and insert. So my question is if i am creating a select statement to reterieve the data from the Table, then what does it do it do if my intention is to only reterie the data. Or what is the other way that it could be helpful to me ??

thanks all I hope it make sence, if not I wrill write another post to bring step by step info into it.

View Replies !

How To Write Select Statement Inside CASE Statement ?
Hello friends,
      I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
    CASE E.EmployeeType
        WHEN 1 THEN
            select * from Tbl1
        WHEN 2 THEN
            select * from Tbl2
        WHEN 3 THEN
            select * from Tbl3
    END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View Replies !

Help With Delete Statement/converting This Select Statement.
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID

I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:

SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180

View Replies !

GridView Based On SQLServerDataSource Using A Select Union Statement, Impacts On Update And Insert?
I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following):

SELECT FirstName, LastNameFROM MasterUNION ALLSELECT FirstName, LastNameFROM CustomORDER BY LastName, FirstName
I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated!
Thanks,
Randy

View Replies !

Using Select Statement Result In If Statement Please Help
Hello
How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement.
<% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>"
ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)">
<SelectParameters>
<asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" />
</SelectParameters>
</asp:SqlDataSource>any help would be appreciated

View Replies !