Complex SELECT Statement Help

Aug 30, 2007

Hi All,

 My sql is a little rusty, i ve been trying to do few things but still no luck. I m trying to query some data in one column based on certain . Here is my puzzle:

I have 7 tables: categories, characteristics, configs, rm_cat, rm_chars, rm_conf and rooms.

And here are the details on these tables:

- categories: {cat_id, cat_name}

- characteristics: {char_id, char}

- configs: {conf_id, conf}

- rm_cat: {room_id, cat_id}

- rm_chars: { room_id, char_id}

- rm_conf: {room_id, conf_id}

- room: {room_id. room_name}

 

I m trying to select a "room_name" based on a certain cat_id, char_id and conf_id and i don't know how to do this.

 

Sincerely,

View 3 Replies


ADVERTISEMENT

Complex SQL SELECT Statement

Jul 23, 2004

There are 3 tables, VendorLists, Vendors, and Referrals.

VendorLists is a linking table. It has VendorListID, VendorID, and ListID fields.

Vendors is linked to VendorLists through the VendorID field (one to many)

Referrals is linked to VendorLists through VendorListID (one to many)

I'm given a value for ListID and have to pull records from both the Vendors and Referrals table (a referral is a description of a vendor, one to many).

I am able to do this with the following SQL select statement:

SELECT Referrals.Description, Vendors.Company
FROM Referrals CROSS JOIN Vendors
WHERE Referrals.VendorListID IN
(SELECT VendorListID FROM VendorLists WHERE (ListID = lid))
AND
(Vendors.VendorID IN (SELECT VendorID FROM VendorLists WHERE ListID = lid))
ORDER BY Vendors.VendorID

This pulls all the appropriate records and values that i need and orders them by the identifier for the vendor. However, I want to randomly order the vendors but still group them together by company, so, if the VendorID is 1 for "joe's crab shack" and 2 for "billy's ice cream shop", the above will always list joe's crab shack first and all it's referrals. i want to be able to randomly order the vendors, but still keep the referrals of those vendors grouped together so that when i iterate over them, they're grouped.

Does anyone have any idea how to do this? I'm stumped!!

View 4 Replies View Related

Complex SELECT Statement

Feb 24, 2004

TABLE : USER
USERID
1
2

TABLE : TIME
TIMEID|USERID|RT|OT|DOT|DATE
1|1|8|2|1|2004-02-01
2|1|8|2|0|2004-02-02

3|2|8|0|0|2004-02-01
4|2|8|2|2|2004-02-02

RT : Regular Time
OT : Over-Time
DOT : Double Over-Time

I need to write a query to display the results in this way

USERID|DATE|TIME
1|2004-02-01|8
1|2004-02-01|2
1|2004-02-01|1

1|2004-02-02|8
1|2004-02-02|2

2|2004-02-01|8
2|2004-02-02|8
2|2004-02-02|2
2|2004-02-02|2

basically, the time entries for each user each day , seperate rows for RT, OT, DOT if they are not equal to 0

ive been breaking my head on this for quite a while. any help is appreciated.

thanks

View 5 Replies View Related

Help: Complex Select Statement

Jul 20, 2005

Here is my SQL string:"SELECT to_ordnum, to_orddate," _& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ONDDPROD.pr_prodnum = DOBOM2.b2_prodnum INNER JOIN DDORD ONDOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =DDTORD.TO_ID WHERE DOBOM2.b2_ordnum = ''order number here from resultof outer select) AS Total" _& "FROM DDTORD WHERE to_trak2id IN (39, 40, 41) AND to_ordtype = 's'AND to_status = 'c' GROUP BY to_ordnum, to_orddate ORDER BY to_ordnumDESC"The outter Select statement returns various amounts of order numbersrepresented by 'to_ordnum' in the outer Select clause which has tomeet the critera in the outer WHERE clause. I would like to placethese numbers selected into the inner WHERE clause for the innerselect statement where DOMBOM2.b2_ordnum = ?the order selected byouter select statement.I have tried placing to_ordnum into that location but the SQL2000server does not process it.Any suggestions, ideas?Thank you,Brett

