Help With Select Statement

Jun 19, 2007

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 results

1 Car Audi A3 1.6 Special Edition 3dr 0 2 10000 229.99
1 Car Audi A3 1.6 Special Edition 3dr 0 2 10000 252.99
1 Car Audi A3 1.6 Special Edition 3dr 0 3 10000 219.99
1 Car Audi A3 1.6 Special Edition 3dr 0 3 10000 242.99
1 Car Audi A3 1.6 Special Edition 3dr 0 2 20000 252.99
1 Car Audi A3 1.6 Special Edition 3dr 0 2 20000 296.99
1 Car Audi A3 1.6 Special Edition 3dr 0 3 20000 241.99
1 Car Audi A3 1.6 Special Edition 3dr 0 3 20000 291.99
1 Car Audi A3 1.6 Special Edition 3dr 0 2 30000 274.99
1 Car Audi A3 1.6 Special Edition 3dr 0 2 30000 340.99
1 Car Audi A3 1.6 Special Edition 3dr 0 3 30000 264.99
1 Car Audi A3 1.6 Special Edition 3dr 0 3 30000 339.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 10000 227.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 10000 249.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 10000 220.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 10000 244.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 20000 253.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 20000 298.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 20000 244.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 20000 292.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 30000 279.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 2 30000 344.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 30000 267.99
2 Car Audi A3 1.6 Special Edition Sportback 5dr 0 3 30000 339.99

What 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 this

SELECT 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 1 Replies


ADVERTISEMENT

Select Statement Within Select Statement Makes My Query Slow....

Sep 3, 2007

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 3 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

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 Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok 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.

View 4 Replies View Related

Using Conditional Statement In Stored Prcodure To Build Select Statement

Jul 20, 2005

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 Related

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement

Oct 29, 2007

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 3 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

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 7 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

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'.

View 7 Replies View Related

How To Use Select Statement Inside Insert Statement

Oct 20, 2014

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]....

View 1 Replies View Related

Help With Delete Statement/converting This Select Statement.

Aug 10, 2006

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 1 Replies View Related

Select Statement Problem - Group By Maybe Nested Select?

Sep 17, 2007

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 Related

SQL Select Statement To Select The Last Ten Records Posted

Aug 6, 2007

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 2 Replies View Related

Using Select Statement Result In If Statement Please Help

Jul 11, 2007

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 2 Replies View Related

If STATEMENT Within Select Statement Syntax

May 15, 2008

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 2 Replies View Related

Can I Use SELECT Statement To Select First 100 Record????

Apr 21, 1999

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

View 1 Replies View Related

Select Statement With Count Within Another Select

Aug 23, 2013

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]....

View 1 Replies View Related

SQL Server 2012 :: Select Statement That Take Upper Table And Select Lower Table

Jul 31, 2014

I need to write a select statement that take the upper table and select the lower table.

View 3 Replies View Related

Using IF...Else Statement SELECT Statement

Sep 7, 2006

 

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 1 Replies View Related

SQL SELECT Statement

Jul 5, 2006

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 4 Replies View Related

Need Help With Select Statement

Dec 29, 2006

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 4 Replies View Related

SQL SELECT Statement

Mar 13, 2007

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 5 Replies View Related

Using An SP In A Select Statement

Apr 18, 2007

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 6 Replies View Related

Select Statement Help

May 29, 2007

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 3 Replies View Related

Select Statement

Aug 9, 2007

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 6 Replies View Related

Select Statement

Oct 19, 2007

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 5 Replies View Related

Select Statement

Nov 28, 2007

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 6 Replies View Related

Use If Statement In Select Sql

Nov 30, 2007

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 7 Replies View Related

Help Me With A SELECT Statement Please

Dec 28, 2007

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 2 Replies View Related

Regarding Select Statement

Feb 15, 2008

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 3 Replies View Related

SQL Select Statement

Feb 21, 2008

What is the syntax for a select statement within the controls of a label, or text box, I’m using VB? Thanks

View 1 Replies View Related

Need Help With This Select Statement

Mar 5, 2008

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 2 Replies View Related

Select Statement

Mar 13, 2008

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 3 Replies View Related

Help Me With A SELECT Statement, Please

Mar 15, 2008

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 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved