Select Statement For User Pyramid
I have a user table for an organization that can best be described as a pyaramid. We are using the terms parent and child to define the relationship between users. Each user is a child under a specific parent; accept for the top person who is not a child under anyone. Each user can be a parent over many children.
User A has a parent - User B
User B has a parent - User C
User C has a parent - User D
User D has not parent
I want to do is create a select statement that would return all the parents for a particular user. For instance, if I ran the select statement for User A, it would return:
User A
User B
User C
User D
If I ran it for User C, it would return:
User C
User D
Ideas anyone on how I can accomplish this.
View Complete Forum Thread with Replies
Related Forum Messages:
First Time SELECT CASE Statement User
Hi, I have created a login page (webform1) that enables me to enter my user number and password and if correct it re-directs to webform 2 where all data related to the usernumber that was entered in the login page, appears. Now I want to be able to set different re-direct pages dependant upon different users as some may require more privileges then others. As for my query I have 2 types of users: staff and managers. I want to set the login page so that if staff logins in it goes webform 3 and if manager logins in it goes webform 4. My table in the Sql database is called Users & the fields are: unumber(pk), pwd, userRole, forename, surname. I have been advised to use CASE statements, yet I do not know how to use them. Below have left my code of a simple login without the userRole validation. Please help! Sub cmdLogin_ServerClick If ValidateUser(txtUserNumber.Value, txtUserPass.Value) Then Dim tkt As FormsAuthenticationTicket Dim cookiestr As String Dim ck As HttpCookie tkt = New FormsAuthenticationTicket(1, txtUserNumber.Value, DateTime.Now(), _ DateTime.Now.AddMinutes(30), chkPersistCookie.Checked, "your custom data") cookiestr = FormsAuthentication.Encrypt(tkt) ck = New HttpCookie(FormsAuthentication.FormsCookieName(), cookiestr) If (chkPersistCookie.Checked) Then ck.Expires = tkt.Expiration ck.Path = FormsAuthentication.FormsCookiePath() Response.Cookies.Add(ck) Dim strRedirect As String strRedirect = Request("ReturnURL") If strRedirect <> "" Then Response.Redirect(strRedirect, True) Else strRedirect = "webform1.aspx" Response.Redirect(strRedirect, True) End If Else Response.Redirect("webform3.aspx", True) End If End Sub
View Replies !
Using User Input As Part Of A SELECT Statement
I know this is an easy one and I know I've read it somewhere, but I can't seem to write the correct format to run correctly. I am trying to build a SELECT statement base on the selected values of a dropdown list on a webform. The selected values will be part of the Table name.. ("client_info" & location_option.selecteditem.value) Can someone show me the correct syntax for adding a form variable into a SELECT statement? Thanks
View Replies !
Counting A Pyramid Db
hiya all, I got a table that is represented as a B-Tree, it has a one to many relation with its self ------------ | UserID | ------------ |FatherID| ------------ UserID = FatherID it looks like this o /// \ ooo ooo //\ ///\ oo ooo ooo I want to get all the nodes that has 256 children and grand children and each node is limited to have 6 nodes under it. any ideas ? the DB isn't filled with data yet so we can add more fields to the table if it helps. I could write a trigger that increments a counter in each node but it will be a recursive trigger so it can go from the child to its father and the father to its father upating them and so on. regards Ahm
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
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...
View Replies !
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
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 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 !
Is There A Way To Show A User-friendly Error When User Did Not Select A Required Parameter ?
Hi all, I was wondering whether there is a way to show the user an error when the user did not select a specific parameter. In my case, I have two optional parameters. The user has to select either one to view the report. If the user does not select either one, I would like to show some sort of an error page indicating so. However, all i am getting is a complete blank with no report in sight. I as a developer know this is an error as a result of missing parameters, but i was wondering whether I could produce a page or direct it to a page so that the user doesn't go all horrified at an empty page ? Thanks Bernard
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 !
Select Statement Problem - Group By Maybe Nested Select?
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 Replies !
SQL Select Statement To Select The Last Ten Records Posted
SELECT 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.
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 !
If STATEMENT Within Select Statement Syntax
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?
View Replies !
Name In CREATE USER Statement?
Hi. I was wondering if I could have a query like this: CREATE USER 'Firstname Lastname' FOR LOGIN 'DOMAINuser' WITH DEFAULT_SCHEMA=[dbo] The combination of firstname and lastname does not seem to work. I have been using: sp_grantdbaccess 'DOMAINuser', 'Firstname Lastname' but i cannot specify DEFAULT_SCHEMA with that sp. Any suggestions?
View Replies !
Using IF...Else Statement SELECT Statement
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
View Replies !
SQL Select Statement
I have a database that is constantly being added to... what im trying to do is select the 10 most recent entries EXCEPT for the very most recent. Selecting the 10 most recent is something I can do.... 'select top 10 * from tablename order by id desc' - but how do I leave out the most recent? SQL Server 2005
View Replies !
SELECT Statement
Hi,I'm sorry for the lame question, but I''m a newbie at C#.With the code that follows my signature, I'm trying to retrieve a single value from a table.The query is ok, I've tested manually.But I get the error:"The name userPassword does not exist in the current context".I realize it's a matter of scope, but I need to have the variable value available there.Furthermore, I think that I'm getting no result from the database with my code, because if I put the Response.Write(userPassword) line inside the while cicle, nothing is displayed.Any help would be appreciated.Warm Regards,Mário Gamito--SqlConnection myConn = new SqlConnection("user id=sa" + "password=secret" + "server=192.168.1.4" + "database=workers");try{myConn.Open()}catch (Exception e){Console.WriteLine(e.ToString());}try{SqlDataReader myReader = null;SqlCommand myCommand = new SqlCommand("SELECT password FROM dbo.users WHERE email = 'gamito@foobar.lan'", myConn);myReader = myCommand.ExecuteReader();while(myReader.Read()) { string userPassword = myReader["password"].ToString()); }}catch (Exception e){ Console.WriteLine(e.ToString());}myConn.Close();Response.Write(userPassword);
View Replies !
Max Select Statement
Hi Guys, Having a little problem, can someone take a look at this... Thank You... Trying to get the Max(WebNameTitle) From Max(NumerID) Example code below throws error : I need the Max Value from the Column( WebNameTitle <---nvarchar) and the Max Value from the Column (NumberID <--int) New SqlCommand("Select Max(WebNameTitle) FROM ComBooks WHERE Max(NumberID) = MaxNumberID", conCommerce)
View Replies !
Select Statement In ASP.NET
hello all, I need to SELECT data in my ASP.NET web service from my MSSQLExpress database. I checked to INSERT data into it and it worked well , and here is the code of my insert: 1 <WebMethod()> _2 Public Function SaveNumName(ByVal UserNum As Integer, ByVal UserName As String)3 Dim DS As New SqlDataSource4 5 DS.ConnectionString = "Data Source=.SQLEXPRESS;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=tstSQL"6 ' DS.ConnectionString = ConfigurationManager.ConnectionStrings("tstSQLConnectionString1")7 DS.InsertCommandType = SqlDataSourceCommandType.Text8 DS.InsertCommand = "insert into tbl1 (number,name) values (@number,@name)"9 DS.InsertParameters.Add("number", UserNum)10 DS.InsertParameters.Add("name", UserName)11 12 DS.Insert()13 14 Return 115 End Function The previous code worked well for the insertion of data, but when I tryed the same technique with the selection of data, it didn't work, and here is my code for the select: 1 <WebMethod()> _2 Public Function GetName(ByVal Number As Integer)3 Dim Name As String4 'Dim ConnStr As String = "Data Source=.SQLEXPRESS;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=malalation_tstSQL"5 Dim GV As New GridView6 GV.Visible = False7 Dim DS As New SqlDataSource8 DS.ConnectionString = "Data Source=.SQLEXPRESS;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=tstSQL"9 10 DS.SelectCommandType = SqlDataSourceCommandType.Text11 DS.SelectCommand = "SELECT name from tbl1 where number = 1"12 ' DS.Select()13 GV.DataSource = DS14 Name = GV.Rows(0).Cells(1).Text15 Return Name16 End Function I think that the problem is with storing the result of the SELECT satatement, so what do you think??
View Replies !
Need Help With SELECT Statement
Hi, I have a table (SQL 2000) that holds inside login records of each system. All the systems logging 0-50 times each day. I need to create “Last Login� report that shows as following: SystemID LastLogin System001 2008-04-23 21:14:49 System002 2008-03-12 21:15:06 System003 2008-01-20 22:14:15 System004 2008-04-23 22:14:24 System005 2008-02-23 22:14:42 Need help with SELECT statement Thanks in advance. Alex
View Replies !
Select Statement
Hi Guys, I have some problem with sqlquery can any one help? Here is my statement strSQL=strSQL & "employee= "Select empname from tbl_emp where empname= strUname" when executing it says Expected end of statement Thanks
View Replies !
Select Statement Help
Hello I only need to get the row from InvoiceHeader where branchID = 116The problem is I'm also getting the rows from InvoiceDetail where branchID is stored how will I solve this? Here's the code. SelectBranch.BranchLocation,Receipt.InvoiceNo,Receipt.AmountPaidFrom ReceiptINNER JOIN InvoiceHeader ON Receipt.InvoiceNo = InvoiceHeader.InvoiceNoINNER JOIN InvoiceDetail ON InvoiceHeader.InvoiceNo = InvoiceDetail.InvoiceNoINNER JOIN Branch ON InvoiceDetail.BranchID = Branch.BranchIDWhereBranch.BranchID = 116
View Replies !
Select Statement
Hi all,Can u plz tell me what is wrong with this select statement "select sku from products where cat='" & catitems.SelectedValue.Replace("'", "''") & "' and subcat is nothing" plz help regards
View Replies !
Help Me With A SELECT Statement, Please
hello i have two tables: ProductComments (CommentID, ProductID, UserName, Comment, Date) and Users (UserName, City, Age) i need to SELECT * FROM ProductComments where ProductID = @ProductID, and select from Users Table info about the users who commented i need some help, because i'm not so good at SQL yet:( thank you
View Replies !
Select Statement
The following Select statement shows "distinct l.userid" for the available c.id. I would like to see all the "c.id" even if "distinct l.userid" are NULL. select c.id, count(distinct l.userid) from Companys c left join labvalues l on c.id = l.companyid where l.labdate>='01/01/2007' and l.labdate <'02/01/2007' and c.id in (1,2,3,4,5,6,7) Output: 1 12 2 13 7 25 Expected Output: 1 12 2 13 3 NULL 4 NULL 5 NULL 6 NULL 7 25 PLEASE HELP......
View Replies !
Need Help With This Select Statement
I have a table called fund and a another table called FundPerformance.. and they both are linked together using FundId.. But suppose if a fund doesnt have any performance information.. It doesnt have a entry in the FundPerformance table but has a entry in the Fund table... I have written select statement that would give me the results.. But suppose if that particular fund is not present in the fundperformance table that fund doesnt show up in the select query.. So how can i get it.. I tried Inner Join, Left Outer Join and Right Outer join but it doesnt show up at all.SELECT pf.FundId, dbo.udf_Quarter(Getdate()) PeriodId, f.FundName, CASE WHEN pf.PlanFundDisplayName IS NULL THEN f.ShortName ELSE pf.PlanFundDisplayName END FundNames, f.InvestmentPolicy, f.FundClassCd, c.ClassName, c.ClassDefinition, p.YearToDate, p.OneYear, p.ThreeYear, p.FiveYear, p.TenYear, p.SinceInception, f.ExpenseRatio, f.PortfolioManager, f.AssetAllocationCashPercent CashAllocPct, f.AssetAllocationEquityPercent EquityAllocPct, f.AssetAllocationConvertiblesPercent ConvertAllocPct, f.AssetAllocationFixedIncomePercent FixedIncAllocPct, f.AssetAllocationOtherPercent OtherAlloc, p.TotalNetAssets, p.AsOfDate FROM PlanFund pf Right Outer JOIN Fund f Right Outer JOIN FundClass c ON f.FundClassCd = c.FundClassCd Right Outer Join FundPerformance p on f.FundId = p.FundId ON f.FundId = pf.FundId --Left Outer JOIN FundPerformance p --ON pf.FundId = p.FundId WHERE pf.PlanId = @PlanId AND p.PeriodId = dbo.udf_Quarter(Getdate()) --PeriodId --@PeriodId ORDER BY pf.FundDisplayOrder any help will be appreciated Regards, Karen
View Replies !
Regarding Select Statement
How to select two different fields from two different tables depending on the ID in a query. For Example: A from table1 and c from table 2 must be selected depending on the ID of table1 and Id of table2. please help me.
View Replies !
Help Me With A SELECT Statement Please
hello i am trying to make a select statement between two tables and i don't know how to start doing that i have two tables: UserComments where i store each comment made by all users and the Pictures table, and in one of his rows "LastComment" i store the userName -varchar(255)- of the user who last commented that picture i need to make a select statement with one input parameter (userName of the logged in user) and two output parameters(int) (@totalComments - would come from the UserComments table, and @lastComments - would come from the Pictures table) @totalComments - i need to COUNT how many comments that user has made from the UserComments @lastComments - count at how many pictures that user is last commenter from the Pictures there is no relation between these two tables, i can make two stored procedures one for each COUNT but i think is stresfull for the database please help me, thanks
View Replies !
Use If Statement In Select Sql
Hi. i have the database with status field (true,false) in access i have the cod in vb.net strSqlQuery="select id,name,status FROM... how i can use the IF in this code,as strSqlQuery="select id,name, if (status=true then status="ok" else "No") FROM table.... thanks
View Replies !
Select Statement
Hi, I dont know whts wrong with this plz help??? strQry = "Select classlevel, classcharge, classCost, classcharge - classcost from cart_shipping_chargeLevel order by [classlevel] assending" plz help regards,.
View Replies !
Select Statement
Hi there, I have a table which has lab values.... id, userid, companyid, testid, testname, value, labdate I want to write a select statement which will give me, fixed 5 tests for some userids. Something like..... select testid=1 testid=2 testid=3 testid=4 testid=5 from labvalues where userid in (11,12,13,14,15,16,17,18,19,20) PLEASE HELP THANKS
View Replies !
Select Statement
Hello, I have the following query: Dim sql As Stringsql = "SELECT Events.EventID, Events.VenueID, Events.EventName, Events.EventTypeID, Events.EventOnSale, Events.VALink, "sql = sql & "Venues.VenueID, Venues.Name, "sql = sql & "EventTypes.EventTypeID, EventTypes.Name as EventTypeName, "sql = sql & "EventDateTimes.UID, EventDateTimes.EventDateTime "sql = sql & "FROM Events "sql = sql & "INNER JOIN Venues ON Events.VenueID = Venues.VenueID "sql = sql & "INNER JOIN EventTypes ON Events.EventTypeID = EventTypes.EventTypeID "sql = sql & "INNER JOIN EventDateTimes ON Events.UID = EventDateTimes.UID " eThe EventDateTimes table can contain multiple records that match with the Events table. Is it possible to adjust this query so that only one record is returned per event? Right now, if there are like 3 records in the EventDateTimes table that match the UID in the Events table, then I will see the same event name outputed 3 times. Hope it makes sense... Thanks!
View Replies !
Help With Select Statement
Hi, i've run into a problem with a nested query. Consider this:SELECT VehicleRef, Type, Manufacturer, Model, Derivative, isLimited, Term, MilesPA, CH FROM vwVehicles WHERE type='Car' AND manufacturer='Audi' AND model='A3'I get the following results1 Car Audi A3 1.6 Special Edition 3dr 0 2 10000 229.991 Car Audi A3 1.6 Special Edition 3dr 0 2 10000 252.991 Car Audi A3 1.6 Special Edition 3dr 0 3 10000 219.991 Car Audi A3 1.6 Special Edition 3dr 0 3 10000 242.991 Car Audi A3 1.6 Special Edition 3dr 0 2 20000 252.991 Car Audi A3 1.6 Special Edition 3dr 0 2 20000 296.991 Car Audi A3 1.6 Special Edition 3dr 0 3 20000 241.991 Car Audi A3 1.6 Special Edition 3dr 0 3 20000 291.991 Car Audi A3 1.6 Special Edition 3dr 0 2 30000 274.991 Car Audi A3 1.6 Special Edition 3dr 0 2 30000 340.991 Car Audi A3 1.6 Special Edition 3dr 0 3 30000 264.991 Car Audi A3 1.6 Special Edition 3dr 0 3 30000 339.992 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 10000 227.992 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 10000 249.992 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 10000 220.992 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 10000 244.992 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 20000 253.992 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 20000 298.992 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 20000 244.992 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 20000 292.992 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 30000 279.992 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 30000 344.992 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 30000 267.992 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 30000 339.99What I need is to only retrieve each vehicleID once, but with the best (lowest) CH price, I also need the term and mileage that corresponds with that price.I tried thisSELECT VehicleRef, Type, Manufacturer, Model, Derivative, isLimited, Term, MilesPA, CH FROM vwVehicles WHERE type='Car' AND manufacturer='Audi' AND model='A3' AND CH = (SELECT MIN(CH) FROM vwVehicles WHERE type='Car' AND manufacturer='Audi' AND model='A3' AND ch >=1 AND ch <=1000)This only returns 1 line though. Is there anyway to get the result I want using one query?Thanks
View Replies !
Select Statement Help
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
View Replies !
Using An SP In A Select Statement
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
View Replies !
SQL SELECT Statement
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
View Replies !
Need Help With Select Statement
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,
View Replies !
SQL SELECT Statement
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
View Replies !
Help With Select Statement
Edited by moderator XIII, please don't use [ code ] tags around your code. Instead change your Profile | Site settings to use the Rich editor (with code support) to insert colorized code and line numbers: I have a select statement that follows: SELECT Events.legendID AS Events_legendID, Units.unitID AS Units_unitID, Units.msbbID, Units.belongsTo, Units.name AS unitName, Legend.legendID AS Legend_legendID, Legend.Color, Legend.Name legendName, Legend.Active, Events.unitID AS Events_UnitID, Events.userID AS eventUserID, Events.startDate, Events.endDate, Events.eventID, Events.TitleFROM Events INNER JOIN Units ON Events.unitID = Units.unitID AND Events.unitID = Units.unitID INNER JOIN Legend ON Events.legendID = Legend.legendIDWHERE startDate BETWEEN convert(datetime, '4/01/2006') AND convert(datetime, '6/30/2006')ORDER BY unitName ASC; This code works great with the exception of the events that start and end outside of the quarter dates I am entering. For example if there is an event that start in January and ends in August then I need to show that event in the 1st quarter, 2nd quarter and 3rd quarter. any help on how to get those events that start and end outside the 'active' quarter BUT pertain to the active quarter? thanks in advance!
View Replies !
Need Help For Select Statement...
Hi Frinds, I need help in select statement.... I have two tables table1 (vp) and table2 (pac). The table2 may have multiple rows or one or NONE per permit_application_id. But, the table1 have all permit_application_id(s) and always single row per permit_application_id. The table2 (apc) have two columns "comment" and "authored_date". I want to display some columns from table1(vp) along with "StringComment" from table2 (apc) where, StringComment should be concatinated with Comment and authored_date from table2. If the table2 has multiple rows, they should be displayed as one string StringComment. Here is my code. I know that, the table2 should be in a loop. But, I couldn't able to do it. I am pretty sure, someone might did this kind of work. I appreciate you all if modifiy and send the response ASAP. Thanks in advance. select vp.Annual, vp.Permit_Application_ID as Permit_Number,vp.start_date, vp.end_date, vp.issued_date, '{' + pac.comment + '} on {' + convert(varchar, pac.authored_date) + '};' AS COMMENT from table1 vp FULL OUTER JOIN table2 pac ON VP.PERMIT_APPLICATION_ID = PAC.PERMIT_APPLICATION_ID where vp.status='ISSUED' AND (ISSUED_DATE BETWEEN @start_date AND @end_date) ORDER BY VP.PERMIT_APPLICATION_ID Please reply or send the response to mysgupta@hotmail.com. Thanks. Srini.
View Replies !
Need Help With My SQL SELECT Statement
I have the following tables (with important fields) that all need to be joined onto my Orders table: Orders - PreNotesID, PostNotesID, StatusID, ReportID, PropertyTypeIDOrderNotes - NotesID, NotesDescStatus - StatusID, StatusDescOrderTypes - ReportID, ReportDesc, PriceIDPriceCodes - PriceID, PriceDescPropertyTypes - PropertyTypeID, PropertyTypeDesc Is the following SELECT Statement going to do what I need? The main thing that I am not sure about how to Join PriceCodes ON orderTypes then join both of them ON Orders "SELECT * FROM Orders As o JOIN OrderNotes As on ON o.NotesID = on.PreNotesID " & _ "INNER JOIN OrderNotes As on2 ON o.NotesID = on2.PostNotesID " & _ "INNER JOIN OrderStatus As os ON o.StatusID = os.StatusID " & _ "INNER JOIN OrderTypes As ot ON o.ReportID = ot.ReportID " & _ "INNER JOIN PriceCodes as pc ON o.PriceID = pc.PriceID " & _ "INNER JOIN PropertyTypes as pt ON o.PropertyTypeID = pt.PropertyTypeID " & _ "ORDER BY o.OrderDate DESC" TIA, Jason
View Replies !
Help With A Select Statement
Greetings,I am an SMS administrator and use SQL to create reports. My SQL skills are junior at best. I am trying to create an SQL select statement that shows me all computers that do not Java 1.5.0_04 installed. It is easy for me to search for all machines that have 'J2SE Runtime Environment 5.0 Update 4' but what I want is all computers minus the computers with Java 1.5.0_04.Posted below is my attempt, but it does not work. Can someone lend a hand and direct me onto the correct path?Thanks_________________select SMS_G_System_SYSTEM.Name, SMS_R_System.LastLogonUserName, SMS_R_System.OperatingSystemNameandVersion, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_R_System.ADSiteName from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like "%Workstation 5.1%" and SMS_G_System_SYSTEM.Name not in (select SMS_G_System_SYSTEM.Name from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "J2SE Runtime Environment 5.0 Update 4")
View Replies !
IF In A Select Statement
Hi, What would be the best way to do this, I have a table like so: JobID Estimate1 Estimate2 1 20.00 25.00 2 52.12 47.31 3 45.00 34.00 4 60.00 65.00 and I need to create an SQL query that will grab the Sum of the highest estimates, ie (25.00 + 52.12 + 45.00 + 65.00) I was hoping to do something similar to the SUMIF function in excel: SELECT SUMIF(Estimate1>=Estimate2, Estimate1, Estimate2) FROM Estimates but I haven't seen anything simular to that in the documetation.
View Replies !
Select Statement Help
I have been trying to get the Or AdminID Is Null to workin this statement and just cannot seem to get it. Can anyone help? Thanks so much, "Select Task_ID,Admin_ID,Priority,ActionDate,Subject,Note,Status,CompletionDate,Document,ReminderDate,Reminder,ReminderTime,Sol_ID,DateEntered,EnteredBy FROM tblTasks Where [Subject] Like '" & "%" & AdminKeyword & "%" & "' AND [ActionDate] Between " & "'" & TStart & "'" & " And " & "'" & TEnd & "'" & " And [Admin_ID] = '" & AdminID & "'" & " Or " & "'" & AdminID & "'" & " Is Null"
View Replies !
|
TRACKER
