I was wondering if it's possible to have a stored procedure that has
two(or more) select statements which you can combine as a single result set.I am not able to use union as my select returns different number of columns.My selects are like this,This example is using only two table but i can have more then 2 tables in situation..
ELECT SUM(col1) AS sumcol1
FROM tbl1
WHERE (ID = @para1')
SELECT SUM(col1) AS sumcol1, SUM(col2) AS sumcol2, SUM(col3)
AS sumcol3, SUM(col4) AS sumcol1
FROM tbl2
WHERE (id = @para1).
thanks
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database. The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records. I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered). I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these). I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes. I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem. So my question is: Is it normal for such a type of read query to take 90 seconds to complete? Is there anything I could do to speed it up. Any other thoughts? Thanks
In my report i have a paremeter which is a dropdown list showing alla available values.Now i want to select multiple values.The parameter is not multi valued.
Multiple select in Parameters Is fixed in Sqlserver reporting services 2005.
Iam using 2005 version,But i can't able to select multiple values by holding Ctrl key.How to achieve this.If we able to select the multiple values,In wgich format the selected value is passed to the Stored Procedure,It is same the way as selecting multiple values from Multi valued Parameter.
Hi there,I want to select records from 3 tables. In SQL Server 2005, I'm using of "For XML" like this:Select *, (Select * From dbo.PageModules Where (PageId = 1) For Xml Auto) As Modules, (Select * From dbo.PageRoles Where (PageId = 1) For Xml Auto) As Roles From dbo.PagesThat works fine in SQL 2005 but not in SQL 2000, Because SQL 2000 does not support nested "FOR XML".Is there any way for selecting records from multiple tables by a query?Thanks in advance
how can take codes below and put them into one store procedure to supplie a gridview. also i will like to define the row name on the left like i did to the column on the top using the 'AS' Code1.... SELECT SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February, SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April, SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June, SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August, SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October, SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December, SUM(CASE WHEN site_descr = 'SITE1' THEN 1 ELSE 0 END) AS AllTotal FROM dbo.V_results WHERE (site_descr = 'SITE1')
Code2.....
SELECT SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February, SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April, SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June, SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August, SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October, SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December, SUM(CASE WHEN site_descr = 'SITE2' THEN 1 ELSE 0 END) AS AllTotal FROM dbo.V_results WHERE (site_descr = 'SITE2')
Hello. I have 2 diffrent tables in the database, is it possible to select both of those back to c# on the same time? I DONT want to combine the tables, just select them into 2 diffrent datatables in one select statement? At the moment, i have something like: function XXOpenDBCon();mCom.Parameters.Clear();mCom.CommandText = "Stuff_GetMovieCategories";myAdap = new SqlDataAdapter();myTable = new DataTable();myAdap.SelectCommand = mCom;myAdap.Fill(myTable);CloseDBCon();return myTable; Can i ask the database to perhaos retrieve Stuff_GetMovieCategories and on the same time retrieve Stuff_MostViewedMovies? So the SP does 2 select statements and returns them, i get in the function above 2 datatables with the information, one datatable with Categories and one table with MostViewedMovies. Is this possible?
Hi guys and gals, I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says: 'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.' My Code is below: //Start of sql CREATE PROCEDURE ADMIN_GetSingleUsers( @userID int) AS DECLARE @userSQL intSET @userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE, Cast(Users.Active as varchar(50)) as Active, Cast(Users.Approved as varchar(50)) as Approved, Users.Unit_ID As usersUnitID, * From TITLE, Users WHERE User_ID = @userID AND TITLE.TITLE_ID = Users.Title_ID ) Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @userSQL //End of sql Can you point to what I am doing wrong? Thanks in advance!
Stored procedure retrieves a single row from a single table... Based on the specific values in 4 different columns, different branch actions are taken using 4 nested IF statements.
The question is, what is more efficient: storing column values in 4 variables and then evaluting each of them, or executing the same query 4 times?
Scenario A:
DECLARE @var1 char(20), @var2 char(20), @var3 char(20), @var4 char(20) SELECT @var1 = col1, @var2 = col2, @var3 = col3, @var4 = col4 FROM theTable WHERE rid = 12345 IF @var1 = 1 ... ELSE IF @var2 = 2 ... ELSE IF @var3 = 3 ... etc. ---------------
Scenario B:
IF (SELECT col1 FROM theTable WHERE rid = 12345) = 1 ... ELSE IF (SELECT col2 FROM theTable WHERE rid = 12345) = 2 ... ELSE IF (SELECT col3 FROM theTable WHERE rid = 12345) = 3 ... etc. --------
Hey everyone, I have a question regarding an SQL query. I'm working on a Web App in .NET at the moment and part of the project is to produce a report of all the information about the clients. Now, there are 4 tables in question: Client, Details, Appointments and RefItem. Now, a lot of the information stored in the first 3 tables are IDs and the names corresponding to those IDs are in the RefItem table.
The RefItem table has the columns ItemID, GroupID, Name, HelpText and Active. For example, a row in the Appoinment table might contain the ID 150 under the AppointmentStatusID. In RefItem, the ID 150 corresponds with "Scheduled". So I have
Code:
SELECT Name FROM RefItem WHERE RefItem.ItemID = Appointment.AppointmentStatusID
This works fine for selecting one name, but my report requires pretty much every name. I was told I'd had to use INNER JOIN or LEFT OUTER JOIN but I can't seem to figure it out. If anyone has any info, please let me know!! Thanks
I have been struggling over the following problem for a few days... i was wondering if anyone could shed some light...!
I have the following query:
SELECT Field1, Field2 FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID WHERE Table2.Field1 = ( SELECT TOP 1 Field1 FROM Table2 WHERE Field3='X' ORDER BY Date1) AND Table2.Field2 = ( SELECT TOP 1 Field2 FROM Table2 WHERE Field3='X' ORDER BY Date1)
Is there a better way to do this. I was thinking of something very similar to the below query (Which doesnt work):
SELECT * FROM Table1 A INNER JOIN Table2 B ON (A.ID=B.ID) INNER JOIN ( SELECT TOP 1 * FROM Table2 WHERE Field3='X' ORDER BY Date1 ) C ON (A.ID=C.ID) WHERE B.Field1 = C.Field1 B.Field2 = C.Field1
Table USERS Contains columns User_id and UserName Table DOMAIN Contains columns Domain_id and DomainName Table USER_DOMAIN Contains columns User_id, Domain_id, count, day, month, year
I am looking to run a report that pulls its information from USER_DOMAIN but instead of displaying User_id, Domain_id, it returns the UserName and DomainName associated.
The query to pull the info i need is very simple, where i am having problems is linking the user_id to the UserName and the Domain_id to the DomainName.
Hi all! I just registred (very nice site) and have problem with getting some data from multiple tables, I would like to get result in one result set and best would be in one sql query.
I have DB for miniMessenger proggy, what i try to do is retrieve list of contacts.
Table containing user account information.
CREATE TABLE `account` ( `id_account` mediumint(8) unsigned NOT NULL auto_increment, `userdata_id` mediumint(8) unsigned NOT NULL default '0', `login` varchar(15) NOT NULL default '', `pwd` varchar(15) NOT NULL default '', `messenger_id` mediumint(8) unsigned NOT NULL default '0', `logged` tinyint(1) NOT NULL default '0', `ost_login` varchar(11) default NULL, PRIMARY KEY (`id_account`), UNIQUE KEY `messenger_UN` (`messenger_id`), UNIQUE KEY `userdata_UN` (`userdata_id`) )
Contact list, first field is contact number (like 4356789 - MESSENGER id) next to this number is its contact number, auth - if contact was authorised, ban selfexplained :) I just take every row with number 4356789 and get contact numbers next to it. CREATE TABLE `contacts` ( `contact_id` mediumint(8) unsigned NOT NULL default '0', `contacts` mediumint(8) unsigned NOT NULL default '0', `auth` tinyint(1) unsigned NOT NULL default '0', `ban` tinyint(1) unsigned NOT NULL default '0', KEY `Contacts ID` (`contact_id`) ) INSERT INTO `contacts` VALUES (4118394, 3333333, 1, 0); INSERT INTO `contacts` VALUES (4118394, 1234567, 0, 1);
Its table for messenger data, ID, status of contact (offline,online,ect), description, chat archiwum, CREATE TABLE `messenger` ( `id_messenger` mediumint(8) unsigned NOT NULL default '0', `status_id` tinyint(3) unsigned NOT NULL default '0', `description` varchar(255) NOT NULL default '', `archiwum` mediumtext NOT NULL, PRIMARY KEY (`id_messenger`) ) INSERT INTO `messenger` VALUES (1234567, 0, '', ''); INSERT INTO `messenger` VALUES (3333333, 1, '', ''); INSERT INTO `messenger` VALUES (4118394, 2, '', '');
Status is enumeration of status states(off,on,brb ect). CREATE TABLE `status` ( `id_status` tinyint(3) unsigned NOT NULL default '0', `stat` varchar(15) default NULL, PRIMARY KEY (`id_status`) ) INSERT INTO `status` VALUES (0, 'offline'); INSERT INTO `status` VALUES (1, 'Online'); INSERT INTO `status` VALUES (2, 'brb');
What i want to get is contact list + additional info of specific user by its messenger id. Like: id_messenger,contacts,auth,ban,stat
which is userID, contact ID, authorisation, ban, status
My query looks like this: SELECT id_messenger,contacts,auth,ban,status_id FROM account,messenger,contacts WHERE account.login = 'User' AND messenger.id_messenger = account.messenger_id AND contacts.contact_id = messenger.id_messenger
And it shows in stat only status of user of which i retrieve contact list. Please help me, im tired of working on this, im sure it is trivial :(
Can someone explain to me why this would be considered "bad"? One thing that pops in my mind is that I really don't need all the columns from all these tables, only specific columns. Would this cause a performance issue when used in a stored proc for a transactional app?
SELECT * FROM CASE_XREF CX, CASE_RENEWAL_XREF CRX, RENEWAL_BATCH RB, PROPOSAL P WHERERB.MKT_SEG = @MKT_SEG AND RB.CORP_ENT_CD = 'oh' AND RB.RENEWAL_DT = '01/01/2008' AND CRX.TRIGGER_TYPE_CD = 'P' AND RB.BATCH_ID = CRX.BATCH_ID AND CRX.CASE_ID = CX.CASE_ID AND CRX.REN_PROSPECT_ID = P.PROSPECT_ID AND CRX.REN_PROP_NUM = P.PROP_NUM AND P.PROP_STATUS <> 'C' AND CX.ACCT_NBR = 123152
I'm looking to see if there are any best known methods or better ways of handling multiple multi select parameters in reports coming from reporting services.
It's not a big deal to account for this in T-SQL when there are only a few multi-select boxes but each new one that I add creates a whole bunch more work for me. Each of these multiselect list boxes are optional filters for the report. The way that I"m handling it today is as follows:
I'm turning the multi-select lists into comma seperated variables and then using Itzik Ben-Gan's split UDF to turn them into tables that I can join to my query. I then take the base query w/out the joins and pass that into a temp table. Then I start a bunch of IF THEN program flow to either JOIN or not JOIN the results of the split UDF.
Here is my example scenarios: Assumptions:
I have a report that has 5 input drop down lists in reporting services.
I have a user stored procedure that takes the multi-select comma seperated list and turns it into a table variable.
I cannot use Dynamic SQL because of information security policy.
The multi-select lists are optional parameters.
The Code below is accurate.... I didn't test it and just wrote it quickly to show the complexity... hopefully you get the idea.
In scenario 1: One of the drop down lists are multi-select enabled. My SQL Sproc Would look like this:
IF @Origin_Warehouse_IDs = '' BEGIN SELECT * FROM #MyCoreTemp END ELSE BEGIN SELECT * FROM #MyCoreTemp MCT JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr) END
In scenario 2: Two of the drop down lists are multi-select enabled. My SQL Sproc Would look like this:
IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs = '' BEGIN SELECT * FROM #MyCoreTemp END ELSE IF @Origin_Warehouse_IDs != '' AND @Ship_To_Geo_IDs = '' BEGIN SELECT * FROM #MyCoreTemp MCT JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr) END ELSE BEGIN SELECT * FROM #MyCoreTemp MCT JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr) JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr) END
In scenario 3: Three of the drop down lists are multi-select enabled. My SQL Sproc Would look like this:
IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs = '' AND @Customer_Type_IDs = '' BEGIN SELECT * FROM #MyCoreTemp END ELSE IF @Origin_Warehouse_IDs != '' AND @Ship_To_Geo_IDs = '' AND @Customer_Type_IDs = '' BEGIN SELECT * FROM #MyCoreTemp MCT JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr) END ELSE IF @Origin_Warehouse_IDs != '' AND @Ship_To_Geo_IDs != '' AND @Customer_Type_IDs = '' BEGIN SELECT * FROM #MyCoreTemp MCT JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr) JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr) END ELSE IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs != '' AND @Customer_Type_IDs != '' BEGIN SELECT * FROM #MyCoreTemp MCT JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr) JOIN udfSplit(@Customer_Type_IDs,DEFALUT) CT ON (MCT.Customer_Type_ID = CT.nstr) END ELSE IF @Origin_Warehouse_IDs != '' AND @Ship_To_Geo_IDs = '' AND @Customer_Type_IDs != '' BEGIN SELECT * FROM #MyCoreTemp MCT JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr) JOIN udfSplit(@Customer_Type_IDs,DEFALUT) CT ON (MCT.Customer_Type_ID = CT.nstr) END ELSE IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs = '' AND @Customer_Type_IDs != '' BEGIN SELECT * FROM #MyCoreTemp MCT JOIN udfSplit(@Customer_Type_IDs,DEFALUT) CT ON (MCT.Customer_Type_ID = CT.nstr) END ELSE IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs != '' AND @Customer_Type_IDs = '' BEGIN SELECT * FROM #MyCoreTemp MCT JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr) END ELSE BEGIN SELECT * FROM #MyCoreTemp MCT JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr) JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr) JOIN udfSplit(@Customer_Type_IDs,DEFALUT) CT ON (MCT.Customer_Type_ID = CT.nstr) END
As you can see by scenario 3 where there are only 3 multi-select lists it gets really hairy. If I go to a forth it will be even worse. Does anyone know of another better way to do this?
So my aim is to display ALL DATA for each of these tables.
Tried the below but doesn't return any rows...
Code SnippetSELECT gd.Quantity, c.Comments, gc.GPositionID, cc.CPPositionID, cd.PositionDate FROM ReconComments AS c INNER JOIN RGCrossRef AS gc INNER JOIN RGData AS gd ON gc.GPositionID = gd.PositionID ON c.GPositionID = gc.GPositionID INNER JOIN RCPData AS cd INNER JOIN RCPCrossRef AS cc ON cd.UniquePositionID = cc.CPPositionID ON c.CPPositionID = cc.CPPositionID WHERE gc.ForcedMatch = 'yes' AND cc.ForcedMatch = 'yes'
Select c.Plan, c.ClaimNumber from tbFLags c inner join tbMembers m On c.Claim = m.HistoryNum where c.Plan = 'J318' and c.Paymon = c.Rmon and c.Rmon = '2008-03-01'
Now I want to add these into this statement, what should be done.
Members meeting any of the 3 sets of criteria should not be selected
1) tbFlags.Hosp='1'
2) tbFlags.RD='1' OR tbCMSFlags.RAType in ('D', 'I2')
I have database with three tables Accounts, Results, and ClosedOrders. All are connected through AccountID PK/FK.
I got a wonderful select statement that gives me the latest Results for each Account.
WITH cte AS ( SELECT Accounts.AccountID, Accounts.AccountName, Results.ResultTime AS LastUpdated,
[Code] ....
I've been struggling to extend this with two more columns from the ClosedOrders table. How to add columns to the this view? Basically what I need is this:
SELECT SUM([Lots]) AS Longs FROM [DEV].[dbo].[ClosedOrders] WHERE OrderTypeID = 0;
SELECT SUM([Lots]) AS Shorts FROM [DEV].[dbo].[ClosedOrders] WHERE OrderTypeID = 1;
But it has to "join" the CTE somehow so that I get the correct answer for each Account row.
If I have a table called "content_hits_tbl" and want to pull information, can't i write something like this:
SELECT COUNT(visitor_id) AS HITS, COUNT(DISTINCT visitor_id) AS VISITORS, COUNT(DISTINCT visitor_id) WHERE visit_type = 0 AS NEW, COUNT(DISTINCT visitor_id) WHERE visit_type = 1 AS RETURNING FROM content_hits_tbl
Can't you have multiple WHERE clauses in the SELECT statement?
Any suggestions would be great. I have been wrestling with it and SQL queries arent my strong area ...
I was wondering if it's possible to have a stored procedure that has two select statements which you can combine as a single result set. For instance:select name, age, titlefrom tableaselect name, age, titlefrom tablebCould you combine these queries into a single result set?
Hello, Is there a way to assign multiple variables to one select statement as in the following example? DECLARE @FirstName VARCHAR(100) DECLARE @MiddleName VARCHAR(100) DECLARE @LastName VARCHAR(100) @FirstName, @MiddleName, @LastName = SELECT FirstName, MiddleName, LastName FROM USERS WHERE username='UniqueUserName'
I don't like having to use one select statement for each variable I need to pull from a query. This is in reference to a stored procedure.
hi,i have three database servers with heterogeneous databases, and i have a sql server 2005 that should has a table that will be filled with records from the three servers every time period, so what would be the best technique to create this table with the scripts ????i used to use linked server + sql server agent jobs but usually for one linked server only, but this time i am afraid of the performance as there will be three linked servers, so i need ur suggestions.
I know I should know the answer to this, but I just can't quite get the syntax down
Code: Select case when zipCode = '10185' Then 'Deliver' Else when zipCode = '2309' And paid = 'Yes' Then 'Deliver' Else When zipCode = '1291' And paid = 'Yes' Then 'Deliver' Else When zipCode = '88221' And paid = 'No' Then 'Hold' Else when zipCode = '34123' Then 'Deliver' End From postalDeliveryDatabase
I have a 'charges' table that records charges for an invoice. There are several different types of charges, each with its own unique set of additional data fields that need to be recorded.
I maintain separate tables for each charge type and these tables participate in an "ISA" relationship with the main charges table.
Here is a simplified version of my schema. Hourly charges are one type of charge:
charges table ============= id int (autoincremented primary key) date datetime amount money
hourly_charges table ==================== charge_id int (primary key, also a foreign key to charges table) start_time datetime end_time datetime
I need to write a query that will duplicate all charges meeting a certain criteria by inserting new records into both the charges table and the hourly_charges table.
Here is some non-working pseudo-code that hopefully will get across what I would like to accomplish:
INSERT INTO charges JOIN hourly_charges ( charges.date, charges.amount, hourly_charges.charge_id, hourly_charges.start_time, hourly_charges.end_time ) SELECT date, amount, SCOPE_IDENTITY(), start_time, end_time FROM charges JOIN hourly_charges ON charges.id = hourly_charges.charge_id WHERE some condition is true
Now I realize this code is invalid and I'll have to go about this an entirely different way but I'm wondering if someone can tell me what the proper way is.
There is always a colour value. But there is not always a value in the MD columns. For example one Product may have values in MD01 MD02 MD03 MD04 MD05 whilst another has values in all MD columns.
My problem is thatI am trying to create a results list based upon selecting distinct values from the colour and md columns
In otherwords i can't have more than only one instance of a word appearing in the recordset list
I'm really struggling with this because there are only 6 colours so if i set distinct purely on 6 colours i only get back 6 rows.
When I try to set disctinct across all the MD columns it seems to ignore it and lists results for example in the table
ProductID Colour MD01 MD02 MD03 MD04 MD05 MD06 MD07 MD08 MD09 MD010 1 red car bike 2 blue bike car train
my select lists results as red car bike blue bike car train
and it is as if it only carries out the distinct command across the row not across all columns for all rows if you see what i mean?
I need to be able to list all data from all rows that have values in the MD columns and colour column but not list the values more than once and not list "empty" (NULL) columns. Does this make sense?
This is the select statement i wrote.
Select DISTINCT md00, md01, md02, md03, md04, md05, md06, md07, md08, md09, md10, colour FROM TEMPLATES WHERE md00 IS NOT NULL or md01 IS NOT NULL or md02 IS NOT NULL or md03 IS NOT NULL or md04 IS NOT NULL or md05 IS NOT NULL or md06 IS NOT NULL or md07 IS NOT NULL or md08 IS NOT NULL or md09 IS NOT NULL or md10 IS NOT NULL
But it returns empty columns and it returns every instance of the same word so in other words the distinct command doesn't seem to be working at all?
I don't know if this is because of my asp code I am trying to list results with the rescordset?
<% While ((Repeat1__numRows <> 0) AND (NOT template_rs.EOF)) %>
I have one more problem. How can I also in addition to being able to list all distinct results list only results for a specific letter
for example
Select DISTINCT md00, md01, md02, md03, md04, md05, md06, md07, md08, md09, md10, colour FROM TEMPLATES WHERE md00 IS NOT NULL or md01 IS NOT NULL or md02 IS NOT NULL or md03 IS NOT NULL or md04 IS NOT NULL or md05 IS NOT NULL or md06 IS NOT NULL or md07 IS NOT NULL or md08 IS NOT NULL or md09 IS NOT NULL or md10 IS NOT NULL WHERE FIRST LETTER ='A'?
I am so far out of my depth here guys I am hoping that someone who has real knowledge of SQL can help me with this statement. I've been pulling my hair out for days now and getting just more and more frustrated listing the same results :(
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
I'm trying to get the number of records from one table where a column matches another column in a 2nd table. I then need the total values of another column that it has selected.
SELECT HOLIDAY_REF].holiday_id, COUNT([BOOKING].booking_status_id) AS record_count COUNT([BOOKING].total_value) AS total_value FROM [HOLIDAY_REF] LEFT OUTER JOIN [BOOKING] ON [HOLIDAY_REF].holiday_id = [BOOKING].booking_status_id WHERE [BOOKING].holiday_id=[HOLIDAY_REF].holiday_id && booking_status_id = '330'
UPDATE g SET g.GroupID = gp.GroupID, g.Contact1 = members.FirstName, g.BusPhone1 = members.BusPhone, g.HomePhone1 = members.HomePhone, g.Internet1 = members.Email FROM statelst AS g INNER JOIN grpcon AS gp ON g.GroupID = gp.GroupID INNER JOIN members ON gp.MemberID = members.MemberID CROSS JOIN
I have my table statelst that I want to update certain columns from the values returned by a select on the grpcon table joined to the members table.I am getting an error "Incorrect syntex near 'JOIN'.
I am trying to apply the logic from the following resource: URL....but cannot get it to work with my logic for some reason.For example, the following query:
;WITH CTE1 AS (SELECT CONVERT(VARCHAR, GETDATE(), 120) AS Col1), CTE2 AS (SELECT CONVERT(VARCHAR, GETDATE(), 111) AS Col2) SELECT CTE1.Col1,CTE2.Col2 FROM CTE1 CROSS JOIN CTE2 GO
Produces the following output:
Col1 | Col2 2014-05-08 10:55:54 | 2014/05/08
But, as soon as I try to do something else like:
;WITH CTE1 AS (SELECT COUNT(login) FROM userinfo AS Col1), CTE2 AS (SELECT COUNT(login) FROM userinfo AS Col2) SELECT CTE1.Col1,CTE2.Col2 FROM CTE1 CROSS JOIN CTE2 GO
I receive the following errors:
Msg 8155, Level 16, State 2, Line 1 No column name was specified for column 1 of 'CTE1'. Msg 8155, Level 16, State 2, Line 2 No column name was specified for column 1 of 'CTE2'.
Are there limitations when trying to use multiple CTE in a single query?
I would like to get records from a table and present a result set based upon the search fields
the search fields could be any of the following: PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3,
they could enter any combination of these however if they dont enter any of the above then the search should not retrieve any thing. the table colunms are listed below and asample data set is also shown below.
Currently the only way i think it can be done is by writing multiple queries with different queries to be executed based upon the search field that have been filled? can it be done in a stored prcedure? and can it be done using non-dynamic sql?
Hi! I want to get some fields from more than one table. How can I use select command to do this? Please help me! The results should be in one table only! Thanks in advance!
Hi, I am new to to the usage of sql server.I have data in the below mentioned format in table called stock_transaction.
stocknumber transtype transsubtype balance
s01 in cust 100 s01 out cust 200 s01 in deal 300 s01 out cust 100
s02 in deal 200 s02 out cust 300 s02 in cust 100 s02 out cust 200 s02 in cust 300
I want to generate a report which has the sum of balances of each stock number that belong to a particular trans group like (in,cust) (out,cust),(in,deal) as below where (in,cust) (out,cust),(in,deal) are temporary aliases only for displaying as shown below
I am using case statements, I am able to retrieve the data when selecting of single trans group like(in,cust) or (out,cust) by giving its condition but was unable to select all the details of a particular stock number as a single record .
