Hi all,
for some reason (stupidity maybe) I can't solve this problem...
I have a table with this information:
ID SerialNumber Result
1 123 Pass
2 125 Fail
3 234 Pass
I just want a select that shows how many pass and fail...
Like this:
Pass | Fail
2 | 1
Thank you
I am trying to select a particular record from a table and for some reason it only returns 1 record when I know there are at least 6. I've tried Rtrim and left to make sure that I'm getting the exact string. The "MyID" field is varchar(32) and the "ExpireDate field is datetime and I have tried the below. Any ideas what could be wrong?
Select DISTINCT K.MyID, KP.[EXPIREDATE] --, K.ACTIVATIONKEY
From [KEY] K INNER JOIN KEY_PRODUCT KP
ON K.MyKey = KP.MyKey
Where K.MyID = '013BEB73C2CF11D39F3600105A05264C'
I have a simple select statement that I am having a complex with. I am a C# developer trying to expand and gain a little more knowledge of SQL. I want to send a string to a SQLCommand catagory = 'news,alerts,events' and return a result set if it matches any term.
This is for a blog I am creating for a church and the catagory is like the tags you normally see. I could use something like:
SELECT GUID,CATAGORY,DESCRIPTION,TITLE,ETC
FROM RSS_FEEDS
WHERE CATAGORY LIKE 'news,alerts,events'
I can seperate the keywords with any character if needed.
WHERE (ITEM_CATEGORY LIKE '%news,events%')
This only returns ones that have all like what is there not the ones having news only
Is there a way or trick to this.
Thanks
Hi
I Have the following table
SequenceNumber___TypeID8_________________IMG7_________________IMG6_________________IMG5_________________IMG4_________________IMG3_________________IMG2_________________FLP2_________________IMG
I want to pull the data out in the following format,
SequenceNumber___TypeID8_________________IMG2_________________FLP
This basically shows the highest SequenceNumber of each TypeID,
I've tried many different SQL queries but I can't seem to get it! Any ideas?
Thanks
I am new to ASP, I come from a PowerBuilder background. I like the sqldatasource model, but I have a question. In PowerBuilder I could just write straight 'embeded' sql in the code.such as Select XFROM YWhere XYZ; So my question is this: I want to get the value out of the database and set a variable to it where I know a key field, what's the quickest way to do it? Thanks Dan
View 9 Replies View RelatedHi guys,
I am back again looking for help… ?
Here’s what I am trying to do…
I have a table which looks like following..
ID int
CustID int
SaleType char (3)
Amount money
So data in my table looks like following..
ID CustID SaleType Amount
1 1001 AB 1.20
2 1002 AB .20
3 1001 BA .50
4 1003 BA .50
I need a query to return me Each Customer’s ID, Total Count of SaleType ‘AB’, Sum of Amount where sale type ‘AB’ and Same for SaleType ‘BA’
So I should return something like this…
CustID SaleTypeABCount SaleTypeABAmount SaleTypeBACount SaleTypeBAAmount
1001 1 1.20 1 .50
1002 1 .20 0 0
1003 0 0 1 .50
I have query which is doing something like this…
Select distinct C.CustID, SaleTypeABCount = (Select Count(*) where SaleType = ‘AB’ and CustID = C.CUSTID),
SaleTypeABAmount = (Select Sum(Amount) where SaleType = ‘AB’ and CustID = C.CUSTID),
SaleTypeBACount = (Select Count(*) where SaleType = ‘BA’ and CustID = C.CUSTID),
SaleTypeBAAmount = (Select Sum(amount) where SaleType = ‘BA’ and CustID = C.CUSTID)
from MyTable
It is working except that it is returning me multiple rows of same data…
Any help with this will be really appreciated..
Thanks heaps..
Ok, I do know SQL and have been using it for quite soem time. For some reason, it is giving me an error and I was wondering if someone could help.
Here is the few lines of interest
System.Data.SqlClient.SqlCommand command;
command = new System.Data.SqlClient.SqlCommand(@"SELECT Password FROM User WHERE Username='" + user + "'", this.sqlConn);
dataReader = command.ExecuteReader();
This is the error I am coming up with.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'User'.
I have tried enclosing the Table name in quotes and removing the @. What am I doing wrong? Thanks!
This is the query I wrote, but it only selects the personel who have
Workgroups and Access Groups assigned to them but I want to show
all the employies and if they don't have Workgroups and Access
Groups assigned to them then show empty cells on DBGrid... and I
know the red part is the problem but I could not build up the logic to
select the matching records and empty records at the same time...
Thanks in advance
SELECT DISTINCT
CARD.LNAME, CARD.MNAME, CARD.CNUM, CARD.CNAME, C_GRP.NAME,
AGRECS.AGNAME, AGRECS.SITENAME, WorkGroups.WGName
FROM CARD INNER JOIN
PersonelWG ON CARD.CNUM = PersonelWG.Cnum INNER JOIN
C_GRP ON CARD.GNUM = C_GRP.NUM INNER JOIN
WorkGroups ON PersonelWG.WGID = WorkGroups.WGID INNER JOIN
AGRECS ON CARD.ACGRP = AGRECS.AGNUM
By the way this is my query structure:
Hi all might seem simple but im having trouble.
how do i include 2 likes in a select:
SELECT from TABLE WHERE name like 'SIB' or 'MIWS' ORDERBY name;
is this valid
Craig
Hi all
There is a stored procedure that updates a "sales" table with the current "sales representative" taken from the "customers" table. I'm changing this mess of cursors into a simple update, my first approach was just to do a select (instead of update) just to verify that the rows selected were the ones that really needed to be changed. The select is very simple but is returning a cartesian product instead of just the 200+ (aprox) rows. I would appreciate if someone took a quick look at this select and see what I'm missing :) (I'm including the code for the cursor as well as the code for the select)
CURSOR:
CREATE PROCEDURE [ReCast_Salesman] AS
-- Recast salesman
Declare @vsite_code varchar(2)
Declare @vcustno varchar(10)
Declare @vsalrep char(4)
Declare @vcustfx smallint
Declare @Ssite_code varchar(2)
Declare @Scustno varchar(10)
Declare @Ssalrep char(4)
Declare @Scustfx smallint
Declare @i int
Declare @intcnt int
Declare @cnt int
Declare @vprodate datetime
Declare @vpro varchar(20)
Declare @vmesg varchar(30)
Declare @vmesg2 varchar(30)
Declare @vmesg3 varchar(30)
Declare @UpdFlag varchar(1)
Declare @Ucnt int
Declare @icnt int
Set @i = 0
Set @cnt = 0
Set @ucnt = 0
Set @icnt = 0
Set @UpdFlag = 'N'
Declare Customer_cur Cursor for
Select site_code, cust_no, cust_sffx, sales_rep
From Customer
where Active_Flag = 'A'
and sales_rep <> 'XXXX'
Order by site_code, cust_no, cust_sffx
For Read only
Open Customer_Cur
Fetch Next from Customer_Cur
into @vsite_code, @vcustno, @vcustfx, @vsalrep
While @@FETCH_STATUS = 0
Begin
Declare sales_cur Cursor for
Select site_code, cust_no, cust_sffx, salesrep
From Sales
Where site_code = @vsite_code
and cust_no = @vcustno
and cust_sffx = @vcustfx
and salesrep <> @vsalrep
Order by Site_code, cust_no, cust_sffx
For Update
Open Sales_cur
Begin
Fetch Next
from sales_cur
into @ssite_code, @scustno, @scustfx, @Ssalrep
While (@@fetch_status = 0)
Begin
--If @vsalrep <> @Ssalrep
-- Begin
Update Sales
set salesrep = @vsalrep
Where current of Sales_cur
set @ucnt = @ucnt + 1
-- End
Fetch Next from sales_cur
into @ssite_code, @scustno, @scustfx, @Ssalrep
End
FetchNext:
deallocate sales_cur
Fetch Next
from Customer_Cur
into @vsite_code, @vcustno, @vcustfx, @vsalrep
End
End
set @vprodate = getdate()
set @vpro = 'Recast Salesman '
set @vmesg = Str(@ucnt) + ' Records Changed'
Insert into Batch
(batchdate, process, message )
Values (@vprodate, @vpro, @vmesg )
Close Customer_Cur
deallocate Customer_Cur
SELECT:
use salesdatamart;
Select A.site_code,
A.cust_no,
A.cust_sffx,
A.salesrep,
From Sales As A
Inner Join Customer As B
OnA.site_code = B.site_code AND
A.cust_no = B.cust_no AND
A.cust_sffx = B.cust_sffx AND
A.salesrep != B.sales_rep AND
B.sales_rep != 'XXXX' AND
B.active_flag = 'A'
Thanks in advance for your help :)
Luis Torres
I have a select statement that is not finding a recordset that I know is there.
SQL_1 = "SELECT cust_id, firstname, lastname, email FROM tbl_customers WHERE email = '" & email & "'"
Works fine on most email addresses, but on email addresses where there is a period in the name it does not find the record. The email string is being submitted via a form. Before the SQL statement above I have the request from the form.
email = Request("email")
Like I said, this select works fine on most email addresses. Im sure this is a formatting issue.
If I manually type the select statement into SQL Query Analyzer it works also, but of couse I am manually typing the email address in there that contains the period in the email address.
I have verified that the request is pulling the correct email address with the period.
The select statement returns no records.
I've been struggling with this problem, hope someone can help. I'm using SQL Server 2005 Express. I have a simple database with 7 tables (m64,m67,m69,m71,m87) all have an identical design(column name, type).
All I want to do is pull out one recordset (if it's in that table) from each of the tables.
This is what I have:
"SELECT * FROM m64, m67, m69, m71, M87 WHERE P_N Like '" & Request.Querystring("P_N") & "'"
If I only enter one table, I get a result.
Hi,I feel so stupid to ask this question, but here it goesWhen I select a column from a table, if the column has a null value Iwant the select to return me a blank. I have done this before but Iforgot, some one help?select name, age from peoplein the above query the name can be null. I do not want to doselect @name=name....Thanks.
View 4 Replies View RelatedHi,I cant figure out how to do this....for example:Select name from mytab order by col1could returnMikeDaveSueSimonPaulFredI would like to show the row number, like in the grid in query analyser. sothe orginal sort order is preservedi.e1 Mike2 Dave3 Sue4 Simon5 Paul6 FredAny ideas?
View 9 Replies View Relatedcan someone read this to me thanks.
SELECT DISTINCT PlanNumber FROM tbmembers WHERE ISNULL(PlanID, '') <> ''
I want to select from a table based of a variable, the variable is either 1 or 2, however is it possible to ask for BOTH 1 and 2 when calling from a variable?I tried this, but it seems to only look for values matching 1 and then stop... SET @FuelType = '1 OR FuelType = 2'SELECT * FROM Engine WHERE FuelType = @FuelType
View 6 Replies View RelatedThis might be too simple of a question to post here. But anyway.... I would appreciate if anyone could answer this:
I have two tables, Visitors and Registrants. The relationship between two tables is that every Registrant is a (web site) Visitor. The primary key of Visitor table (v_id) is foreign key in Registrant table, hence enforcing the 1-to-many relationship between tables. Registrant table has its own PK as reg_id. Essentially, Registrant table contains log of those visitors who in fact registered.
How can I retrieve the rows from Visitors table for users who did NOT register? In other words, just opposite of what I would getting if I JOIN the two tables. Any ideas?
Thanks,
W.
I have searched for what I think is a simple solution.
In my aspx.vb code page, I need to make a connection to SQL Server (my connection strings are in the web.config file) and then make a simple SQL Select to return one or 2 values.
For example, the user will select a customer on the ASP form, then I need to read a couple of default values from that user's record and display them back onto the form.
Can someone provide the sample code or direct me to this? I'm thinking that this is rather simple, but I can't seem to make it work. If I see a sample of exactly what I need to do, such as reading a value using the Northwind sample DB, then I can modify it to fit my table structure, etc.
Thank you.
I know there must be something i'm missing. I'm logged on as sa on the Query Analyzer, and run a simple select statement, or any statement for that matter, and i get an 'INVALID OBJECT NAME' error.
View 5 Replies View RelatedHi,
I have several records in a table of "links", which has the fields ID, UserID, Description and URL. I'm trying to select all the records in this table for a specific user. The stored prodecure I'm using is:
ALTER PROCEDURE dbo.sprocLinkSelect (@UserID uniqueidentifier)
AS
SELECT ID, UserID, description, URL
FROM links
WHERE UserID = @UserID
When I execute the procedure without "UserID" in the SELECT part of the statement it works and returns the correct rows for the UserID entered as a parameter. However, when UserID is included in the SELECT part of the statement (as above), it says:
"No rows affected.
(1 row(s) returned)"
and just shows the column headings, but with no record details beneath. (When the example works as intended, it returns two rows.)
I've tried changing the procedure to take @description as its parameter and return all records with one specific description, and it also only works when UserID isn't included in the SELECT line. I've also tried using SELECT *, but that doesn't seem to work either.
Any help would be greatly appreciated, I'm using SQL Express.
Well my problem lies in that I am generating reports with the data I retrieve from my sql database. However my problem resides in the fact that I am generating one report at a time and if I want to grab each entry in the order in which they were produced its no problem.
IE - Using PowerBuilder 10.0 as an IDE for my application to generate reports.
select i_id into :insp_id from inspection where i_id = :index order by i_id asc using sqlca;
But now if I want to grab them in alphabetical order from another table I have problems.
this is the code I am trying to use maybe I am just thinking it through wrong.
select s_id into :insp_id from section where s_id = :index order by s_name asc using sqlca;
any help is appreciated. Is there a way to grab each row in alphabetical order?
Hi,
I'm trying to return all of rows by applying a simple query to my database, however not all of the rows are being returned. The simple SQL query is:
SELECT `id` FROM `tags` WHERE `tagname`='baseball'
The rows that are left out are those in which the variable being searched for is not the first record listed in the table for a corresponding record. For example, the query of the "tags" table (below) for 'baseball' above returns only ids 10 and 12. id 11 also has a tag "baseball" but it is not being returned. What do I need to add to my query in order to return all of the ids that correspond, and not just the "first" ones? Thanks in advance!
table: tags
id |tagname
10 | baseball
10 | free
10 | stars
11 | fakeguy
11 | baseball
11 | free
12 | baseball
12 | test
12 | fantasy
12 | sports
13 | basketball
13 | hoops
13 | ncaa
I have a table with the name of customers in it. The problem is that I have one column with the full name in it like
|Last First Middle|.
As you can see this is a problem for me.
I need to find a way to put these values into 3 seperate columns.
|Last| First| Middle |
Is there a function to seperate them via a space?
I have been looking on the web and have not found any functions or solutions.
Any help would be most appreciated.
Thanks in advance,
Gene
I am very new to sql and would like some help with a simple select statement. I am trying to pull birthdates using the following code. I can get the query to run but some of the dates are not between the parameters. What am i doing wrong?
SELECT Employees.EmployeeStatusID, People.FirstName, People.LastName, People.BirthDate
FROM Employees FULL OUTER JOIN
People ON Employees.SystemAssignedPersonID = People.SystemAssignedPersonID
WHERE (People.BirthDate BETWEEN '10 - 01 - 1900' AND '12 - 15 - 2007')
ORDER BY People.BirthDate
<<<<<<<<<<<<<<<<<<<<<<<<<< Results
A
KWANG
TAK
10/25/1929 12:00:00 AM
T
ETHEL
MOE
5/24/1933 12:00:00 AM
T
GILBERT
BARAJAS
1/9/1937 12:00:00 AM
A
EDILBERTO
PENA
2/10/1937 12:00:00 AM
Ok, I have two tables with a child/parent or one -> many relationship:
parent_table:
pid int primary key
pname varchar
child_table:
cid int primary key
pid int
cname varchar
Say the contents of these two tables are:
parent_table:
pid pname:
1 Ben
2 Jesse
3 Michael
child_table
pid cid cname
1 1 ben_Child1
1 2 ben_Child2
1 3 ben_Child3
2 4 jesse_Child1
2 5 jesse_Child2
2 6 jesse_Child3
3 7 michael_Child1
3 8 michael_Child2
3 9 michael_Child3
Now what I would like to be able to do is:
select pname, cname
from
parent table a,
child_table b
where a.pid = b.pid
Except! Instead of getting the results in the form of:
Ben ben_Child1
Ben ben_Child2
Ben ben_Child3
...
I would like them in
Ben ben_Child1 ben_Child2
Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?
I've just inherited a system and have some concerns about the speed ofconnections to a remote server (SQL2000). If I do a simple selectstatement on the table below, it takes 14 minutes to retrive 6 millionrows across a 2Mb line. Obviously it's a reasonable amount of data toretrieve, but I would have thought this would be quicker if I'm honest.Run locally, this is 50 seconds.My thoughts are that there may be some issues with our connection (weget general network errors sporadically, which are being looked at),but wanted some thoughts if the performance is acceptable for what itis doing with what is available. I don't think there is a SQL issue,but want to check if this sounds about right.It's early days, so I'm after a general impression of the speed ofretrieval for the amount of data on the available bandwidth. Assuming abest performance scenario, what is the minimum time it should take as abest guess ?ThanksRyanCREATE TABLE [FIELD_VALUES] ([DEALER_DATA_ID] [int] NOT NULL ,[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[FIELD_VALUE] [numeric](15, 5) NULL ,[CHANGED_TYPE] [int] NULL ,CONSTRAINT [PK_FIELD_VALUES] PRIMARY KEY CLUSTERED([DEALER_DATA_ID],[FIELD_CODE]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GO
View 1 Replies View Related
Hi all,
I know this sounds rather dumb but my select statement is running forever!
I am trying to execute the statement through my C# code.
If I try to run the query through sql server management studio, sometimes it runs fine, but sometimes it keeps running and never returns a value.
I am doing something like this:
###########################################################################################
String query = "SELECT studentID from StudentTable WHERE studentDeptID = '100' AND deptName = 'CS'";
SqlCommand command = new SqlCommand(query, connection);
Object myObject = command.ExecuteScalar();
############################################################################################
StudentTable contains roughly 1000 rows.
Somebody please help me out of this. Thanks in advance.
Surya
Hey there everyone,
I'm sure there's a good reason for this, I just have no idea what it is.
If I run a SELECT * statement on one of my tables, the result set is missing one of the records.
If I SELECT that specific row (by identifier or anything else really), it returns just fine.
Any kind of select (e.g. WHERE ID > X) fails to return that specific row.
Any idea why this might be happening?
Thanks in advance for any guidance!
I have 2 SQL 2000 servers. One has been added as a linked server.
They both have a db called claims. If I am on server 1 and want to write a sql statement I am stuck with the write syntax
I tried select patient.* from testedi..claims.
The server is linked using the sa user. I know this is simple but in a hurry and stuck. THe servers are on same domain.
Thanks
I have a table tblCustTrans which contains
custid int
transid int
startdate datetime
value int
the custid, transid and startid are composite primary key.
the table contains more than 10 million records. Now i want to fetch record for
select * from tblcusttrans where startdate > = 10/10/2006 10:00:000 and startdate <= 10/10/2006 11:00:000
This statement is taking more than 2 hours to fetch the data. is there a way to fetch the record with less time
Regards
Hi everyone,
I am new to T-SQL and am trying to do some simple database for fun. Here is my problem and hope you guys can drop me some hint or solution:
OS: Win XP pro
SQL Server 2005 Express
Tool: SQLCMD in command prompt
I created a database and then create a table. Then I tried : select * from contract_Info, it gave me this:
1> CREATE TABLE Contract_Info
2> (
3> Order_No varchar(50) NOT NULL ,
4> Company varchar(255),
5> Product_Name varchar(255) NOT NULL,
6> Discount_Rate varchar(50), --Discount_Rate (%) -> Discount_Rate
7> Status varchar(50) NOT NULL,
8> Quantity varchar(50) NOT NULL,
9> Remainder varchar(50),
10> Deleted bit
11> )
12>
14>
15>
16> INSERT INTO Contract_Info values ('00000', 'DEFAULT','DEFAULT','0','Cancelle
d','0','0',0)
17>
18> go
(1 rows affected)
1> select * from contract_Info
2> go
Order_No Company
Product_Name
Discount_Rate Status
Quantity Rema
inder Deleted
-------------------------------------------------- -----------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------------ -------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- -------------------------------------------------- --------------------------
------------------------ -------------------------------------------------- ----
---------------------------------------------- -------
00000 DEFAULT
DEFAULT
0 Cancelled
0 0
0
(1 rows affected)
1>
In SQL Express Management , the output looks perfectly fine
What is missing??
Thanks for your time
Could someone please tell me why the following SELECT statement...
SELECT ID, SpecimenNr, ScientificName, Locality, TaxonFROM petrander.QueryViewWHERE (InstitutionCode = 1) AND (Collectioncode = 1) AND (ScientificName LIKE N'%le%') AND (Locality LIKE N'%Fakse%') AND (22 IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8))
...gives me 9 rows back, but embedding the exact same statement in the following sproc...
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [petrander].[DynamicQuery] @taxparent int = NULL, @museum int = NULL, @collection int = NULL, @binomen Nvarchar(254) = NULL, @locality Nvarchar(254) = NULLAS SELECT ID, SpecimenNr, ScientificName, Locality, Taxon FROM QueryView WHERE InstitutionCode = COALESCE(@museum, InstitutionCode) AND CollectionCode = COALESCE(@collection, CollectionCode) AND ScientificName LIKE 'N%' + @binomen + '%' AND Locality LIKE 'N%' + @locality + '%' AND (@taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8))
...and passing the exact same parameter values to with the following execute statement...
USE [Geomusdb]
GO
DECLARE @return_value int
EXEC @return_value = [petrander].[DynamicQuery]
@museum = 1,
@collection = 1,
@binomen = N'le',
@locality = N'Fakse'
SELECT 'Return Value' = @return_value
GO
gives me 0 rows!? What is different!?
Any help is greatly appreciated...
Hello All,I've got a DATETIME field, and it includes hour:minutes:second data. I want to do selects where I can simply match on the month, day and year. For instance, something like this:SELECT * FROM QuizAttempts WHERE DateTimeTaken = '1/12/2006'And have it match anything that was taken that day, regardless of *when* it was taken. Any suggestions?Thanks! -Josh
View 2 Replies View Related