How Can I Run Select Statement For Two Tables That
Jul 2, 2007
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 2 Replies
ADVERTISEMENT
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
May 8, 2004
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 1 Replies
View Related
Jan 28, 2005
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 2 Replies
View Related
Mar 20, 2006
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 6 Replies
View Related
Apr 30, 2008
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 6 Replies
View Related
Jan 11, 1999
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 1 Replies
View Related
Jul 23, 2005
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 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
Dec 4, 2007
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks
View 7 Replies
View Related
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
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
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
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
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
Oct 15, 2007
Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
View 1 Replies
View Related
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
May 26, 2008
How do I do a WHERE statement on two tables?
CREATE TABLE [Audit]
(
[AuditID] [int],
[AuditDate] [datetime]
)
CREATE TABLE [ToDoItem]
(
[ToDoItemID] [int],
[AuditID] [int],
[DueDate] [datetime],
[CompletedDate] [datetime]
)
What I need to do is select all Audits that
still have ToDoItems not complete
that the DueDate has passed
or where the audit was done over six months ago
What I tried is
SELECT
[AuditID]
FROM
Audit a
WHERE
(a.AuditID in (SELECT AuditID
FROM ToDoItem t
WHERE
t.CompletedDate is null
AND (t.DueDate < getDate()
or ( (DateAdd(Month, 6, < a.AuditDate)) < getDate()) )
))
----
Dave
View 6 Replies
View Related
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
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
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
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
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
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 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 1 Replies
View Related
