Multiple Selects On Same Column, Same Table, One Query
Jul 20, 2005
I want to get a column count several times in one query using different
filters but can't work out how to do it - can anyone point me in the right
direction?
For example, how would combine these two selects into one query that will
list the total and filtered actions:
SELECT COUNT(actions) as actioncount, location
FROM mytable
GROUP BY location
SELECT COUNT(actions) as actioncount, location
FROM mytable
WHERE mycondition IS NULL
GROUP BY location
View 6 Replies
ADVERTISEMENT
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
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
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
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
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
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
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 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
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
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
Dec 2, 2005
I can create 1 view of a database then use that views results to query
but i want do this with a stored procedure
pass some data to it, so it selects some data
query the results it created and output this data.
ie
pass 2 values to the procedure
(
@FirstValue int,
@secondValue int output
)
select from a database with the firstpassed value
Select *
From TableName
Where ID = @FirstValue
Then
using the results from the above select
Select *
From theResultsOfAbove
Where ID = @SecondValue
any ideas would be fabo !
View 6 Replies
View Related
Sep 11, 2014
DECLARE @EmployeeID nvarchar(1000)
DECLARE @FiscalYear nvarchar(1000)
SET @EmployeeID = '101,102,103,104,105'
SET @FiscalYear = '2013,2014'
SELECT Data FROM dbo.Split(@EmployeeID, ',')
SELECT Data FROM dbo.Split(@fiscalyear, ',')
_______________________________________
This is part of a bigger project but I am stuck on this part. I get back 2 result sets
Data Data
101 2013
102 2014
103
104
105
I want to insert the results in a new table 2 columns and get the results below.
New Table
ID Fiscal Year
101 2013
101 2014
102 2013
102 2014
103 2013
103 2014
104 2013
104 2014
105 2013
105 2014
View 2 Replies
View Related
Apr 17, 2008
Hi,
I am having two tables Products and Transaction
In products I have ProductID and Description. (10 Records)
In Transaction I have ProductID, Lot, Quantity and ListID . ( 4 Million Records)
When I use the inner join between these tables as below query its taking lot of time to give output.
select ProductID, Desc, Lot, qty from Products inner join Transactions on Products.ProductID = Transactions.ProductID
where ListID = '9090909'
otherwise, if I use the below query its takes very few milli seconds to give output but I am not able to get the description from the product table.
select * from Transactions TR where TR.ListID= '9090909' and TR.ProductID in
(select NDC from Products where Products.ProductID = TR.ProductID)
Any from can help me to get the description too at very few times.
Regards,
View 1 Replies
View Related
Jul 20, 2005
Hi,I have a table like this :TimeIDApplicationLoginState1App1login101App2login211App3login311App1login401App1login511App4login102App1login102App2login202App3login312App1login402App1login512App4login103App1login103App2login213App3login313App1login403App1login513App4login10And I want a result like thisTimeID (state=0) (state=1)1 3 32 4 23 3 3I use this code :set nocount ondeclare @timeid1 intdeclare @timeid2 intdeclare @timeid intdeclare @sessionstate intdeclare @count1 intdeclare @count2 intdeclare @count intdeclare @aux intdeclare txt_cursor cursor forselect timeid, sessionstate , count(login) from metromasterwhere sessionstate = 0group by timeid, sessionstateUNIONselect timeid, sessionstate , count(login) from metromasterwhere sessionstate = 1group by timeid, sessionstateorder by timeidopen txt_cursorselect @aux = 0fetch next from txt_cursor into @timeid, @sessionstate, @countwhile (@@fetch_status = 0)beginif @aux = 0beginselect @timeid1 = @timeidselect @count1 = @countselect @aux =1endelsebeginselect @timeid2 = @timeidselect @count2 = @countselect @aux = 2endif @aux = 2begin--select @timeid1, @count1, @sessionstate, @count2, @timeid2select @timeid1, @count1, @count2select @aux = 0endfetch next from txt_cursor into@timeid, @sessionstate, @countendclose txt_cursordeallocate txt_cursorset nocount offBut it create a lot of blank row and field header.Does anyone know an other methode ??Thanks.
View 1 Replies
View Related
May 23, 2008
I have a fact table with one column storing string values. I need to use this column as a measure, but as different measure. Here's a snapshot of the fact ( with 4 columns)
#1 #2 #3 #4(value)
1 1 2007-12-20 Male
2 1 2007-12-20 French
1 2 2007-12-20 Female
2 2 2007-12-20 English
I have a dimension which is like
id name
1 Gender
2 Language
Is there a way to get measures from column 4 in fact table - so that I can give reports like how gender graph during a time period or language usage during a time period. The design will have more than 2 dimension members, so fact column can hold more data like location, etc...
Can you please suggest a way to handle such kind of fact table?
When I try to create a measure based on that column (and 'no aggregation') - I get error like - "Error 1 Errors in the metadata manager. The data type of the 'value' measure is not valid because the data type of the measure is a string type."
Thanks,
Chandra
View 4 Replies
View Related
Nov 6, 2014
in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').
i tried in my join query
these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')
its taking so much of time how t o solve ?
View 1 Replies
View Related
Aug 7, 2007
Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation:
I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner:
(AISC_Shapes_Table)
AISC_MANUAL_LABEL W
W44x300 300
W42x200 200
(and so on)
WT22x150 150
WT21x100 100
(and so on)
MT12.5x12.4 12.4
MT12x10 10
(etc.)
I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:
Code SnippetSELECT AISC_MANUAL_LABEL, W
FROM AISC_Shape_Table
WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))
It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance.
Regards,
Steve G.
View 4 Replies
View Related
Jun 7, 2012
I have a database that has 370 tables that match %_REF_% naming. All of these tables have a column call ds_name.
I need to get a list of all values of ds_name for each table.
View 3 Replies
View Related
Dec 11, 2004
Hi all,
I want to produce some output for Mainframe application. For that I want to insert values from multiple table as source to a single column (huge in size)of a different table (Destination table). There may be same related records in all of the source tables with the primary key. When I export values from the source tables , each related records should be insterted to the destination table's field (multiple entries for each table). Please advise.
Thanks
View 5 Replies
View Related
Aug 14, 2012
I have a table with a string value, where all values are seperated by a space/blank. I now want to use SQL to split all the values and insert them into a different table, which then later will result in deleting the old table, as soon as I got all values out from it.
Old Table:
Code:
ID, StringValue
New Table:
Code:
ID, Value1, Value2
Do note: Value1 is INT, Value2 is of nvarchar, hence Value2 can contain spaces... I just need to split on the FIRST space, then convert index[0] to int, and store index[1] as it is.
I can split on all spaces and just Select them all and add them like so: SELECT t.val1 + ' ' + t.val2... If I cant find the first space that is... I mean, first 2-10 characters in the string can be integer, but does not have to be.Shall probably do it in code instead of SQL?Now I want to run a query that selects the StringValue from OldTable, splits the string by ' ' (a blank) and then inserts them into New Table.
Code:
SELECT CASE CHARINDEX(' ', OldTable.stringvalue, 1)
WHEN 0 THEN OldTable.stringvalue
ELSE SUBSTRING(OldTable.stringvalue, 1, CHARINDEX(' ', OldTable.stringvalue, 1) - 1)
END
AS FirstWord
FROM OldTable
Found an example using strange things like CHARINDEX..But issue still remains, because the first word is of integer, or it does not have to be...If it isn't, there is not "first value", and the whole string shall be passed into "value2".How to detect if the very first character is of integer type?
Code:
@declare firstDigit int
IF ISNUMERIC(SUBSTRING(@postal,2,1) AS int) = 1
set @firstDigit = CAST(SUBSTRING(@postal,2,1) AS int)
ELSE
set @firstDigit = -1
[code]....
View 2 Replies
View Related
Feb 12, 2015
I am looking a script which allow me add single coilumn to multiple table of my database.
For Example :-
I am having 4 table
1-Emp , 2-Dept , 3-Location , 4-Salary like this I have around 100 of table
Now I want to run below command to add column Rowchecksum in all table where table name start with Archivebbx keywords.
Alter table EMP
Add Rowchecksum varbinary(8000)
View 1 Replies
View Related
Jun 17, 2015
I have a SQL query like this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
As per this query I got the result like this
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 1 1
AED BNT 12 1
AED SCN 1 1
AED SNT 1 3
[Code] ....
But I wish to grt result as
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 13 2
AED SCN 1 1
AED SNT 11 7
AFN BPC 8 6
[Code] ....
I also tried this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans]
from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date)
group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
View 5 Replies
View Related
Oct 23, 2014
I need to name make the name of a column the same as the name of a table from the result of a sql query.. here is the assignment question below..I can't figure out how to get the name of the table to be inputed as the column name..
Write a script that uses dynamic SQL to return a single column that represents the number of rows in the first table in the current database. The script should automatically choose the table that appears first alphabetically, and it should exclude tables named dtproperties and sysdiagrams. [highlight=#ffff11]Name the column CountOfTable, where Table is the chosen table name.[/highlight]
Hint: Use the sys.tables catalog view.
I can figure out the rest. and actually have alredy done it, but i cannot figure out how to do the part that is highlighted above. I looked at lots of things on google to figure it out but no luck..Can some just give me some directlon or an example..
View 14 Replies
View Related
Jan 29, 2014
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
Supersessions Table
----------------------
RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191
[code]...
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
View 9 Replies
View Related
Jul 31, 2015
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
View 5 Replies
View Related
Apr 5, 2008
Hi,
I'm a beginner with query sql and i'm french sorry for my english !
i have 3 table in sql server 2005:
PERSONNE: person_id (clef), nom (varchar), prenom (varchar)
FORMATION : formation_id (clef), person (foreign key), titre (varchar), actif (bit)
EXPERIENCE: experience_id(pk), personne (foreign key), description (varchar), actif(bit)
I would like to display the person_ID, the total row for formation actif = 1 by person and formation actif = 0 by person and experience actif = 1 by person and experience actif = 0 by person
the result must be:
person_ID nbFormationActif NbFormationInactif NbExpActfi
1 2 5 4
2 1 2 5
...
NbExpInactif
0
3
I have doing a simple query who display total experience actif and inactif for all people but now i'm must to do the SAME THING in the SAME QUERY FOR FORMATION and i'm still block!!!
someone can help me ?
SELECT PERSONNE.Person_ID ,COUNT(PERSONNE.Person_ID) AS totExpAct, totExpInactif
FROM
(
SELECT PERSONNE.Person_ID ,COUNT(PERSONNE.Person_ID) AS totExpInactif
FROM PERSONNE INNER JOIN EXPERIENCE
ON PERSONNE.Person_ID = EXPERIENCE.Personne
AND EXPERIENCE.Actif = 0
GROUP BY PERSONNE.Person_ID
)
PERSONNE INNER JOIN EXPERIENCE
ON PERSONNE.Person_ID = EXPERIENCE.Personne
AND EXPERIENCE.Actif = 1
GROUP BY PERSONNE.Person_ID, totExpInactif
...
Thanks for all
Christophe
Im' null in query :-)
View 1 Replies
View Related
Apr 15, 2008
Hi friends,
I have three table named as Eventsmgmt,blogmgmt,forummgmt..
Each table contain the common column named as CreatedDateTime..
I want to get the most recent CreationDateTime from these three table in single query..
Plzz help me its urgent
Thanks
View 20 Replies
View Related
Dec 16, 2004
What is the best way to use a left join in a SQL statement with multiple tables (more than 2)? I have a query that uses 7 tables, where most of the joins are inner joins, except for one, which needs to be a left join. The current SQL statement looks something like this:
SELECT [table1].[field1], [table2].[field1], [table3].[field1], [table4].[field1], [table5].[field1], [table6].[field1], [table7].[field1]
FROM [table1],[table2],[table3],[table4],[table5],[table6],[table7]
WHERE
[table4].[field2]=[table1.field2]{this is an inner join}
[table4].[field2]=[table2.field2]{this is an inner join}
[table4].[field2]=[table3.field2]{this is an inner join}
[table4].[field2]=[table5.field2]{this is an inner join}
[table5].[field3]=[table6.field2]{this is an inner join}
[table5].[field4]=[table7.field2]{this is needs to be a left join}
As it stands now, the last line in the WHERE clause is an INNER JOIN and limits the number of rows in my result. I need to select rows from [table7].[field2] whether or not a matching record exists in [table5].[field4]. The other INNER JOINS in the SQL statement must have matching records. Please advise.
View 2 Replies
View Related
Aug 1, 2014
I have a table named LEDGER
LEDGER has two columns named PATID and CODE
Example data:
PATID CODE
1 Z1110
1 D3330
1 Z0330
2 Z1298
2 Z0987
2 Z0330
2 D1092
I need a query that returns PATID if they have CODE Z0330 but not Z1110. I only want one PATID to return if this condition exists.
View 5 Replies
View Related
Jul 23, 2005
Hey,I am having some confusion about how to formulate this particularquery.I have 2 tables. Table A has 4 columns say a1,a2,a3,a4 with thecolumns a1,a2,a4 forming the primary key. Table B again has 3 columnswith b1,b2,b3,b4 and like before, b1,b2 and b4 form the primary key.All columns are of the same datatype in both tables. Now I want to getrows from table A which are not present in table B. Whats the best wayof doing this?Thanks--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict235166.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=815725
View 6 Replies
View Related
Mar 8, 2012
I would like to run the same query on multiple tables. So say I have a list of tables
@tableList = a|b|c|d
And then I have my query looping through the tables
for (@table in tableList)
{
update from @table
set = ''
}
Is there a simple way to do this in an mssql query, if so how do I get to loop through the query switching the table name?
View 4 Replies
View Related
Jul 23, 2005
Hello All & Thanks in advance for your help!Background:1) tblT_Documents is the primary parent transaction table that has 10fields and about 250,000 rows2) There are 9 child tables with each having 3 fields each, their ownPK; the FK back to the parent table; and the unique data for thattable. There is a one to many relation between the parent and each ofthe 9 child rows. Each child table has between 100,000 and 300,000rows.3) There are indexes on every field of the child tables (though Idon't believe that they are helping in this situation)4) The client needs to be presented a view that has 5 of the mainfields from the parent table, along with any and all correspondingdata from the child tables.5) The client will select this view by doing some pattern-matchingsearch on one of the child records' detail (e.g. field-name LIKE%search-item% - so much for the indexes...)Problem:When I do the simple join of just the parent with one of the children,the search works *fairly* well and returns the five parent fields andthe corresponding matching child field.However, as soon as I add any one of the other child records to simplydisplay it's unique data along with the previously obtained results,the resulting query hangs.Is the overall structure of the tables not conducive to this kind ofquery? Is this a situation where de-normalization will be required toobtain the desired results? Or, more hopefully, am I just an idiotand there is some simpler solution to this problem?!Thanks again for your assistance!- Ed
View 9 Replies
View Related