View 1 Replies View Related

Complex SQL Select Statement That Works But...

Jul 24, 2006

I have a pretty complex SQL statement that looks like this:
SELECT     aspnet_Employers.active, aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title AS Contact,                       SUM(aspnet_Employers.EmployeeCount) AS [# Emps], COUNT(aspnet_Signups.account) AS [# Email Addresses], COUNT(aspnet_ContactMe.username)                       AS [# Contact Me], COUNT(aspnet_AppsSubmitted.account) AS [# Apply Now]FROM         aspnet_Employers LEFT OUTER JOIN                      aspnet_AppsSubmitted ON aspnet_Employers.UserName = aspnet_AppsSubmitted.account LEFT OUTER JOIN                      aspnet_ContactMe ON aspnet_Employers.UserName = aspnet_ContactMe.username LEFT OUTER JOIN                      aspnet_Signups ON aspnet_Employers.UserName = aspnet_Signups.accountGROUP BY aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title, aspnet_Employers.active
It does work the way i want it, but the problem is, on my Gridview when i change the Employers accounts "Active" status either way, it changes the username field from the username of the account, to "null".
Why does it do this?
What would i change to prevent this from happening?
 
Thanks!

View 3 Replies View Related

Complex SELECT Statement Issue

Sep 30, 2007

I may not be seeing the forest through the trees here but here goes.  I've got a table of computer configurations with columns for cpu, ram, m/b, hdd, etc.  The values in those columns are related to the id field in another table named parts.  The parts table has columns, id, partnumber, description, and cost.  What I want to do is be able to pull a record from the computer configurations table and instead of getting the integers in the cpu, ram, etc. fields I want to put the corresponding description field from the parts table.  For example:I want this...id    Name            CPU            RAM        MB              HDD    ... 1   Fast Machine   Fast CPU   Big RAM   Greate MB   Huge HDDNOT this....id    Name            CPU            RAM        MB              HDD    ...
1   Fast Machine   1               3               2                7 Below is a screenshot of my actual table relationships.Thanks in advance 

View 14 Replies View Related

Row_number Selecting From A Complex Select Statement

Sep 3, 2007

Hi,


Code Snippet


This is difficult to explain in words, but the following code outlines what I am trying to do:


with myTableWithRowNum as
(
select 'row' = row_number() over (order by insertdate desc), myValue
from
(
select table1Id As myValue from myTable1
union
select table2Id As myValue from myTable2
)
)

select * from myTableWithRowNum
Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?

View 4 Replies View Related

Please Help With Complex (for Me) Sql Statement

Sep 3, 2006

I need some help on how to structure a sql statement. I am creating a membership directory and I need the stored procedure to output the Last Name, First Name (and if married) Spouse First Name. Like this Flinstone, Fred & Wilma All members are in one directory linked by two fields. [Family ID] all the family members have the same family id and then there is a Family position id that shows if they are the Husband, Wife or Kids. I have no problem with this part select (LastName + ',' + FirstName) as Name, [Phone 1] as Phone, [Unit Name] as WD, [Street 1] as Street, SUBSTRING(City,1,3) as City, SUBSTRING(Postal,1,5) as Zipfrom Membership Where [HH Order]=1 Order By LastName ASC Could someone help me on how to display the " & Spouse FirstName " as part of the name field only if there is a spouse [HH Order]=2 for the current [Family ID]????

View 6 Replies View Related

Complex Sql Statement

Nov 13, 2006

I need to get multiple values for each row in a database, then do a calculation and insert the calculation and the accountnumber related to the calculation the data, into a different column.  I get an error trying it this way...there is no real identifier, it is jsut something that needs to get done per row...any ideas on how I can accomplish this?
 Declare @NetCommission decimal
Declare @AccountNumber varchar(50)
Set @NetCommission = (select (CommissionRebate * Quantity)
from Account A
Join Trades T on A.AccountNumber = T.AccountNumber)
Set @AccountNumber = (select A.AccountNumber
from cmsAccount A
Join Trades T on A.AccountNumber = T.AccountNumber)
 
Insert into Transaction
(
Payee
,Deposit
,AccountNumber
)
Values
(
'Account Credit'
,@NetCommission
,@AccountNumber
)

View 13 Replies View Related

Complex Sql Statement, Need Help

Jan 15, 2004

i have a complex sql statement and i think that my structure looks good but apparently not because i keep getting the same error, i was wondering if anyone knew how to correct this problem.



SELECT A.*, B.Name, C.SIName, D.IID,

(Select [LastName] , [FirstName]
FROM E INNER JOIN F ON E.SID =
F.SID , A
WHERE F.Emp='Service' AND E.Lead=1 AND E.ID=[A].[D])
AS Service,

(Select [LastName] , [FirstName]
FROM E INNER JOIN F ON E.SID =
F.SID ,A
WHERE F.Emp='Industry' AND E.Lead=1 AND E.ID=[A].[D])
AS Industry

FROM A , B, C
WHERE (1=1) AND B.SID = C.SID AND A.ID = B.ID


i always get this error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

does anyone know how to fix this query?

View 9 Replies View Related

About The Complex SQL Statement

Mar 18, 2004

Belows are the data of table T1

field1 field2 field3 Value
F1 F2 F3 A
F1 F2 F3 B
F1 F2 F3 C
... .... .....
F1 F2 F3 Z


Can any bright person help to script the SQL to extract above data set and present as below ???

field1 field2 field3 Value
F1 F2 F3 ABC......Z

Thanks for help

View 2 Replies View Related

Complex Insert Statement

Oct 9, 2006

I have this stored procedure that returns a rowid, distance.  It has a latitude, longitude, and range as inputs, it takes the latitude and longitude and computes a distance with every lat/long in a table PL_CustomerGeocode.  Once that distance is computed it compares that distance with the range, and then returns the rowid, distance if the distance is <= range.  I have the SELECT statement down, but now i just need to enter this information into a seperate table PL_Distance with (rowid, distance) as columns.  The sql statement is as follows, and i cant figure out where the rowid part is an the distance part is:      DECLARE @DegreesToRadians    float    SET @DegreesToRadians = Pi()/180    SELECT rowid, Cast(distance As numeric(9,3)) AS distance    FROM (SELECT rowid, CASE WHEN @srcLat = geocodeLat And @srcLong = geocodeLong THEN 0.0                             WHEN ABS(Arc) > 1 THEN 0.0                             ELSE 3963.1 * 2 * asin(Power(Arc, 0.5)) END AS distance    FROM (SELECT Power(sin(DLat/2),2) + cos(@srcLat*@DegreesToRadians)*cos(geocodeLat*@DegreesToRadians)*Power(sin(DLong/2),2) AS Arc, rowid,geocodeLat,geocodeLong    FROM (SELECT @srcLong*@DegreesToRadians-geocodeLong*@DegreesToRadians AS DLong,    @srcLat*@DegreesToRadians-geocodeLat*@DegreesToRadians AS DLat,    rowid,    geocodeLat,    geocodeLong    FROM dbo.PL_CustomerGeoCode) AS x) AS y) AS z    WHERE distance <= @range

View 1 Replies View Related

Help Forming A Complex SQL Statement

Nov 12, 1998

I have what is turning out to be a very complex T-SQL query to build.

I'm porting an App from Access to SQL Server... one of the Access queries used a function made in VBA to return a value.

For the life of me, I can't figure out how to make this work using only SQL Statements.

I'm not even sure how to even ask this. So here I go.. I've really simplified the SQL statement to help out with this. There are initially two tables.

Container and TraceRecord

Container is a table of Cargo Containers (the truck trailers you see on the highways)
TraceRecord is a table of location records as the containers move from city to city on the railroad.

The Containers move on fixed routes (ie.. Long Beach to Chicago, Long Beach to New York, etc...).. in the Container table there is a field called Route which records which Route the container is moving on and is related to a table of routes which I'll get to later.

[This is the old Access query.. notice the IIF statement and the function call to "IsOnTime"]
SELECT c.ContainerID, c.IngateDate, t.Location, t.Status, t.EventDate, t.EventTime
IIf(IsNull(c.IngateDate) Or IsNull(t.Location),"No Ingate Rail Record Captured",IsOnTime(t.Location,t.Status,t.Rail,c.Ro ute,c.IngateDate,t.EventDate, t.EventTime)) AS RailSch,
t.Rail
FROM c Container LEFT JOIN t TraceRecord ON c.ContainerID = t.UnitNumber

For each route there is a scheduled travel plan.

Example.. when a container is taken to the BNSF railroad in Long Beach that is called an "Ingate" and is considered Day 0 (zero). As the container moves on the railroad from Long Beach to Chicago, it will pass through other cities, and the TraceRecord table will record where the container is and what time and day.

What I need to do is determine, based on the latest TraceRecord record, by how many hours is the Container "on time".

There is a routing table which lists the predefined travel path for each route.. listing the number of days and hours a container should be a certain place since the day the container was taken into the railroad at the origin "Ingate".

What the IsOnTime function did was take the arguments and do some math with the routing tables and find out how many hours a unit is or is not on time.

Here's a copy of the function from Access 97 using DAO. I don't know if any of this is going to make sense to anyone.. but I'm stuck and don't know what else to do.

Function IsOnTime(strLocation As String, strStatus As String, strRail As String, intRoute As Integer, _
dateIngateDate As Date, dateCurrentDate As Date, dateCurrentTime As Date) As Variant
On Error GoTo errorh:
Dim rs As Recordset 'Rail Schedule Recordset
Dim db As Database 'Current Database
Dim sqlFind As String 'Search String to find city transit time
Dim dateScheduleDateTime As Date
Dim dateDifference As Integer 'Hours difference between trace and schedule
Dim varvar As Variant


If dateIngateDate = Null Then
IsOnTime = -9999
Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("tTransitTimeTable", dbOpenSnapshot)

sqlFind = "([RouteID] = " & intRoute & " AND [City] = '" & strLocation & "' AND [StatusCode] = '" _
& strStatus & "' AND [Rail] = '" & strRail & "')"



rs.FindFirst sqlFind
If rs.NoMatch = False Then

If (rs!daymarker = "" Or IsNull(rs!daymarker)) And (IsNull(rs!daymarker) Or rs!cutoff = "") Then
IsOnTime = "Finished"
rs.Close
Exit Function
End If

dateScheduleDateTime = DateAdd("d", rs!daymarker, (dateIngateDate + rs!cutoff))

dateDifference = DateDiff("h", dateScheduleDateTime, (dateCurrentDate + dateCurrentTime))
If dateDifference <> 0 Then

IsOnTime = -dateDifference
End If
If dateDifference = 0 Then
IsOnTime = 1
End If
Else
IsOnTime = -9999
End If

rs.Close
End If
Exit Function

View 1 Replies View Related

Please Help Me With A Complex DELETE Statement

Jan 27, 2005

Hello, currently I have a query like this:


PHP Code:




 SELECT     *
FROM         relations INNER JOIN
                      paths ON relations.path = paths.path_id
WHERE     
                      (paths.links = '161') AND (relations.node1 = 162) OR
                      (paths.links = '161') AND (relations.node2 = 162) OR
                      (paths.links = '162') AND (relations.node1 = 161) OR
                      (paths.links = '162') AND (relations.node2 = 161) OR
                      (paths.links LIKE '162%') AND (relations.node1 = 161) OR
                      (paths.links LIKE '%162') AND (relations.node2 = 161) OR
                      (paths.links LIKE '161%') AND (relations.node1 = 162) OR
                      (paths.links LIKE '%161') AND (relations.node2 = 162) OR
                      (paths.links LIKE '%161;162%') OR
                      (paths.links LIKE '%162;161%')
ORDER BY relations.node1 





Don't pay attention to the 161 and 162 things, is just test data, now my problem is that I want to transform that into a DELETE statement, but I can't find the right way to do it, so far I managed to do something like:


PHP Code:




 DELETE relations
FROM         relations INNER JOIN
                      paths ON relations.path = paths.path_id
WHERE     
                      (paths.links = '161') AND (relations.node1 = 162) OR
                      (paths.links = '161') AND (relations.node2 = 162) OR
                      (paths.links = '162') AND (relations.node1 = 161) OR
                      (paths.links = '162') AND (relations.node2 = 161) OR
                      (paths.links LIKE '162%') AND (relations.node1 = 161) OR
                      (paths.links LIKE '%162') AND (relations.node2 = 161) OR
                      (paths.links LIKE '161%') AND (relations.node1 = 162) OR
                      (paths.links LIKE '%161') AND (relations.node2 = 162) OR
                      (paths.links LIKE '%161;162%') OR
                      (paths.links LIKE '%162;161%') 





But that would delete only from the relations table and not from the paths table. I need to delete from both tables.

Can anyone help me please? Its kinda urgent.

Thansk!

View 5 Replies View Related

Complex Update Statement Not Working

Nov 6, 2006

hi.

can somebody explain to me why the below
update fails to update one row and updates
the entire table?


Code:


UPDATE addlist
SET add_s = 1
WHERE EXISTS
(SELECT a.add_s, a.email_address, e.public_name
FROM add a, edit e
WHERE a.email_address = e.email_address
and a.add_email = 'mags23@rice.edu' and a.add_s = 0 and e.public_name = 'professor');



and, what is the solution? thank you.

View 8 Replies View Related

Issue With A Complex Case Statement

Apr 29, 2008

I am new to SQL server 2005. I run a large query daily against a teradata warehouse and it populates an access database. I am now attempting to run my query in a SQL 2005 database. I get an error on this case statement:

CASE
WHENCAST ( ( B.zip_cd ( format '99999' ) ) AS char ( 5 ) ) = '*****' THEN SUBSTR ( CAST ( ( B.zip_cd ( format '999999999' ) ) AS char ( 9 ) ), 1, 5 )
ELSECAST ( ( B.zip_cd ( format '99999' ) ) AS char ( 5 ) )
ENDAS ZIP_CD

The error is: Msg 102, Level 15, State 1, Line 229
Incorrect syntax near '99999'.

Any help is greatly appreciated!!!

View 2 Replies View Related

Please Help With Complex Update Statement Logic

Nov 8, 2006

hi.I am having probelms with an update statement. every timei run it, "every" row updates, not just the one(s) intended.so, here is what i have. i have tried this with both AND and ORand neither seem to work.i dont know why this is elluding me, but i'd appreciate help with thesolution.thanks.UPDATE addSET add_s = 1WHERE add.add_status = 0 and add.add_email = 'mags23@rice.edu'or add_s in(SELECT a.add_sFROM add a, edit eWHERE a.email_address = e.email_addressand e.public_name = 'professor')

View 22 Replies View Related

Help With Complex SELECT

Jan 31, 2007

Can someone help me with an advanced query?I have two tablesTable 1: EmployeesCols: UID, lname, fnameTable 2: StatEntryCols: UID, Timestamp, descriptionThe queary should reaturn all the information in both tables. If morethan one entry exists in the second table, it should return the onewith the greatest timestamp. If not entries exist I would like thesecond table columns set to "no value"Something link:select Employees.*,StatEntry.* from Employees JOIN StatEntry ONemployees.uid == statentry.uid WHERE timestamp in (selectMAX(timestamp) from statentry where uid=employees.uid).Anyone db guru's out there?-SJ

View 4 Replies View Related

Complex SELECT

Jul 20, 2005

Hi,I have problem about writing a proper SELECT query for the followinggoal:Table name: peoplelistcolumn 1: id (not NULL, auto_incremental)column 2: namecolumn 3: countrynow, there are about 7,000 rows in this table. I want to select out:first 10 or less people in the table for each country.for example: suppose there are :1000 people from US3000 people from UK3000 people from CanadaI want to list totally 30 people, i.e. 10 people from each country.The problem is , the actual table includes many countries, not onlythree. How can I do this by a SELECT sql query ?Thanks.Han

View 1 Replies View Related

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

Complex SQL Select Query

Apr 29, 2007

Hi all
I im trying to write a SELECT query to display a set of my logged in user's 'Friends'. Although the way that i have designed my tables means that its very complex, and im hoping someone out there can tackle it!
To start ill show you how i contruct friends:
Friends
FriendshipID                     Incrementing PKInviteeID                           Unique UserID of person who offered the friendship linkInvitedID                           Unique UserID of person who was invitesApprovedBInvitee              True/False - sets to 'True' by default (probably isnt needed come to think of it )ApprovedByInvited            True/False/Declined - an nvarchar
Next, I have my UserDetails table:
UserDetails
UserID                            Unique UserID PKUserName                      Unique Username  (foreign key from aspnet_Users as created by aspnet_regsql.exe)Avatar                             Integer which represents an image name in a photos folder
So, on the myFriends.aspx i firstly set an invisible label's text property to the unique UserID of the logged in user. This gives me a control paremater for the select statement.
The information I want to display is just the UserName and Avatar of all users who are friends with the logged in user.
I know that to get the records where the logged in user is either that Invited or the Invitee, I do this:WHERE (@loggedInUser = Friends.IniteeID) OR (@loggedInUser = Friends.InvitedID)
 (that will show the logged in user as his own friend but i dont mind that)
After that I am stuck more or less... it seems to become very complex... maybe i need 2 queries?
If anyone can help i would be very very grateful

View 12 Replies View Related

Complex Select Query

Mar 13, 2008

Hi.
I have two tables. The first is a price proposal table which contains fields like the index of the proposal, proposal decription, proposal date etc. The second table contains data about the products associated with a certain price proposal. This table has a proposal index field and other fields like the index of the product (a unique identifier), product catalog number, price of the product etc.
I want the rows which returned by my select query to display the following:
The Proposal index, description, date, product catalog number grouping, the number of products with a certain catalog number in this proposal, the sum of the prices of these products.
Example:
PriceProposalTable : ProposalIndex = 1 ----->  ProductsTable: ProposalIndex = 1, ProductIndex = 1, CatalogNumber=A, Price=100
                                                                                      ProposalIndex = 1, ProductIndex = 2, CatalogNumber=A, Price=90
                                                                                      ProposalIndex = 1, ProductIndex = 3, CatalogNumber=B, Price=80
 
PriceProposalTable : ProposalIndex = 2 ----->  ProductsTable: ProposalIndex = 2, ProductIndex = 4, CatalogNumber=C, Price=100
 
I want the select query to return:
ProposalIndex         ProposalDescription     ProposalDate    CatalogNumber  NumOfProducts     SumOfPrices
          1                             ...                         ...                   A                     2                      190
          1                             ...                         ...                    B                    1                       80
          2                             ...                         ...                    C                    1                      100
 
I hope the example is clear enough. I would appreciate your help on this.
Thanks.

View 3 Replies View Related

Complex Select N Query

Mar 28, 2008

Ok, I have a table with two columns in the group by clause, ServerID and Database ID. I have 9 unique ServierIDs and 117 unique DatabaseIDs. I need a query that says gimme the 5 highest database id hitcount totals for each server id. I would like to do this without a #temp table or a cursor.
here's what I have:

-------------------------------------------------------------------
create table #server_hitcount_summary(
SID int not null,
DBID int not null,
ORD int not null,
SUM_HITCOUNT bigint not null,
SUM_MAXWRITES bigint not null,
SUM_MAXREADS bigint not null,
SUM_MAX_DURATION bigint not null,
SUM_MAX_CPU bigint not null
)

