Sub Selects Vs. Joins
Jun 8, 2005
What is your opinion using Sub Selects versus Joining Tables?
SELECT (SELECT COMPANY_NAME
FROM COMPANIES
WHERE COMPANY_ID = c.COMPANY_ID) AS 'Company Name'
FROM COMPANY_ORDERS c
vs
SELECT co.COMPANY_NAME AS 'Company Name'
FROM COMPANIES c, COMPANY co
WHERE c.COMPANY_ID = co.COMPANY_ID
I'm not having any problems, just curious what everyone is
practicing and is "system-actically" more resourceful. I
personally like using Sub Selects (or Sub Queries) so this way I
wouldn't have to deal with complex joins and where conditions,
especially when your just dealing with a releational table.
View 5 Replies
ADVERTISEMENT
Nov 3, 2000
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign
then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033
4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators.
The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
Any ideas on the principles involved here.
View 1 Replies
View Related
Aug 11, 2005
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
View 3 Replies
View Related
Apr 3, 2008
I'm self-taught on T-SQL so forgive me if this is a dumb question. If I have a stored procedure containing the following...
IF [condition]
SELECT ...
ELSE
SELECT ...
Would it be more efficient/proper/etc. to break those two SELECTs out into their own SPs and execute them from the IF statement?
IF [condition]
EXEC [SP]
ELSE
EXEC [SP]
I've got a number of pretty lengthy SPs that I think are pretty tight but if I can make them more efficient by breaking them down into smaller tasks, I'd rather do that.
thanks!
Ron Moses
View 1 Replies
View Related
Apr 19, 2008
I've got two queries that I'm using, but I would like to know if I could combine them into one query.
SELECT MAX(ENTRY_ID) FROM MYDB.CALLS
.......
SELECT * FROM MYDB.CALLS WHERE ENTRY_ID = ?(result from previous query)
as always I appreciate any help.
thx
View 6 Replies
View Related
Apr 29, 2008
Hallo, i need some help
I've done this script
SET NOCOUNT ON
DECLARE @login varchar(50)
DECLARE cur CURSOR FOR
select distinct user
from products with (nolock)
OPEN cur
FETCH NEXT FROM cur
INTO @login
WHILE @@FETCH_STATUS = 0
BEGIN
select last,first from users where login=@login;
FETCH NEXT FROM cur
INTO @login
END
CLOSE cur
DEALLOCATE cur
But the result is not what i originally wanted.
It's generates multiple select tables with only one row.
I need only one table with all the rows generated.
I tried this:
SET NOCOUNT ON
DECLARE @login varchar(50)
DECLARE cur CURSOR FOR
select distinct user
from products with (nolock)
OPEN cur
FETCH NEXT FROM cur
INTO @login
IF @@FETCH_STATUS = 0
BEGIN
select last,first from users where login=@login;
FETCH NEXT FROM cur
INTO @login
END
WHILE @@FETCH_STATUS = 0
BEGIN
union
select last,first from users where login=@login;
FETCH NEXT FROM cur
INTO @login
END
CLOSE cur
DEALLOCATE cur
But it ended in an error on the union command.
Can you help me?
Thanks and sorry for my bad english :)
Debiru
View 6 Replies
View Related
Nov 13, 2007
This is my statement:
SELECT TOP 10 * FROM Team_Roster ORDER BY P_Goals DESC
This is what I get:
Place Number Player Name Team Year Position Goals
1 44 Tyler, Bret Brunswick 2004 Webmaster 17
2 29 House, Tanner Bonny 0 5
3 25 Dee, Robby Cheverus 0 4
4 7 Marshall, Jeffrey Cheverus 0 3
5 27 Ramsey, Travis Biddeford 0 3
6 3 Thompson, Joe Brunswick 1960 Wired 3
7 5 VanDyk, Josh Cheverus 0 2
8 3 Dimmen, Jeff Cheverus 0 2
9 79 Danis-Pepin, Simon Cheverus 0 2
10 45 Oinker, Jeremy Biddeford 1999 Wonderboy 2
11 98 Good, Wicked Brunswick 2007 Lefty 2
12 21 Duffy, Matt Biddeford 0 2
What the heck is going on?
See the following list of: rows desired vs. rows returned
SELECT TOP n
When n=1, actual number of rows returned = 1 etc.
" 2 " " 2
3 3
4 12
5 12
6 12
7 12
8 12
9 12
10 12
11 12
12 12
13 16
14 16
15 16
16 16
17 27
Any ideas?
View 4 Replies
View Related
Oct 16, 2007
how can i make this stored procedure:
INSERT INTO tableX (field1, field2, field3) VALUES (value1, value2, value3)
where value1 is field11 in table11
and value2 is field22 in table22
and value3 is field33 in table33
so all values are stored in another tables
View 1 Replies
View Related
Feb 2, 2006
I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example:
table Company, fields CompanyID, CompanyName
table Address, fields AddressID, AddressName
table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID
CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter:
Select c.CompanyID, c.CompanyName, a.AddressName
FROM Company c
LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID
LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID
WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%))
There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address.
Is there any way to optimize this query, or is it a problem with the base table implementation?
Any advice would be appreciated.
View 6 Replies
View Related
Nov 5, 2007
I remember when studying for exam 70-528 that I read a practice question that said it was better to return 2 select satements rather than a join on two tables as this would be more efficent. The question from MCPD Self Paced Training Kit: Designing and Developing Web Based Applications Using Microsoft .NET Framework (Certification Series): Designing and Developing ... Applications Using Microsoft .NET Framework was:
You are an ASP.NET application developer. You are participating in the design of a Web retail application.
The application accepts orders from the clients and stores them in a SQL Server 2005 database. Each order contains header information (order date, client information, and shipping address) and details information, which includes information about ordered products. Each detail line contains information about one ordered product and its quantity and price.
All of the preceding information will be stored in two database tables: one table called Orders for the order header and another table, called OrderDetails, for order details in which each record in the orders table could have one or more related records in the OrderDetails table.
When the user selects the specific order ID, the application is supposed to select all the information about the order from the database, and you need to create a stored procedure that allows selection of information about all the fields for the stored order from the database, based on a provided order ID value.
Which of the following stored procedures do you recommend creating in a database to achieve the best performance on a database server side and reduce network traffic between application and database server?The answer was:CREATE PROCEDURE ORDERS_SELECT_ORDER @ORDER_ID INT AS
SELECT * FROM Orders WHERE OrderID=@ORDER_IDSELECT * FROM OrderDetails WHERE OrderID=@ORDER_ID
RETURNAs oppose to the traditional:
CREATE PROCEDURE SP_SELECT_ORDER @ORDER_ID INT AS
SELECT * FROM Orders LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderlD WHERE Orders.OrderID=@ORDER_IDThe explanation was:
To achieve the best performance, avoid names of the stored procedures that start with sp_ prefix. If the name of the stored procedure starts with sp_ SQL Server always looks for the stored procedure in the master database first because all the system stored procedures start with the same prefix and are stored in a master database. It requires extra effort from the server to locate the stored procedure. This occurs even if you qualify the stored procedure with the database name. To avoid this issue, use a custom naming convention rather than the sp_ prefix.
In addition, using a join inside of the stored procedure for the provided scenario will require an extra task from the server to join the data and produce a joined result. It will also lead to the redundant data for the header information that will be duplicated with each detail of the order and will increase traffic between the client and the server. Using only two separate SELECT SQL statements will allow you to avoid a join operation and reduce the size of the returned data to the client.Any one got any views on this as I have never actually seen this demostrated in a sample applcation. Is it a lot more efficent?ThanksScott
View 2 Replies
View Related
Oct 7, 2004
I'm about to build a batch of procedures that will be returning one row of data that is created by referencing the same table multiple times. The example below is very simplified but you'll see the differences.
Which is better:
Option A:
SELECT a.Field1, a.Field2, b.Field1, b.Field2
FROM MyTable a, MyTable b
WHERE a.ID = 10
AND a.Type = 1
AND b.ID = 10
AND b.Type = 2
Or Option B:
SELECT a.Field1, a.Field2, b.Field1, b.Field2
FROM MyTable a
JOIN MyTable b ON a.ID = b.ID
WHERE a.ID = 10
AND a.Type = 1
AND b.Type = 2
Thanks!
View 3 Replies
View Related
Sep 19, 2005
I have a statment that checks fine in query analyser but fails in my webmatrix asp net page. I wonder if it's being too picky?Dim queryString As String =SELECT distinct [CSULOG5].[status] , [CSULOG5].[lmca_nbr] FROM [CSULOG5] works butDim queryString As String = SELECT distinct [CSULOG5].[status] + [CSULOG5].[lmca_nbr] FROM [CSULOG5] does not
View 3 Replies
View Related
Apr 14, 2008
Hi All,
I have a table that looks like this:
Date | Event
01/01/2008 | CRG
01/01/2008 | MAG
01/01/2008 | CPY
01/02/2008 | CPY
01/02/2008 | MAG
01/02/2008 | MAG
01/03/2008 | CRG
01/03/2008 | CPY
I need to display the data like this:
Date | CRG | MAG | CPY | Total
01/01/2008 | 1 | 1 | 1 | 3
01/02/2008 | 0 | 2 | 2 | 4
01/03/2008 | 1 | 0 | 1 | 2
Thank you so much for your help.
View 3 Replies
View Related
Mar 21, 2014
I have two select statements:
SELECT COUNT(id) as 'clickthrough' FROM stats WHERE objectid =@id AND eventtype = 'newsletter-clickthrough'
SELECT COUNT(id) as 'open' FROM stats WHERE objectid =@id AND eventtype = 'newsletter-open'
They are identical except counting different eventtypes. There must be a better way to do this, so I can get both bits of data in one query.
View 3 Replies
View Related
Jul 9, 2014
I am trying to write a query that selects all the mis_key that is not in the subquery. The left join query is working but when nested it comes out blank. Here is the query:
select *
from [TLC NEW Inv. Anal.].dbo.['Home Decor$']
WHERE NOT EXISTS
(SELECT *
FROM [TLC New].DBO.['Fabric or basket accessory$'] LEFT JOIN [TLC NEW Inv. Anal.].DBO.['Home Decor$']
ON [TLC New].DBO.['Fabric or basket accessory$'].[Item #]=[TLC NEW Inv. Anal.].DBO.['Home Decor$'].mis_key)
View 1 Replies
View Related
Jun 1, 2007
Hello, i would like to take the following select statements and make one table out of them. However, i want each statement to be its own column. The Union adds all of the statements into one colummn. Ultimately, i would like to place these into a stored procedure. Any help would be apreciated. Here are the statements:
select count(chalf_upd) as CHalfFile from elpc where elpc.chalf_upd is not null
select count (halfupd) as HalfFile from elpc where elpc.halfupd is not null
select count (cstaff_upd) as CStaffFile from elpc where elpc.cstaff_upd is not null
select count (staffupd) as StaffFile from elpc where elpc.staffupd is not null
select count (coffice_up) as COfficeFile from elpc where elpc.coffice_up is not null
select count (officeupd) as OfficeFile from elpc where elpc.officeupd is not null
select count (signupd) as SignFile from elpc where elpc.signupd is not null
select count (informupd) as ISignFile from elpc where elpc.informupd is not null
select count(staff_color_lastupd) as InStaffFile from staff_graphics_lastupdate where staff_graphics_lastupdate.staff_color_lastupd is not null
Strange Game. The only winnng move is not to play.
View 6 Replies
View Related
Jul 6, 2007
I have a Stored Procedure that performs a simple SELECT. The Selecthave no locking hints or other hints and the database is set up in astandard configuration.The problem is that the SELECT runs for some time and while it isrunning I can see (in the profiler) that other SPs with simple SELECTsare held waiting until "my" SP has finished. The other SPs may beother instances of the same SP as the one I'm running. All SPscontains simple SELECTs and should only hold shared locks.I have also checked if there are any locks holding the other SPs back- there isn't any.So my question is: What resouce can hold out other simple SELECTs inthis situation? Where should I look to identify the resource?RegardsBjørn
View 1 Replies
View Related
Apr 7, 2008
Ok, here's the deal. At the moment I have problems with extracting some data as the table structures refer to itself. To get the data I want I have three sql-queries setup as views. the first one takes the raw data out of the table:
fsp_marken:
Code Snippet
SELECT TOP (100) PERCENT
LFDNR,
KRITERIUM,
EINORDNUNG,
CASE kriterium WHEN 1 THEN 'Marken' ELSE CASE kriterium WHEN 2 THEN 'Markenhauptgruppe' ELSE CASE kriterium WHEN 3 THEN 'Markenuntergruppe' END END END AS Gruppierung
FROM dbo.ARTEINORDNUNG
WHERE (KRITERIUM = 1) AND (LFDNR <> 0) OR
(KRITERIUM = 2) AND (LFDNR <> 0) OR
(KRITERIUM = 3) AND (LFDNR <> 0)
ORDER BY KRITERIUM
The second one takes the necessary information from the first statement fsp_marken and fills up additional info in this case the article number:
fsp_marken2:
Code Snippet
SELECT
dbo.ARTIKEL.ARTNR1,
CASE fsp_marken.kriterium WHEN 1 THEN fsp_marken.einordnung END AS Marken,
CASE fsp_marken.kriterium WHEN 2 THEN fsp_marken.einordnung END AS Markenhauptgruppe, CASE fsp_marken.kriterium WHEN 3 THEN fsp_marken.einordnung END AS Markenuntergruppe
FROM dbo.ARTIKEL INNER JOIN
dbo.AEINORD ON dbo.ARTIKEL.LFDNR = dbo.AEINORD.ARTNR INNER JOIN
dbo.FSP_MARKEN ON dbo.AEINORD.KRITERIUM = dbo.FSP_MARKEN.KRITERIUM AND
dbo.AEINORD.EINORDNUNG = dbo.FSP_MARKEN.LFDNR
GROUP BY dbo.ARTIKEL.ARTNR1, dbo.FSP_MARKEN.KRITERIUM, dbo.FSP_MARKEN.EINORDNUNG
The third one takes the results from fsp_marken2 and groups them by artnr, getting only one resultset per article number:
fsp_marken3:
Code Snippet
SELECT
ARTNR1,
MAX(ISNULL(Marken, CHAR(NULL))) AS Marken,
MAX(ISNULL(Markenuntergruppe, CHAR(NULL))) AS Markenuntergruppe, MAX(ISNULL(Markenhauptgruppe, CHAR(NULL))) AS Markenhauptgruppe
FROM dbo.FSP_MARKEN2
GROUP BY ARTNR1
What I need is doing all the stuff in just one sql-statement relating on ARTIKEL.ARTNR1 as primary. I don't know If this can be accomplished...
View 10 Replies
View Related
Feb 5, 2008
I need help in figuring out the proper way of writing a stored procedure out correctly to get my desired datasource. In my ocnIdToRatePlanOptions table, I will recieve a parameter via request.querystring @ocnId to filter out my result set for ocnIdToRatePlan table. Based on the ocnId filtered I want it to select the corresponding tables too.So, if a querystring is passed that is 3955 in my ocnIdToRatePlanOptions table, I want it to use it to create a select for RatePlan1. If a querystring is passed that is 1854 in my ocnIdToRatePlanOptions table, I want it to use to create a select for RatePlan2. Is this possible? ocnIdToRatePlanOptions Table [otrpoRefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL,[FKrpoRefId] [int] NOT NULL,1, 3955, 12, 1854, 2RatePlan1 Table[rp1RefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL[fee] [decimal](18, 2) NOT NULL1, 3955, 1.002, 2350, 2.00RatePla2 Table[rp2RefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL,[q_0_50] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_0_50] DEFAULT ((225)),[q_51_100] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_51_100] DEFAULT ((325)),[q_101_150] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_101_150] DEFAULT ((345)),[q_151_200] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_151_200] DEFAULT ((400)),[q_201_250] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_201_250] DEFAULT ((450)),[q_251_300] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_251_300] DEFAULT ((500)),[q_301_400] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_300_400] DEFAULT ((650)),[q_401_600] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_401_600] DEFAULT ((950)),[q_601] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_601] DEFAULT ((1.50)) 1,1854, 225.00, 325.00, 345.00, 400.00, 450.00, 500.00, 650.00, 950.00, 1.502,8140, 225.00, 325.00, 345.00, 400.00, 450.00, 500.00, 650.00, 950.00, 1.50
View 12 Replies
View Related
Oct 24, 2005
I'm having problems with handling a very large amount of user records - about 100.000 - 150.000 records. Instead of selecting all of them at a time, how do I f.ex. select 1000 of them? (f.ex. get nr. 1 - nr 1000, then get nr. 1001 - nr. 2000) ???
View 1 Replies
View Related
Oct 11, 2007
Hello
I'm trying to figure out how to write this query...for example,
A stud attended University of Toronto in 2001 and then attended Seneca College in 2006 ..... I want to select the Education Institute that the stud attended last.
Thank you
View 1 Replies
View Related
Jul 13, 2012
I'd investigate the performance differences between two similar queries (written against AdventureWorks) :
select FirstName, LastName from Person.Contact
where LEFT(LastName,2) = 'Mc'
-- cost .877619
-- after index.0931743
select FirstName, LastName from Person.Contact
where LastName Like 'Mc%'
-- cost .875662 - missing index - add a nonclustered index including first name
-- after index: .0033256I was surprised to see that the Estimated Execution Plan shows a slightly higher cost for LEFT(LastName,2) over LIKE. The Plan for LIKE recommended creating a nonclustered index including FirstName. The LEFT Plan did not recommend any new indexes, yet after I created it the estimated performance increased dramatically, thought not nearly as dramatically as for the LIKE query.
One lessons to take from this is that that you can't always relay on Estimated Execution Plan to tell you where new indexes should be. How accurate are the costs in general? Other lessons?
View 5 Replies
View Related
Aug 22, 2013
I want to merge these queries in one query. When I use UNION ALL parameter sth_tarih sort is wrong.
SELECT TOP 5 sth_stok_kod,sth_evrakno_seri,sth_evrakno_sira,cha_kod ,sth_RECno,sth_tarih
FROM STOK_HAREKETLERI AS SH INNER JOIN CARI_HESAP_HAREKETLERI AS CHH ON SH.sth_evrakno_sira= CHH.cha_evrakno_sira WHERE sth_stok_kod = (
SELECT sth_stok_kod FROM STOK_HAREKETLERI WHERE sth_RECno = (SELECT MAX (sth_RECno) FROM STOK_HAREKETLERI) ) AND sth_evraktip = 3
ORDER BY sth_stok_kod ASC,sth_tarih DESC
[code]....
View 6 Replies
View Related
Jul 20, 2005
COUNTING NUMBER OF SELECTS MADEtable mytable {id, data, hits}users view data from the table:SELECT data FROM mytable WHERE id=1 --for exampleSELECT data FROM mytable WHERE id=20 --for example....How do increment the hits column without replacing the above with the below?update mytable SET hits=hits+1 WHERE id=1;SELECT data FROM mytable WHERE id=1update mytable SET hits=hits+1 WHERE id=1;SELECT data FROM mytable WHERE id=20....I believe triggers can't be used as they only trigger on update/delete events.I'm using sql server 2000 (latest patches) with aspThanksAlex
View 3 Replies
View Related
Jul 20, 2005
I am trying to run 3 dynamic selects from stored proc, really onlythe table name is dynamic.. Anway I'm kinda lost on how I canaccomplish this.. this is what I have but it only returns the firstresult.. that being basicCREATE PROCEDURE email_complexity@TableName VarChar(100)ASDeclare @SQL VarChar(1000)Declare @SQL1 VarChar(1000)Set nocount onSELECT @SQL = 'SELECT Count(complexity) AS basic FROM 'SELECT @SQL = @SQL + @TableNameSELECT @SQL = @SQL + ' WHERE len(complexity) = 5'Exec ( @SQL)SELECT @SQL1 = 'SELECT Count(complexity) AS moderate FROM 'SELECT @SQL1 = @SQL1 + @TableNameSELECT @SQL1 = @SQL1 + ' WHERE len(complexity) = 8'Exec ( @SQL1)ReturnIs there a better way of doing this??tiaDave
View 2 Replies
View Related
Feb 17, 2008
Try the following code in SQL Server 2005:
Code Snippet
CREATE DATABASE test_bug
GO
USE test_bug
GO
CREATE TABLE test1 ( a int )
CREATE TABLE test2 ( b int )
GO
INSERT INTO test1 VALUES ( 1 )
INSERT INTO test2 VALUES ( 2 )
GO
SELECT * FROM test1
SELECT * FROM test2
GO
-- This should error but instead returns all rows from test1
SELECT * FROM test1 WHERE a IN ( SELECT a FROM test2 )
I've tried it onw three different boxes that I have access to.
View 4 Replies
View Related
Dec 4, 2007
Is it possible to block updates from Excel but still allow selects? We're using SQL Server authentication for the time being and a number of users use Excel to query the database. One user has figured out that Excel can also send data to SQL Server. Is there a straightforward way to prevent this?
View 1 Replies
View Related
May 8, 2008
hi, i'm using Access 2007 and i'm trying to join two selects and create two new columns[complete and not complete] where 'x' denotes a hit was made. i will use this later for grouping. here is my code so far. thanks.
SELECT tblOutlookTask.TaskSubject, tblOutlookTask.PercentComplete, tblOutlookTask.ID
FROM tblOutlookTask
WHERE (((tblOutlookTask.PercentComplete)=100))
SELECT tblOutlookTask.TaskSubject, tblOutlookTask.PercentComplete, tblOutlookTask.IDFROM tblOutlookTask
WHERE (((tblOutlookTask.PercentComplete)<>100))
View 3 Replies
View Related
May 23, 2008
Hello,
I want to know how efficient are cross database selects such as
select column from data.dbo.table;
The reason I'm inquiring is because I want to design a database whereas I'm tightly coupling the tables in several databases that are all referencing each other in some way shape or form.
If it is not too efficient or if you know of a way to make these cross database selects more efficient please offer the suggestion.
Thank,
Sharp_At_C
View 2 Replies
View Related
Dec 7, 2006
I have a parameter dropdown as USERID.
I want a functionality that the user can selct multiple userid's to show more than one record as output for comparison purpose.
How can i achieve this multiple select (i.e cntrl+click) within parameter dropdown.
Thanks,
Kiran.
View 7 Replies
View Related
Aug 12, 2014
I have 2 queries where I select all accounts with bill code in 'bmonit' example ('12','39','124','1FA') then I also have a Select where the same account might have additional services, that are not in example ('12','39','124','1FA') and for these accounts I need to just put a 'Y' if stop_date is null.
View 4 Replies
View Related
Jan 17, 2008
Here's my stored procedure:
ALTER PROCEDURE sproc_InsertOrder
(
@CustID tinyint,
@Size varchar(50),
@Crust varchar(50),
@total smallmoney
)
AS
INSERT INTO tblOrders (CustID, SizeID, CrustID, Total)
VALUES(@CustID,
SELECT SizeID FROM tblSizes WHERE @Size = SizeName,
SELECT CrustID FROM tblCrusts WHERE @Crust = Crust,
@total)
SELECT SCOPE_IDENTITY()
RETURN
And my tables:
tblOrders
-------------------------------------------
OrderID | CustID | SizeID | CrustID | Total
-------------------------------------------
tblSizes
--------------------------
SizeID | SizeName | Price
--------------------------
tblCrusts
------------------------
CrustID | Crust | Price
------------------------
My stored procedure is giving me an error, it says "Unable to parse query text", and I'm certain the problem is with the SELECT statements. What am I doing wrong?
View 4 Replies
View Related
Jul 20, 2005
I want to get a column count several times in one query using differentfilters but can't work out how to do it - can anyone point me in the rightdirection?For example, how would combine these two selects into one query that willlist the total and filtered actions:SELECT COUNT(actions) as actioncount, locationFROM mytableGROUP BY locationSELECT COUNT(actions) as actioncount, locationFROM mytableWHERE mycondition IS NULLGROUP BY location
View 6 Replies
View Related
