Noob Alert! What I Thought Was A Real Simple Query...
Oct 22, 2007
Hi
I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items
I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.
By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.
SELECT itemNumber FROM itemNumberSet
WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable)
ORDER BY itemNumber
Thanks for any suggestions you might have.
J
View 3 Replies
ADVERTISEMENT
Jul 14, 2006
I had originally thought a full outer join would solve this, but alas I'm a roadblock. Perhaps it's because it's a Friday!
The goal is to create the most efficient query that will display results of the daily calls and sales as
this:
Date EmpID Calls Sales
7/1/2006 1 20 5
7/1/2006 2 25 7
7/1/2006 3 NULL 1
7/1/2006 4 10 NULL
The problem is a simple full outer join ends up ignoring EmpID 3 who has no Calls in t1, but I still want that row displayed in the results. Any ideas? TIA
create table t1 (Date smalldatetime, EmpID int, Calls int)
create table t2 (Date smalldatetime, EmpID int, Sales int)
insert into t1
values ('7/1/2006', 1, 20)
insert into t1
values ('7/1/2006', 2, 25)
insert into t1
values ('7/1/2006', 4, 10)
insert into t2
values ('7/1/2006', 1, 5)
insert into t2
values ('7/1/2006', 2, 7)
insert into t2
values ('7/1/2006', 3, 1)
View 5 Replies
View Related
Sep 11, 2007
Two tables:FruitfruitID, fruitNameBasketbuyerID, fruitID(ie. we can see which buyer has what fruit in their basket)I simply want to display all available fruit and whether or not it'sin a specific persons' basket.SELECT Fruit.fruitID, Fruit.fruitName, IsNull(buyerID, 0)FROM Fruit INNER JOIN Basket ON Fruit.fruitID = Basket.fruitIDWHERE Basket.buyerID = 12but this just gives me what's in buyer 12s' basket.What am I doing wrong? Am I a basket case...
View 2 Replies
View Related
Jul 20, 2005
It is my understanding that Views cannot have parameters. Also thatstored procedures can not be queried. My problem is this:I want to select the rows that match a certain parameter.From that I want to select the most current 20 rows (there is a datefield).From that I want to select the lowest 10 rows based on a numericfield.Finally I want that to be input to a report and some calculations.What this basically is the selection for USGA Golf Handicap Index. Itis the most current 20 rounds of golf by a golfer, then the best 10 ofthose 20 and then finally the calculation.Any help would be appreciated.
View 3 Replies
View Related
Apr 11, 2006
Hell, I host web pages on a server(s) that I own, located in a datacenter.I am trying to figure out how to properly liscnese sql server 2005.I have called microsoft, and the people i talk to dont seem to understand my situation. They keep asking me about the number of employess i have, which is totally irrelevant. Let me give you a basic example.Example 1.My personal web page has a blog on it. The blog data is stored in a SQL database. Its a popular blog, thousands of anonymous people reading my blog every month via my webpage. The only "thing" accessing the SQL databse is an asp.net script i write, which then turns around and presents the data via html over the www. just like every other blog in the universe. Example 2.I sell artwork over the internet via my web page. My web page uses a shopping cart system which makes use of an SQL database to keep track of inventory and orders etc. No one ever tries to connect to my database, they just use the shopping cart on my web page which of course connects to the database.Now according to the MS liscensing documentation it seems that i can buy sql server w/ 5 device CAL's. Since I only have 1 or 2 webservers accessing the database server, that should be no problem right?So my basic question is this: is a single Device CAL enough to allow one webserver to connect to one sql server and then show dynamic content to thousands of anonymous users (whoever happens to visit my sites?).Thank you very much for any help you can provide.
View 1 Replies
View Related
Jul 23, 2005
I'm wondering how/why this query works. Trying to get my head wrappedaround SQL. Basically the Query deletes from the Import table allrecords that are already in FooStrings so that when I do an insert fromthe FooStringsImport table into the FooStrings table, then I won't getprimary key violations.DELETE FROM FooStringsImportWHERE EXISTS(SELECT * FROM FooStringsWHERE FooStringsImport.FooKey = FooStrings.FooKey)It seems to work fine, but I'm wondering about how the EXISTS keywordworks.(SELECT * FROM FooStringsWHERE FooStringsImport.FooKey = FooStrings.FooKey)This part is going to return only records from FooStrings correct? Ordoes it do a cartesian product since I've specified more than one tablein the WHERE statement?I wonder if it only returns records in FooStrings, then I don't see howa record from FooStringsImport would "EXISTS" in the records returnedfrom FooStrings.The reason I wondered about the cartesian product is because, if onlyFooStrings is specified in the FROM part of the SELECT statement, thenI was thinking it is only going to return FooString records. Theserecords would then be returned by the select statement to the WHEREEXISTS, which would look for FooStringImport records, but would findnone because the select statement only returned FooString records.I'm guessing maybe because it has to do a cartesian product to evaluatethe WHERE Pkey's equal, then the "SELECT *" just goes ahead and getsALL the fields, and not just those in FooStrings.FooStrings and FooStringsImport are identically structured tables,where the FooKey is set as the primary key in each table:CREATE TABLE [dbo].[FooStrings] ([FooKey] [bigint] NOT NULL ,[Name] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[StartDate] [datetime] NULL ,[EndDate] [datetime] NULL ,[Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThanks in advance. I'm so appreciative of the help I've gotten here,as I've been able to write several very useful queries on my own nowafter everyones help and plus lots of reading on my own.
View 4 Replies
View Related
Dec 22, 2005
Question:
How do you concatinate fields in SQL? I tried in a SQL query, but did not have any success. I'm coming from MS Access where it is simple to add fields together.
*** MS Access SQL ***
SELECT Table1.Field1, Table1.Field2, Expr1: Table1.Field1 & " - "& Table1.Field2
FROM Table1
*** END ***
You could add fields and put characters between the two different fields like a space and/or some sort of seperation character (/,-,_,:) in an expression. Then you could call that expression from whatever you wanted.
Is there a way to do this in SQL Express 2005? Do I do it in a query? Can I add seperation characters?
Thanks
PS. If you have a good link to the basics of SQL queries, I would look at that as well.
View 6 Replies
View Related
Jul 20, 2005
Hi!How to create an alert that responses in case of long querya. I haven'tnoticed any counters that deals with these kind of situations?Thx for your help!
View 3 Replies
View Related
Feb 27, 2007
Hello,
I've been trying to fill a tree view based on some criteria in another table.
I have the standard table with a ID - ParentID - Text
when I bind the table everything works fine.
What I need to do is display only the tree items based off a username and ID in another table.
Files can be found http://www.cthere.com/treeviewhelp.zip
EXAMPLE:
tblTourgroup(UserName = Jim)
tblTourgroup (GroupID = 110)
tblGroups (GroupID = 110)
Results from tblGroup:
- RESTAURANTS
- Cuisine
- Steakhouse
Query Results should be layed out like tblGroups
Any ideas?
This is what I have so far... But it does not traverse up and get the other nodes..
USE SQL2005_335573_cplanet;
GO
WITH DynamicTree (ParentID, GroupID, GroupName)
AS
(
SELECT e.ParentID, e.GroupID, e.GroupName
FROM tblGroups AS e
INNER JOIN tblTourGroup AS edh
ON e.GroupID = edh.GroupID
)
SELECT e.ParentID, e.GroupID, e.GroupName
FROM DynamicTree as e
INNER JOIN tblTourGroup AS dp
ON e.GroupID = dp.GroupID
WHERE dp.UserName = N'Test'
GO
View 1 Replies
View Related
Feb 18, 2002
I know that you can alter the maximum characters per column value in Query Analyzer by using the menu item 'TOOLS' then 'OPTIONS', then selecting the 'RESULTS' tab. I'm convinced that there is a way to do this programmatically (need it for a stored procedure) but cannot figure out how and would appreciate any help given.
Thanks,
rdchalker@hotmail.com
View 1 Replies
View Related
Apr 29, 2004
The query below gets percentage of coils for a location by anneal cycle. It does this correctly (ie, if you change count(*)/b.total to count(*), b.total ... show both columns, the numbers its pulling are correct), but in its current state (trying to divide count by total), it always returns a zero (no decimal values).
If I change that section to : (count(*)*100/ b.total*100)/100, I get integer percentages: no decimals (this is a problem because some records round off to zero). I'm pretty sure this is the entire problem (why I'm getting zeros as written below), but I don't have a clue how to fix it. Is this some kind of server setting that allows the query to only return decimal values or something?
select a.department, a.cycle, count(*)/ b.total percentage
from inventory a join (select department, count(*) total from inventory
where location not like 'bas%' and archived = 0 and quality_code = 'p'
group by department) b on a.department = b.department
where a.archived = 0 and a.quality_code = 'p' and location not like 'bas%'
group by a.department, a.cycle, b.total
order by a.department, a.cycle
View 2 Replies
View Related
Jun 21, 2015
How can I turn this query into an Email alert if one of the counts are GT 50. The Source are machines that I capture counts(Unprocessed_Cntr) from every 5 minutes and load to the Load_Time_Capture table. I also add a datetime to each capture(Unprocessed_Capture ).
If any individual source has a count > 50 or if the combined(ALL) GT 50 send an email to support person.
SELECT
CASE GROUPING([Source])
WHEN 1 THEN 'ALL'
ELSE [Source] END AS 'Input Source',
avg([Unprocessed_Cntr]) as 'Average Queue Past 15 Min'
FROM Load_Time_Capture
WHERE Unprocessed_Capture >= DateADD(mi, -15, Current_TimeStamp)
GROUP BY [source] WITH ROLLUP
View 4 Replies
View Related
Dec 18, 2006
Hello,
I'm creating a website for a student organization at my university. Basically, I have an Excel file with a list of contacts that the members of our organization need to be able to search on our website.
The only field that I need our members to be able to search is "contact location." So for example, a member might want to know if our organization has any contacts in Spain, so the member would type "spain" into the "contact location search" field" and would get results.
I really don't known where to turn... I need the easiest thing that would be manageable for a novice. It doesn't need to be an intense search function; just something to make the website functional (so it obviously needs to be uploadable to a server; the server has PHP capability...no idea if that has any relevance). I've been searching around all day and have come across potentials like PHP, SQL, XML, etc. but have no idea which way to turn... I have Office and Adobe Studio programs at my disposal.
Thank you all-powerful and all-knowing web gods! I owe you my deepest gratitude!
Mikey
:beer:
View 1 Replies
View Related
Jan 30, 2015
which have a lot of impact to database performace since it do not fit onto memory and spill over to disks.
The question is when i ran a profiler and tried to catch a sort warning i cannot find the query which cause the alert.
View 1 Replies
View Related
Nov 21, 2005
SELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullNameFROM Employee e INNER JOIN EmployeeEval ev ON ev.PeriodID = @Period WHERE (ev.Approved = 0) AND (e.DeptID = @deptID)GOI want to select everyone from the employee table in a dept (determined by DDL) who has either a) had a review and not been approved yet - or b) has not had a review yet ---- all employees are in the employee table -- and all reviews are placed in the employeeeval table.
View 4 Replies
View Related
Apr 8, 2008
So I've been working on this project and I've finally gotten into the program stages. However, I've realised some things that I need to change to some of my stored procedures so that they work the way I want them to inside the program. Anyways I have this stored procedure called "Delete_Minors" and what it originally did was delete any class found in this table called MinorRequiredClasses that matched that minor and was not considered complete. I realised that this would be a bad decision to delete classes in a minor and not consider any other majors/minors a user might have that also require those classes...SO I thought I might be able to come up with a query but it doesn't seem to work. Below are the tables the query/stored procedure deals with as well as the old procedure and the new one (that I attempted to work the way i wanted it to).
Code Snippet
DECLARE @studid int
DECLARE @minorid varchar(50)
SET @studid = 0
SET @minorid = 'Computer Science'
IF (SELECT COUNT(*) FROM Student_Minors sMinors WHERE sMinors.MinorID = @minorid AND sMinors.StudentID = @studid) > 0
BEGIN
DELETE FROM Student_Classes
WHERE StudentID = @studid AND Completed = 0
AND ClassID IN (SELECT minReqC.ClassID
FROM MinorRequiredClasses minReqC
WHERE minReqC.MinorID = @minorid)
AND ClassID NOT IN (SELECT majReqC.ClassID
FROM MajorRequiredClasses majReqC
WHERE majRecC.MajorDisciplineID IN (SELECT sMajors.MajorDisciplineID
FROM Student_Majors sMajors
WHERE sMajors.StudentID = @studid))
DELETE FROM Student_Minors
WHERE StudentID = @studid AND MinorID = @minorid
END
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MinorRequiredClasses] Script Date: 04/07/2008 22:49:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MinorRequiredClasses](
[MinorClassID] [int] IDENTITY(0,1) NOT NULL,
[MinorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MinorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MinorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_MinorName] FOREIGN KEY([MinorID])
REFERENCES [dbo].[Minors] ([MinorID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_MinorName]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MinorRequiredClasses] Script Date: 04/07/2008 22:49:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MinorRequiredClasses](
[MinorClassID] [int] IDENTITY(0,1) NOT NULL,
[MinorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MinorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MinorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_MinorName] FOREIGN KEY([MinorID])
REFERENCES [dbo].[Minors] ([MinorID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_MinorName]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MajorRequiredClasses] Script Date: 04/07/2008 22:50:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MajorRequiredClasses](
[MajorClassID] [int] IDENTITY(0,1) NOT NULL,
[MajorDisciplineID] [int] NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MajorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MajorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MajorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MajorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MajorRequiredClasses] CHECK CONSTRAINT [FK_MajorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MajorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MajorRequiredClasses_MajorDisciplineID] FOREIGN KEY([MajorDisciplineID])
REFERENCES [dbo].[MajorDisciplines] ([MajorDisciplineID])
GO
ALTER TABLE [dbo].[MajorRequiredClasses] CHECK CONSTRAINT [FK_MajorRequiredClasses_MajorDisciplineID]
OLD QUERY
DECLARE @studid int
DECLARE @minorid varchar(50)
SET @studid = 0
SET @minorid = "Computer Science"
DELETE FROM Student_Classes
WHERE StudentID = @studid AND Completed = 0
AND ClassID IN (SELECT minReqC.ClassID
FROM MinorRequiredClasses minReqC
WHERE minReqC.MinorID = @minorid)
NEW QUERY
DECLARE @studid int
DECLARE @minorid varchar(50)
SET @studid = 0
SET @minorid = 'Computer Science'
DELETE FROM Student_Classes
WHERE StudentID = @studid AND Completed = 0
AND ClassID IN (SELECT minReqC.ClassID
FROM MinorRequiredClasses minReqC
WHERE minReqC.MinorID = @minorid)
AND ClassID NOT IN (SELECT majReqC.ClassID
FROM MajorRequiredClasses majReqC
WHERE majRecC.MajorDisciplineID IN (SELECT sMajors.MajorDisciplineID
FROM Student_Majors sMajors
WHERE sMajors.StudentID = @studid))
Let me know if you need more information...Hope someone can help!
View 4 Replies
View Related
Feb 3, 2004
I have a hunch this one is going to be a resounding "no", but I thought I'd try anyways.
I have a report that uses a user defined date range many, many times throughout the datasource. Ideally, I would like to pass a query declaring and setting variables and let sql server (2000) sort out the dirty work. Essentially I'm working on something that would look like this:
DECLARE @sDate AS DATETIME
DECLARE @eDate AS DATETIME
SET @sDate = 'this string gets constructed during the On Open event of a report
SET @eDate = 'same thing here
SELECT lotsOfStuff, (SELECT oneOfManySubSelects FROM t2 WHERE t2.field BETWEEN @sDate AND @eDate)
FROM somewhere
WHERE somefield BETWEEN @sDate AND @eDate
I have some five subselects that are dependent on this daterange. I can construct the entire string purely in VB, but it's messy and rather tedious. Ideally I'd like to set the variable ONCE at runtime and be done with it. This way, I keep a full record source that calls @sDate and @eDate. Then I simply set the variables and insert them before the query.
The problem is Access doesn't seem to know how to pass the query without trying to parse the variables itself. So it gets mad that @sDate and @eDate haven't been defined for each occurance. I'm looking for a way to make access ignore the fact that there are variables in the query, and pass it as-is to the sql server.
Thoughts?
View 3 Replies
View Related
Feb 7, 2004
HI FRIENDS,
IS THERE ANY PERFORMANCE IMPACT WHEN I USE "CHAR" AS A DATA TYPE INSTEAD OF USING "INT" FOR RETRIEVING DATA OR FOR SOME COMPLEX QUERY.
THANKS
WITH BEST REGARDS,
DHIRAJ
View 3 Replies
View Related
Oct 6, 2006
I assume that MS has a directive never to change the format of SSIS raw files...
However, what I'd like to know is that when I'm planning long-term systems where I've got backups of data (staging, logging, whatever) using raw files, can I be assured that future versions of SSIS will be able to read those raw files?
I assume a certain level of backwards compatibility, however, I'm just curious if I should think about building processes into my projects that would factor that in and rebuild raw files everytime a new/major release of SSIS comes out.
Phil
View 5 Replies
View Related
Nov 15, 2007
So, Jimmy G helped me out with it in showing a little bit how to do it. SqlCommand command = new SqlCommand(, object>)
SqlParameter param = new SqlParameter();
param.ParameterName = "@return";
param.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(param);
command.ExecuteNonQuery();
//get the return value
int val = int.Parse(command.Parameters[0].ToString());
Where I get lost is in the declaring of a new sqlcommand and sqlparameter. Can you please spell out where to use this and if I need to change my SQLdataSource. I currently was trying to use it in the OnClick of a button. What I had did the following
Protected Sub CreateIssue_Click(ByVal sender As Object, ByVal e As System.EventArgs) dim returnValue as integer 'how do I get a return value from the stored procedure executed in 'insertissue.insert() here to a variable? InsertIssue.Insert() Response.Redirect("/addarticletoissue") End Sub
again, thank you for your help and patience with such a beginner =)
View 2 Replies
View Related
Dec 29, 2003
I have a production 60GB database set to Full Recovery and every 15 minutes I am log shipping to a Stand by Server .
During the production hours there are no problems but at night when I run DBCC DBREINDEX, the log grows to 22GB and because of this I have a problem sending this over the network to the stand by server.
I tried changing the recovery model to Bulk_Logged but the there is no difference in log file backup size.
AnyIdea
View 1 Replies
View Related
Aug 13, 2015
Can you use the below query to get CPU high utilisation alert purposes for both named and default instance? or, do I need to make any changes here (@wmi_namespace=N'.ROOTCIMV2' ) ?
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'CPU_WM_Utilization_Check',
@message_id=0,
@severity=0,
[code]....
View 2 Replies
View Related
Oct 1, 2007
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.
View 2 Replies
View Related
Jan 7, 2008
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
View 2 Replies
View Related
Mar 18, 2008
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
View 2 Replies
View Related
Apr 21, 2005
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".
Many thanks
accelerator
View 2 Replies
View Related
May 15, 2005
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"
View 4 Replies
View Related
May 28, 2006
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?
View 2 Replies
View Related
Mar 7, 2003
Hi,
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
Thanks a lot,
Heta
View 2 Replies
View Related
Aug 21, 2004
Hi,
I have a very simple question:
declare @treshold int
set @treshold = 10
SELECT
dbo.fn_Calc(t.column1) as calc,
t.column2
FROM
mytable t
WHERE
dbo.fn_Calc(t.column1) > @treshold
I can't think of a way to get rid of the function call in the WHERE clause.
Is this actualy a problem ?
I mean does realy the function fn_Calc execute 2 times in this statement and isn't this a performance issue then?
thanks
View 2 Replies
View Related
Jan 17, 2002
Hi,
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.
Thanks in advance for your help
Kevin
View 1 Replies
View Related
Oct 21, 2006
This is my query...I am attempting to only return the records for each
sales_contact with the greatest issue_id
select
sc.sales_contact_id
, idd.issue_id
, sr.code
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
thanks in advance for your help.
View 1 Replies
View Related
Jan 7, 2005
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:
Code:
-----
| ID |
-----
| 1 |
| 2 |
| 1 |
| 4 |
| 1 |
| 4 |
------
Would return something like:
Code:
----------------
| ID | Count |
----------------
| 1 | 3 |
| 2 | 1 |
| 4 | 2 |
-----------------
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.
View 3 Replies
View Related