declare @id int
set @id = 1
while (@id < 20)
begin
insert #server_hitcount_summary
select top (@top)
SID,
DBID,
ROW_NUMBER() over (order by sum(hitcount) desc) as ORD,
sum(hitcount) as SUM_HITCOUNT,
sum(MAX_WRITES) as SUM_MAXWRITES,
sum(MAX_READS) as SUM_MAXREADS,
sum(MAX_DURATION) as SUM_MAX_DURATION,
max(max_cpu) as MAX_MAX_CPU
from
hitcounts_table group by SID, DBID having SID = @id and DBID <> 1 order by sum_HITCOUNT desc
set @id = @id + 1
end
select
SID,
DBID,
ORD,
SUM_HITCOUNT,
SUM_MAXWRITES,
SUM_MAXREADS ,
SUM_MAX_DURATION,
SUM_MAX_CPU
from #server_hitcount_summary order by SID asc
drop table #server_hitcount_summary
-------------------------------------------------------------------

What d'ya think? is there a better way to do this?


-The Universe is naturally cold. It takes the power of a star to change that.

View 2 Replies View Related

A Complex SELECT Stored Procedure, Pls Help

Apr 6, 2008

hi!
i have two tables: Pictures (PictureID, UserName, UserID, DateAdded, Comments int) and UserVisits (VisitID, UserName, UserID, PictureID, NewComment bit)
if a new comment is added on a picture with PictureID = 4, i update NewComment from UserVisits with 1 (true) where PictureID = 4 to all rows, 
and if a user visits a picture with PictureID = 5, NewComment will be set to 0 back where pictureID = 5 (something like asp.net, when a reply is added to a therad)
i need to make a stored procedure with UserName input parameter witch selects ALL pictures from Pictures table and selects NewComment (true or false) from UserVisits witch corresponds with that UserName
!!! Is possible that in UserVisits to not have yet any column where UserName corresponds with the user who visits if the user did not visited that picture yet (this stored procedure will be used on a Repeater witch lists all the pictures)
if i was not clear please tell me, sorry i am verry bad at T-SQL language
thanks in advance

