Simple Merging A Few Queries In One Query Question
Jan 25, 2008
how can I fill a data set with a multi parameter filter:
I want to create in my form few textboxes and create a query that filters the dataset to show only the specific data.
but the problem is if the user doesn't supply any words in some textbox the dataset is not filled.
it would be a good solution for me if I can either:
for example let's say my query is SELECT * FROM table WHERE column = @parameter,
i want to write before the query something like "if @parameter="" then replace the query syntax near WHERE with * so it looks SELECT * FROM table WHERE column = *.
or:
if there is some character or character series that sql reconizes in a parameter as an asterisk.
I know how to get the results I want with two queries, but would like to use only one. The following is as abbreviated and concise as possible.
My first table [Items] contains two relevant fields: ItemID (int) and GroupNum (varchar). The second table [Call] contains three relevent fields: ItemID (int), Started (smalldatetime), Voicemail (smalldatetime).
These are the results I would like: SELECT GroupNum, Count(NOT Voicemail IS NULL), COUNT(DATEDIFF(n, Voicemail, Started) > 60) OR I would like the results grouped by by the group number, the total count where the voicemail is not null, and the total count where the date difference in minutes between started and voicemail is greater then 60.
I can join the table, and I can€™t get the group number and one OR the other of the two remaining fields, but not both.
can anybody help in combining all the mentioned queries into a single query so as to display all fields in a row.
1.number of imported imported animal type:
select count(*) as import_lic_no from appln_clip ac,consign_animal c,pet p
where ac.ac_id=c.ac_id and p.pet_no=ac.clip_id and ac.appln_id like 'A8%'
2. number of imported animal type that are licensed:
select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id
3.percentage: 2/1*100
select percentage=
((select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id) (select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id))*100
4.local animal type:total pet records - 1
select number=
(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id)
5.local animal type that are licenced:total pet records-2
select number1= (select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)
6.percentage: 5/4*100
select percentage=((select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)/ (select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id))*100
Allow me to preface this by saying I'm really excited about writing stored procedures, etc. in C#!! Now...on to my question. When an application needs a simple result set (i.e., SELECT....), why use C# to write this? In the samples I've seen, the developer ends up writing the select statement anyway in the CLR hosted stored procedure. What would be the benefit? You end up writing more code just to write the same query. Now, executing complex logic is another story. I see HUGE benefits to hosting classes on the data server. Anyway, what is Microsoft's answer to this question? Is it recommended that one still write simple statements in T-SQL and leave the complex stuff to CLR code? Thanks in advance for your advice!
Hi, please excuse me if this seems like a daft or badly formed question. A colleage mentioned that when constructing the where clause in a query that it was important what order the criteria were entered. They weren't sure themsleves but the idea was that starting from the bottom of the query you should add the criteria that will have the effect of reducing the data the most and then work 'up' the query back to the from clause. As a dodgy example, if you have a staff table and you want to run a specific query on all staff over 75 that are male, then:
where gender = "male" and age >- 75 The reasoning being that the age criteria would reduce the size of the data being worked on more than the gender criteria. It's been a while since I've had to look at intermediate tables etc but i'm pretty sure this suggestion will not make any difference to the performance of a query. Thanks,
I have made a simple application with VB6 and SQL 2005 Express.
And after testing it in a network simulated with Virtual-Machine Workstation, the "real world" application was first getting slower and slower, and finally giving timeout errors.
It is even difficult to connect. The application times out always at 30 secs although the ado connection timeout was established longer (45)
I have checked on the machine where the server is installed, just in case the slow down was due to network problems, but it fail locally too.
I have this database running (ignore that that was done in Access, this is being made in Microsoft SQL Server 2005).
What I need to do is if you look at the tbl_events table and the tbl_timekeeperDetails table I need to make a query that:
Lists the names of all timekeepers (whether they are booked for a meeting or not), and the meetings at which they are timekeeping.
The tricky part of this is getting the query to show the timekeepers who aren't assigned to an event.
I have two seperate querys so far, but I'm presuming there must be a way of merging them or something.
I have this code so far: select timekeeperTitle,timekeeperNameFirst,timekeeperNameLast,eventID from timekeeperDetails,events where timekeeperDetails.timekeeperID = events.timekeeperID select timekeeperTitle,timekeeperNameFirst,timekeeperNameLast from timekeeperDetails
If anyone has any ideas, please do post a reply or email me at paul [at] abscond [dot] org
This SQL query has been driving me nuts, I am sure there is a simpleway of doing this but I am afraid it is eluding me at the moment.I have two tables:Table1X Y----------- -----------1 122 416 149 12Table2X Y----------- -----------1 102 123 125 126 32What I am after is a list of the maximum Y values for each value of Xfrom the tables:X Y----------- -----------1 122 413 125 126 329 12Any help would be greatly appreciatedThanks - Jim
I have been trying to export an sql2000 database to sql2005 with no luck. I continuosly get the error
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I have looked upon all support material and have installed the latest sql2005 service pack, but this has not fixed the problem. a simple query as:
select * from [transaction] ta,transactionentry tr
where tr.transactionnumber=ta.transactionnumber and
glacctid=6 and ta.[time]> '3/01/2006'
would produce this fatal error.
the current version I am using is:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
This problem is happening on some of the most important tables in the database.
I have this database running (ignore that the ERD below was done in Access, this is being made in Microsoft SQL Server 2005).
What I need to do is if you look at the users table and the orderContents table I need to make a query that:
Collects the users' names and any products they have bought (preferably shown by title not ID) as well as still showing the users that have not ordered any products.
I get the feeling there's a left join involved, but can't quite see how to do it.
Hello,I have a Database in a SQL Server 2000 where I have different userstables with equal fields like this:id (int)email (varchar)name (varchar)address (varchar)joinedon (datetime)I want to merge all the tables in one taking rows with the same emailbut taking always the more recent fields based on the 'joinedon' field.So if I have this four different rows:Tbl email name address joinedon--------------------------------------------------------------------------------------------T1 Join Bytes! johnathan NULL 01/01/95T2 Join Bytes! NULL barcelona street 01/01/98T3 Join Bytes! john valencia street 01/01/97T4 Join Bytes! john Q NULL 01/01/99And the final row entered in the new table would beTbl email name address joinedon----------------------------------------------------------------------------------------new Join Bytes! john Q barcelona street 01/01/99I am trying doing his with union statements, but i am not getting thereal merging of data. Any clue?Thanks for your help.
Can someone plz refer/recommend any document on query merging? I am working on a database sever. The response time, of view's query has become a challange to me. I have tried everything, the last hope left is query merging. But I didnt find any docs/papers/books on it.
Plz help. Shigs. ============================= Are there those, In this world of brave, Who can tell me, How should I behave, When I am disgraced. =============================
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
I've been writing this stuff for a while, and can't seem to come to the conclusion of how I should be retrieving data and assigning this data to variables.
Since i'm using SQL Server, I'm convinced that I should be using the datareaders GetSqlDouble (or whatever) function, but this would mean i need my local variables to be one of the SQL types. The problem with that is, that there will have to be lots of conversions done by me to be able to use a SQL type in my application.
For instance, I have a class where i'm retrieving dates. In order to retrieve them correctly (Null values included), I need to retrieve them with GetSqlDateTime(), then when it comes time to display the date in a table, i must first check for nulls, then convert to a string. This seems to be very cumbersome. Would I be better off just using GetDateTime(), and the .ToString method, and ignoring Sql Types all together?
so, basically, how are you guys using your sql server data? with the supplied sql types, and doing all of the post-processing work manually? I feel like i'm having trouble conveying my issue...hopefully someone knows what i mean....i'd just like some direction to save trouble in the long run, since i feel like there's got to be a better way...
HiI have the following tables and stored procedure. I need to pass a value tothe stored procedure and have it use the value in a query. After runningthat query it will return an ID which is then used in an insert statement.At present the values in @MovieId int, @UserID int are left empty (see myoriginal code at the bottom of posting). I think this is due to an issuewith when the sql is executed (?).I get the feeling I should have something like this instead, but not sure:ALTER proc inserttransactions@MovieName nvarchar(50) = 'team',@uName nvarchar(50) = 'frank',@FrameNumber int = 0asDECLARE @MovieId int, @UserID int-- Find MovieID from MovieName@MovieId = exec MovieName2Id @MovieName-- Find UserID from UserEmail@UserEmail = exec Email2UserId @uName-- Insert DataINSERT INTO Transactions(MovieId, UserId, FrameNumber)VALUES (@MovieId, @UserID, @FrameNumber)Thanks in advance.========MY CODE=============Tables:CREATE TABLE [dbo].[movies] ([movieID] [int] IDENTITY (1, 1) NOT NULL ,[movieName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[movieDateAdded] [datetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[transactions] ([userID] [int] NULL ,[movieID] [int] NULL ,[FrameNumber] [int] NOT NULL ,[transDate] [datetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[users] ([userID] [int] IDENTITY (1, 1) NOT NULL ,[userEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[userDateRegistered] [datetime] NULL) ON [PRIMARY]GOStored Procedure:ALTER proc inserttransactions@MovieName nvarchar(50) = 'team',@uName nvarchar(50) = 'frank',@FrameNumber int = 0asDECLARE @MovieId int, @UserID int-- Find MovieID from MovieNameSELECT @MovieId = MovieIdFROM MoviesWHERE MovieName = @MovieName-- Find UserID from UserEmailSELECT @UserID = UserIDFROM UsersWHERE UserEmail = @uName-- Insert DataINSERT INTO Transactions(MovieId, UserId, FrameNumber)VALUES (@MovieId, @UserID, @FrameNumber)GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
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.
