If I want to select certain values from a table where date is equal to something and the second field is null,would I go.
select * from table where date = 'something' and Sent = null or something else because if I do it this way I get nothing as a result but if I scroll down I can see that I have null values.
thanks
I have an sql data source..select command is something like this.
Select password from users where username = Username.Text
how can i retrieve the password value and save it into a string?! to make a comparison between password value and a textbox Password.Text
i know its a silly question but i took me long time and i still can't find the ans
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?
Hi,
I have written a basic UPDATE statement to update two fields in one table using a where clause to identify the record i wish to update. i.e.
UPDATE TableName SET field1=1234, field2='' WHERE primary_key=N'1234';
When i run the statement, in the results window i get a value returned that is equal to the total number of records in that table!! i.e.
(No column name)
--------------------------
588061
but when i check the table, indeed only one record has been updated as expected (and i have confirmed this by using SELECT @@ROWCOUNT straight after)
My question is, why does it do this?? i paniced at first as i thought i had updated ALL rows, but luckily this seems not to be the case.
And can i turn this off??
Many thanks,
Martin
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
Hi,
I'm new to SQL and learning, so please forgive my question....
I have two tables; Authors and Books with the fields as shown below. Each author will have 1 or more books listed in the Books table.
I need a SELECT statement that will GROUP all authors and return 1 record per author with the LAST published book fields for each author (most recent PubDate) from the Books table. All fields on both tables should be returned:
AuthorID, FullName, Country, BookID, BookName, PubDate
Can anybody help me w/ this?
Thanks in advance!!!!!!!!!
George.
Authors
=======
AuthorID
FullName
Country
Books
=======
BookID
AuthorID
BookName
PubDate
Hello All,
I have what i believe to be a pretty basic SQL question. I have 2 tables, One called participant, and another called cnAvailableParticipant_List they are related with a field called userid I would like to select the firstname, lastname, userid, fields from the participant table and the dnnuserid field from the cnAvailableParticipant_List table where the users have a record in the participant table but do NOT have a record in the other table. Basicially i want to select all the users who are essentially not checked in. So far i have:
SELECT p.ParticipantID as ID, p.FName as FName, p.LName as LName, ap.DNNID FROM Participant p, cnAvailableParticipant_List ap
WHERE
ORDER BY LName, FName
can someone explain to me what i need in the where clause and why. I don't just want the answer, i would like to know why it works the way it does as well. Sorry for the basic question but im new to SQL
Thanks all help is appreciated,
John
Basically, im being asked Which is the maximal budget of projects in which employee 'John Smith' is involved?
I got a nested query done kinda right, like this
Select max(Budget)
From aProject
Where (aProject.Pid = (
Select Pid
From aWorks_For
Where aWorks_For.Eid = (
Select Eid
From aEmployee
Where Ename = 'John Smith')))
But when an employee is working on more then one project I get this error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Heres my Create Table Statements
Any help would be appreciated
CREATE TABLE EMPLOYEE ( EId INT, EName VARCHAR(20), Salary INT, PRIMARY KEY (EId) )
CREATE TABLE WORKS_FOR ( EId INT, PId INT, PRIMARY KEY (PId, EId) )
CREATE TABLE PROJECT ( PId INT, PName VARCHAR(20), Supervisor VARCHAR(20), Budget INT, PRIMARY KEY (PId) )
Hi! I am trying to do something that I would think should be rather simple.I have a SQLDataSource that has a simple select statement.I have a String Variable st_tempI know my SQLDataSource returns only one value, and it's a string.HOW DO I DO somehitng like st_temp=SQLDataSource.Select;I am getting nothing but errorrs Thanks in advance Dan
View 4 Replies View RelatedHi,I feel stupid for posting this question but I cannot find out how to create an SQL statement to return records that have been added today.My database table has a field called calldate which hold the date and time of the record added to the database i.e. "02/08/2005 16:55:41".Please can someone let me know how I can search to find all records added today. I thought it would be something like it, but I assume I need to format the calldate so it's just 02/08/2005.Select *From TableWhere CallDate = GetDate()ThanksBrett
View 9 Replies View RelatedI am learing VWD as I build a site and while I can use the various controls (GridView, FormView) to set up operations and retrieve/display database information, I need to be able to codebehind to do SQL operations for retrieving data to fill paper forms and to modify the database. After I retrieve data from the database, I need to do a lot of computation befor I build a paper quote that is emailed to a customer. I have written a subroutine to which I pass my sql query statement and I can add a new entry and edit an existing entry. The problem is a simple query to read an existing database entry.
If I write a simple command : Cmd = SELECT PartNumber, PartName, PartsAvailable FROM Inventory WHERE PartNumber = "P7226" , the command is executed but the return data falls on the floor somewhere as I dont know how to code to put the results in an array or in fact, have any idea on how to find the returned data. Can anyone help with this simple(?) problem? My sub that handles the sql command is shown below.
I have also been looking for the way to code in VB to cause existing controls like GridViews to access, edit, and insert but I havent been able to find a paper or tutorial. If anyone has seen this information, it would be helpful also.Protected Sub CallSQL(ByVal Cmd As String)
Dim conn As New SqlConnection("Data Source=.SQLExpress;AttachDbFileName=C:Documents and SettingsaMy DocumentsVisual Studio 2005WebSitesWebSite5App_DataPINEMgt.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True") 'Data Source=.SQLEXPRESS;AttachDbFilename="C:Documents and SettingsaMy DocumentsVisual Studio 2005WebSitesWebSite5App_DataPINEMgt.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True)Dim sql As String = String.Empty
sql = Cmd
'Open the connection to the database and execute he SQL
conn.Open()Dim command As New SqlCommand(sql, conn)
command.ExecuteNonQuery()
conn.Close()
End Sub
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies View RelatedOk I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
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 2 Replies View RelatedHi 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.
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
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
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
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
View 2 Replies View RelatedSELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
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
Hi,
I am a newbie to this site and hope someone can help....
I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is:
if(TL_flag= '1', "yes") as [Trial Leave]
it is coming up with an error.... I can use Select case but I should not need to as this should work?
Any ideas?
I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?
Thanks,
Sam
I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).
Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.
SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees,
(
SELECT COUNT(gifts.donor_id) AS Count2
FROM gifts
WHERE gifts.donor_id = ed.id_number
) subquery2
[code]....
I need to write a select statement that take the upper table and select the lower table.
View 3 Replies View Related
Hi All,
Can some one point me in the right direction in how to construct my SQL query within my cursor?
I Have got a cursor which i am using to iterate through a table, What i am trying to do is in my statement(used to open the cursor) is compare 2 tables (the one which my cursor is iterating) to see if there is a matching row in the other table (using both tables ID's Like So:
SELECT column_List
FROM Table1
WHERE Table1_id = Table2_id
so for each row my cursor checks if there is a corresponding match in table2... but i would like to write to an error log
and do other statements if there is no match
how do i add this condition to my statement either using an if...else statement proceeding to the next row?
here is the statment i attempted to write:
SELECT column_List
FROM table1
WHERE
Table1_id = Table2.id
now i want to incoporate the statements below into the statement above as a condition when table1.id <> table2.id
IF table1.id <> table2.id
BEGIN
SET @DebugMessage = 'data not live.'
RAISERROR (@DebugMessage, 16, 1) WITH LOG
END
essentially what i am trying to sayin my statement is:
go to the first row
check if it has a match in table 2,
if there is no match execute a number of statements such as error loging e.t.c
go to the next row
repeat the previous statements
...i also looked through some Case...When statements am just not sure how to put in the condition
thanks in advance
I am a newbie to SQL. I have a table (AenComponent) with three columns (State1, State2, State3). Each column has a set of numeric values. I would like to get a number count from all of the rows that contain the value of 1, no matter which column they are in.
I have tried
SELECT COUNT(*) AS Expr1FROM AenComponentWHERE (State1 = 1) OR (State2 = 1) OR (State3 = 1)
but it does not give me an accurate count. Any help would be appreciated.
thanks
rusty
I'm trying to get a list of clients and their sum of total pmts, their pmt level, and pmt level description by date range.
Here is what I’ve tried and it will not work. I need to do this without using temp tables.
SELECT C.ClientID, SUM(P.AmountPaid) AS SumOfpmts, tblpmtLevels.pmtLevel, tblpmtLevels.DescriptionFROM tblPmts AS PL INNER JOIN tblPmtReceipts AS P ON PL.PmtID = P.PmtID INNER JOIN tblClients AS C ON PL.ClientID = C.ClientID INNER JOIN tblPmtLevels ON SUM(P.AmountPaid) >= tblPmtLevels.PmtLevelLow AND SUM(P.AmountPaid) <= tblPmtLevels.PmtLevelHighWHERE (P.PaymentDate BETWEEN @Start AND @End)GROUP BY C.ClientID
Please provide any help you can,
Hello, Is there a SQL SELECT Statement that can remove the time part in my DataTable. see example below
This is what I have in the first column 0 - MM/dd/yyyy 12:00:00 AM
This is what I want - MM/dd/yyyy
I would rather do it in the SELECT statement instead of doing it in my DataTable using the FORMAT function.
Thanks
Steve
I have a stored procedure that works like this
getCountryByIPAddress @ipaddr='1.2.3.4'
returns a single 2 char column.
I want to use it in a select statement similar to:
select customername, @country=(exec getCountry @ipaddr=customer.customeripaddress) from customer
I'm intend to receive a list of customer names and the 2 char result of GetCountry based on the customeripaddress field of the customer table. What I get is a syntax error near Exec and then another one near '.'
Is there another way to accomplish this that I am not seeing?
Ryan
Hi,
I am trying to select only the most recent record added to a table per user. Say the table has a userid, date, and a color and may look like this:
2 5/06/07 red
8 5/3/07 grey
2 5/13/07 green
4 4/30/07 orange
8 4/26/07 yellow
And I want, in any order:
2 5/13/07 green
8 5/3/07 grey
4 4/30/07 orange
Thanks for any help.
Greg