View 2 Replies View Related

Complex/annoying SELECT/JOIN Query

Feb 8, 2008

I have a very confusing/complicated query that I am trying to run and getting not the results that i want.

Essentially I have three tables (t1, t2, t3) and I want to select data from two of them, but there are conditions on the data where I need fields to match across pairs of tables.
When I run my select query I am getting far too many records - it's giving me all possible combinations, instead of the proper combinations that I want.



Select t1.*, t3.field2, t3.field3
FROM, t1, t2, t3WHERE t2.field4=t3.field4 AND t1.field5=x AND t1.field6=t2.field6

I suspect there is plenty wrong with this query - where should I start correcting it?

View 10 Replies View Related

Problem With Building SQL To Select Complex Joined Olumns

Mar 17, 2006

John writes "Problem with building SQL to select columns from three joined tables, all of which can have an outer join to a fourth table.

Environment is SQL Server 2000.

Here is a simplified version of schema:

EveTable:
EveTableId (key)
Title
OrgTableId
LocTableId
ImageId (can be null)

EveTable Joins to:

OrgTable:
OrgTableId (key)
Title
ImageId (can be null)

EveTable also Joins to:

LocTable:
LocTableId (key)
Title
ImageId (can be null)

All three tables join to:

ImgTable
Title
ImageId
Title

Problem: I wish to: Select EveTable.Title, LocTable.Title, OrgTable.Title, ImgTable.Title [all] where EveTableID=n

I am currently stuck at building even the basic SQL for this!

Best Regards,"

View 1 Replies View Related

Complex DB Search Forms (Store Proc Vs. Complex Where)

Nov 12, 2003

I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.

Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.

I build clauses like this (i.e., 4 fields shown):

SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )

My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.

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







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