Hello there, i'm pretty new to web development and i've come across this problem, so here's the case.
I got a videoclub database and i wanna show the movies categorized by the category that the movie belongs to. I got a table with all the categories (the table includes an ID and the name of the category),
and the movies table has movie data plus a category id field which is related with the categories table. Anyway enough with the background info here's the query i'm trying to use:
SELECT MovieID, Name, Director, Category, Casting, Photo, Supplier, ReleaseDate
FROM Movies
WHERE (Category = @ Category)
The error i'm getting on the query builder is "Error in WHERE clause near '@'. Unable to parse query text.". If i aint wrong i'm supposed to use the "@" to indicate that its supposed to get data from a control. Anyway thats the prob, any help would be really apreciated.
P.S. The control i'm using is a dropdownlist that shows the categories names and has as a value the category id, so there isn't a variable miss match error as much as i know.
Can someone help me rewrite this query? Basically I need to check if the name stored in "CustomerName" already exist in the table "Renter". If it does not then I ncan insert the new customer name into the data table "Renter". If the value in "CustomerName" already exists in the database, then I need to bypass the "INSERT" and somehow return a value indicating that the insert was not performed. How can I do this. Here is the query I currently have for performing the insert. CREATE PROCEDURE [dbo].[VacancyGet] ( @CustomerName nvarchar(100), @ClientCode nvarchar(10) ) AS INSERT INTO Renter ( CustomerName, ClientCode, ) VALUES ( @CustomerName, @ClientCode, )
I know that the query governor works off of estimated query plans, but the estimation is usually so far off from reality that it might as be generating a random number to determine whether a query should run.
Right now I have query governor set at 300. I've been able to run queries that take more than 10 minutes without any complaint from query governor.
What's really annoying though, is that it has also blocked queries that take less than a second to run. It blocked a query for having an estimated executing time of 338 seconds. When I set the limit up to 400, the query ran virtually instantly.
Is this just how query governor is suppose to work? Is there any way to make it work better?
Hey guys, I'm new to reporting services and I just need to throw together a report that references multiple datasources. I can query up all my info individually, but I need to write a query that says,
Select ProductName,ProductId from datasource 2
where ProductId = @ProductIdFromDataSource1
I can get this to work, but if my query from datasource 1 returns 10 productID's, this will only return the productname from the first ProductID, 10 times. I believe that's because I'm using the First() aggregate function. Is there another function I can use that doesn't do that, or some way I can reference each field individually?
Just to clarify a little more, here's what I want:
Hate to sound so dumb, but, it's been years since I've done any SQL programming and I was bad at the time!
What is the code for taking my time stamp 2007-02-09 10:15:02.000 and just displaying the date as 02/09/2007?
Do I use variables? Are they set up before my select statement? Can anyone suggest a really good, easy, very explanable SQL (that will work in SQL Server) progamming book?
Hi, I changed the sa password today on my subscriber databases, and now my transactional replication fails, as it cannot connect to the subscribers. I updated the publisher's linked server logins, but it still fails. Help please! Thanks, Jason
Hi, I am getting many instances of the following error, and I cannot find any documention on it, or how to remedy. Any help/suggestions would be GREATLY appreciated. Thanks in advance - jason
SQL Server Assertion: File: <xcbmgr.cpp>, line=1299 Failed Assertion = 'pss->IsXcbLocked ()'. Dump thread - spid = 58, PSS = 0x2e637158, EC = 0x2e637308 Stack Dump being sent to C:MSSQL7logSQL00115.dmp ************************************************** ***************************** * * BEGIN STACK DUMP: * 10/04/00 16:39:54 spid 58 * * Input Buffer 42 bytes - * s e l e c t u i d = N E W I D ( ) * ************************************************** *****************************
I am trying to make a single display page for an author's books.the books page only displays books of a type "type" (novels, non-fiction, etc)I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this: <asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader %>" SelectCommand="SELECT * FROM [Books] ( If @Type <> "" then WHERE ([Type] = @Type)) ORDER BY [SortDate] DESC"> But it doesn't seem to want to work. I get a "server tag is not well formed" error.
I have a table called "member" that has two fields (amongst others) called "firstname" and "city"I want to return a list that sorts people who live in the same city, but does not return people who do not have a city in common.For example if this is my data:Karen - ParisMike - RomeTim - Dallas
Jim - ParisChris - DallasJohn - ManhattanSarah - OrlandoDavid - DallasThe query would return this:Dallas - ChrisDallas - DavidDallas - TimParis - JimParis - KarenI have this SQL so far: Select city, firstname From member Order By city, firstnamebut it includes all the cities including those that only have 1 resident.Can someone tell me how to do this?Thanks a lot,Chris
hi,i have three fields in the database tablep1 p2 rank10% 20% 3 21% 40% 2now i am passing the input as 15%...this 15% is related to 3rd rank.. so i need the o/p rank as 3..if the p1 and p2 datatypes are varchar..then how we can we write the query for that one tgo get the rank as 3.Ramesh
Hi All Can anyone tell me what this simple SQL query would be: Find all the words "black", "dvd" and "player" from any of the fields "Product", "Brand" or "Description".
I am trying to select players from a database based on the userid not having any user's name in it with an order by ASC. I basically want all the players that haven't been taken. Here is what I came up with and it gives me empty.
strSQLA="SELECT UserID, playerName from Allplayers WHERE UserID = '' ORDER BY playername ASC"
I hope it's considered appropriate to post a SQL query question here; it's not I'll gladly post this question elsewhere. That having been said, I've run into a simple but quite tricky SQL query and was wondering if the community might be able to help. Here's what's going on: I have a "Documents" table. Let's call it DI have a "Benefits" table. Let's call it B.There is a many-to-many relationship between D and B so I have a third table to store the relationships. Let's call that table BD. When I create a document it creates a row in D. Let's call that D1, D2...DN. When I create a benefit it creates a row in B. Let's call that B1, B2...BN When I associate a document to a paritcular benefit, let's say that if D1 and B2 are associated, that I created row B2-D1 in my BD table. Now here's what my challenge is. Suppose I have these entries in my tables: D: D1, D2, D3B: B1, B2, B3 Table BD:B1-D1B2-D1B2-D2 When the user goes to add documents to benefit B1, I want to show the user only documents that are not yet added to B1. In this case, I want to show the user D2 and D3 but not D1 since that one's already added. How do I write a SQL query to do this? Here's what I've been using, but it shows too many results (e.g. included D1, D2, D3) SELECT D.*, BD.*FROM Documents AS d LEFT JOIN rel_BenefitsDocuments AS bd ON d.doc_id = bd.doc_idWHERE (benefit_id <> @benefit_id OR benefit_id IS NULL) Any thoughts?
I want to write a sql query for an asp page which will display only unique rows from the specified column along with the number of count for each unique row.
Example:
Table that I want to query
Last Name || First Name Gates || Bill Boyce || Mike Gates || Bill Gates || Phil
Results I want:
Last Name || First Name || Count Gates || Bill || 2 Boyce || Mike || 1 Gates || Phil || 1
I am new to SQL and this question may be most easiest to many of you. Here is what I need.
I have two identical tables (exactly the same in structure) having a compound primary key with a combination of 3 columns. Can someone give me the most efficient query that fetches all the rows from table1 that are not in table2.
from sales_contact sc , invoice i , invoice_line_item il , sales_region_special_section srss , sales_region sr , issue_date idd
where sc.sales_contact_id = i.sales_contact and i.invoice_id = il.invoice and srss.sales_region = sr.sales_region_id and il.issue_date = idd.issue_date_id and srss.invoice_line_item = il.invoice_line_item_id
order by sc.sales_contact_id
================
heres some of the result set I want only the records with the greatest id for each unique sales contact...
AF85F32E-8E34-4C40-9468-00148A34E903, 41, N AF85F32E-8E34-4C40-9468-00148A34E903, 42, N 5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N 5D26328A-192B-4E4E-9B34-010C4E077CF8, 70, N 5D26328A-192B-4E4E-9B34-010C4E077CF8, 34, BC B44C914E-6001-40CE-8AB6-0126BD572D45, 25, NW B44C914E-6001-40CE-8AB6-0126BD572D45, 26, NW B44C914E-6001-40CE-8AB6-0126BD572D45, 24, NW B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC B44C914E-6001-40CE-8AB6-0126BD572D45 , 28, NW
this would be an ideal result set using the data above...note there are situtations where there are n* for a greatest issue id.
AF85F32E-8E34-4C40-9468-00148A34E903, 42, N 5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC B44C914E-6001-40CE-8AB6-0126BD572D45, 28, NW
This should be easy, I'm just having a brainfart at the moment, can't remember how to do this:
Say I have a bunch of records in a table, with an ID field as Key. I want to return the count of times each ID shows up, so for the following example data:
This should be obvious but for some reason I can't see it.
When this is done I'm going to join in another table to get a name based on the ID with the highest count. I believe it's faster to do a TOP 1 here rather than after the join, but I want to verify that while I'm at it also.
I am a complete newbie to SQL. I have a simple query to make in SQL, but I'm not being able to construct the statement properly. I've already tried googling this info, but with little luck. If someone could help me here, I'd be very thankful.
The problem:
I have a table with a list of questions. All questions have a serial number. The query has to return whether a particular question is the last one in the table or not (according to serial no.)
Logically, this is the query -
Code:
select islast = 0 if count(*) from question where serialno > $serial != 0 else select islast = 1;
The '$serial' part will get replaced dynamically before query execution through another program.
Any idea how to code this in valid SQL? I would like to make it a simple statement and not a stored procedure. Thanks.
Hey.. hope someone can help, i desperatly need some assistance. I have a few query issues but first things first.. one is that i have a table of messages and users having an online conversation, so fields are msg id, subject, topic, message, user, reply to and time it was sent. Im trying to do a query that will tell me which user sent the most messages, ive tried using count and stuff like that but not really getting anywhere. It will basically have to count each time a user has spoken and then give me the name of the user that has sent the most messages, ive been playing around with stuff like SELECT Count(*) AS Expr1 FROM Table1 WHERE User='andro8472' OR User='bumies';
That will count the times those users have spoken but cant get further
any help would be greatly appreciated really stuck at the mo..
I'm having a bad day as I just cannot get a query to work and its doing my head in and it should be a simple query.
I'll try and explain what I need.
Have a simple query with one join, this is it so far:
SELECT DISTINCT Replace(StaffName,'''''',' ') AS StaffName,OutReason,OutStartDate ,OutEndDate,OutStartTime,OutEndTime,OutID FROM vwOutOfOffice LEFT OUTER JOIN vwStaffList ON OutUser=StaffID WHERE OutComplete=0
What I'm having trouble with adding to the WHERE is as follows, I need a list of Staff who are out of the office based on the various criteria:
Example Current date=29/02/2008 Current time=14:00
Start date---End Date----Start Time--End Time--Should Appear 27/02/2008--28/02/2008--09:00------17:00-----No (because of date) 28/02/2008--03/03/2008--08:00------17:00-----Yes 28/02/2008--29/02/2008--17:00------17:00-----Yes 29/02/2008--29/02/2008--12:00------14:00-----Yes 29/02/2008--29/02/2008--14:30------17:00-----No (because of time) 02/03/2008--02/03/2008--08:00------10:00-----No (because of date)
There may be others I've missed but as long as the dates combined with the times match correctly I need the records to appear.
Please help before I end up putting my head through the monitor.
ID Number NameJoin DateSex 101 Jason01/02/1996M 117 Robert06/25/1999M 113 Doug08/12/1999M 161 Marian07/30/1998F
Please let me know the SQL queries for the following questions for the above table:
1)Write a Select statement that selects the persons whose Type is "M" and who have joined in the last four years and place the result in an array. Today's date is 12/31/1999.
2) Write a Select statement that selects the persons who do not have unique ID Number and place the results in an array.
3) Write code to sort the array by Name + Join Date. Assume that the array has already been created and is sorted in the same manner as the table above. The Join Date is of type smalldatetime. The Name field should be sorted case insensitively.
How do I retrieve all rows from a middle_name column that have a character length of <= 2. This table is populated with middle names and middle initials. I want to isolate the middle initials only.
SELECT ('Section : ' + F.Section + ' ' + (SELECT S.SectionName FROM SFM_Section AS S WHERE S.Company = F.Company AND S.Department = F.Department AND S.Section = F.Section ) ), F.Variety, F.Category, isnull(SUM(F.AreaCutCurrent), 0) AS AreaCutCurrent, isnull(SUM(F.TonnageCurrent), 0) AS TonnageCurrent, isnull(SUM(F.YieldCurrent), 0) AS YieldCurrent FROM SFM_Field AS F WHERE F.CropYear = 2007 and ActiveFlag = 1 and Section = 04 GROUP BY F.Company, F.Department, F.Section, F.Variety, F.Category ORDER BY F.Section, F.Variety, F.Category
that returns me the following results:
Section:04 Region Haute|M0052/78|R07|1.84 |137.64 |74.804 Section:04 Region Haute|M0695/69|R01|37.71|2817.65|434.009 Section:04 Region Haute|M0695/69|R02|35.08|2282.33|523.234 Section:04 Region Haute|M0695/69|R05|21.2 |1563.01|147.579 Section:04 Region Haute|M0695/69|R09|6.52 |484.39 |74.293 Section:04 Region Haute|M0695/69|R11|0 |0 |0 Section:04 Region Haute|M1397/86|R04|8.35 |793.61 |190.141 Section:04 Region Haute|M1400/86|GS |18.03|2093.91|116.135
What should I do if I don't want the records that the sum is equal to 0 to appear in my results? (Like the 3rd row from the end)
You probably seen this Q before but I have no idea what to search and and therefor any direction would be appreciated.
I have three columns table, two are int and one is datetime (for sorting). I would like to query all values witch are unique within the two integers, however, in some cases data looks like this: int1 = 1 int2 = 2 and another record like: int1 = 2 int2 = 1
this pair should be considered as a duplicate and second record should not come with the results set.
Hi AllI know that I am supposed to submit a schema of my table, but it is a verybasic one so I'll crack straight on with my query.In essence I have a 3 column table called STOCKTRANS, which consists ofSTOCKCODE, TRANSACTIONDATE and TRANSACTIONTYPE.Each time a user orders a stock item I insert the stock code and the datethey ordered it into this table, egSTOCKCODE TRANSACTIONDATE TRANSACTIONTYPEIVP 1-4-04 1STP 31-6-04 8KGC 8-7-04 6IVP 11-8-04 8etc etcetcWhat I want to create is a simple query that informs what the last orderdate was for a stock code. As you can see from the above example, I mayhave hundreds of the same stock code with different dates so I thought thatthe following query would work:select STOCKCODE, MAX(TRANSACTIONDATE) as 'LastOrderDate'from STOCKTRANSwhere STOCKCODE = 'IVP' and TRANSACTIONTYPE in (1,8)But it doesn't.NOTE: the reason for the transaction types of 1 and 8 is because these arethe types I want. The other types are used for credits, returns, etc.First of all the above, brings me back loads of rows when all I want is:IVP 11-8-04 << this being the last date of order for this stock item.Just as a test I removed the STOCKCODE out of the select and found that Idid get my one liner, but for some reason I was only getting the last datefor transaction types 1 only - not the last date for a combination of 1 and8 transactions.Can you help??Many thanksRgds Robbie
