T-SQL (SS2K8) :: 2 Select Statements To Join By CalendarID
Jun 5, 2014
In t-sql 2008 r2, I have 2 select statements that I would like to join by calendarID . I would like to obtain the results in the same query but I keep getting syntax errors.
The first select is the following:
SELECT Section.number, Section.homeroomSection,
Course.number, Course.name, Course.homeroom, Calendar.calendarID
FROM Section
INNER JOIN Course
ON Course.number = Section.number
INNER JOIN Calendar
ON Course.calendarID = Calendar.calendarID
The second select is the following:
SELECT Person.studentNumber, Enrollment.grade, Calendar.calendarID, Calendar.name, Calendar.endYear
FROM Enrollment
INNER JOIN Person
ON Enrollment.personID = Person.personID
INNER Calendar
ON Enrollment.calendarID = Calendar.calendarID
I have a scenario where the second SELECT statement in my query has to be removed such that the logic needs to be included in the CTE select statement itself.
How this can be done? The columns in the final SELECT statement is having bit complexity.
My query is below:
WITH Local_EOL_Dioxin_Statistic_Import_New AS ( SELECT CASE WHEN ISNUMERIC(vr.stringValue) = 1 THEN vr.stringValue
how can i join these 2 queries to produce 1 result
Query 1: select R.Name, T.Branchid, t.TradingDate,t. TransactionDate, convert(varchar,T.Tillid)+'-'+convert(varchar,t.Saleid) as DocketNumber, t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount as CommissionAmt from TransactionHeader T join SalesRep R on R.SalesRepid = T.SalesRepid where T.SalesRepid is not null
Query 2 : select C.TradingName,C.AccountNo From Sale S Join ClMast c on C.Clientid = s.CustomerAccountID
The result should be R.Name,T.Branchid, t.TradingDate,t. TransactionDate,DocketNumber,t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount, TradingName,Accountno..Field Saleid is present in Transactionheader Table and Sale table
I have two seperate tables that I need to get the data into one select statement. Here is the two separate select statements that I have created.
Select AGEGROUP, [YEAR], SUM(CASES) as Cases FROM tbHosp a LEFT JOIN tbAGEGROUP b on a.AgeGroupID = b.AgeGroupID LEFT JOIN tbYEARHOSP f on f.YEARID = a.YEARID LEFT JOIN tbSEX g on g.SEXID = a.SEXID where a.AgeGroupID in (1,2,3) and a.YEARID in (1,2,3) Group BY AgeGroup, [YEAR] ORDER BY AgeGroup, [YEAR]
Select AGEGROUP, [YEAR], SUM(POPULATION) as [Population] FROM tbPopulation a LEFT JOIN tbAGEGROUP b on a.AgeGroupID = b.AgeGroupID LEFT JOIN tbYEARHOSP f on f.YEARID = a.YEARID LEFT JOIN tbSEX g on g.SEXID = a.SEXID where a.AgeGroupID in (1,2,3) and a.YEARID in (1,2,3) Group BY AgeGroup, [YEAR] ORDER BY AgeGroup, [YEAR]
Is it possible to have an AND within an inner join statment? The below query works, except for the line marked with --*--.
The error I get is the "multipart identifier pregovb.cellname could no be bound", which usually means that SQL server can't find what I'm talking about, but it's puzzling, as I've created the temp table with such a column in it.
Is there a different way i should be structuring my select statement?
SELECT [Survey Return].SurveyReturnID, '1', #temp_pregovb.paidDate, #temp_pregovb.email FROM #temp_pregovb, [Survey Return] INNER JOIN SelectedInvited ON [Survey Return].SelectedID = SelectedInvited.SelectedID --*-- AND [SelectedInvited].cellref=#temp_pregovb.cellname
INNER JOIN [panelist Contact] ON SelectedInvited.PanelistID=[Panelist Contact].PanelistID WHERE [panelist contact].email=#temp_pregovb.email AND SelectedInvited.CellRef IN ( SELECT surveycell FROm [Survey Cells] WHERe SurveyRef='5')
I built a query that brings in 'Discounts' (bolded) to the Order detail by using the bolded syntax below. I started off by running the query without the bolded lines and got exactly what I was looking for but without the ‘Discount’ column. When I tried to add the ‘Discount’ into the query, it duplicated several order lines. Although total ‘Discount’ column ties out to the total amount expected in that column, ‘Total Charges’ are now several times higher than before.
For example, I get 75 records when I run without the bolded syntax and I get several hundred results back when adding back in the bolded syntax when i should still be getting 75 records, just with an additional column ‘PTL Discount’ subtotaled.My question is, how to I introduce a new select or join from another table without duplicating the original data?
I have the following situation. We sell books on our website, and someof the books have more than one author. So I needed to create amany-to-many table, which is the intermidiate table between the authortable and the book table.I can't get the right join statement to work. I've used the code below,submitting an isbn (book id number) to identify the book, but the returnfrom the query simply sends me back all of the authors that are in themany_to_many table(called the book_to_author table here). I'd like it to return only theauthors attached to that isbn, instead of all the authors that are inthat table. What's wrong with the code below? Thanks for your help!SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id ASExpr3 FROM author a INNER JOIN book_to_author m ONa.author_id=m.author_id CROSS JOIN book_detail_final bf WHEREbf.isbn='"&isbn&"' order by m.id descBill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
In SQL Server 2000/2005 (not CE) I can use the following T-SQL statement to delete orphaned rows from a table:
DELETE GroupsMembers FROM GroupsMembers LEFT OUTER JOIN Groups ON GroupsMembers.GroupID = Groups.ID WHERE Groups.ID IS NULL
SQL Server CE does not seem to support combining the JOIN statement with the DELETE statement. Is this correct? If yes, is there any alternative statement that could be used to accomplish the same thing?
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
Hello, how can i merge together several select statements? I have something like this: CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data
WHERE (MPI.MPI_NBR=MPI_PERSON.MPI_NBR) AND (MPI.ADDR_NBR=MPI_CURRENT_ADDR.ADDR_NBR) AND MPI.CREATE_DT>=20070101 ORDER BY MPI.CREATE_DT
SELECT PATIENT.PAT_NBR,PATIENT.PHYS_NBR, PHYSICIAN.FIRST_NM,PHYSICIAN.LAST_NM,PHYSICIAN.DE_NBR, PHYSICIAN.SALUTATION_CD FROM PATIENT, PHYSICIAN WHERE PATIENT.PHYS_NBR=PHYSICIAN.PHYS_NBR
I have a table that list Canadian provinces and American States it looks something like this:
ID | ProvState
Under ID 1-13 lists the Canadian provinces and everything over 13 lists the American states. I want to create 1 query that will list the Canadian provinces first in alphabetical order then the States in alphabetical order.
I have tried using UNION but it's not returning what I want and it does not allow me to use order by for the first statement.
SELECT * FROM SPProvince WHERE ID < 14 ORDER BY ProvState UNION SELECT * FROM SPProvince WHERE ID > 13 ORDER BY ProvState
Arnie and All others. Thanks for your help. The previous case became lenghty and then just mixed up a lot.
To make it easy I have created two temp tables and wrote to test select statement .
You will notice that I tired two select statement but they are giving different set of result however the 2nd Select statement not giving the result as should be looking at the following requirement.
--Selected record must RaType='b' -- PlanID='H321' -- Gender='0' -- --And not to include in select if any one of these meets: -- Hosp='1' in other words it has to be 0 -- ESRD='1' or Rafctor Type in ('g','f') in other words ESRD should be 0 and rafctorType in ('h','i') -- Dod is not null in other words Dod has to be null --
--copy from here
GO Create table #MyTable
( RowID int IDENTITY, RD varchar(10), RAType varchar(5), History varchar(15) )
INSERT INTO #MyTable VALUES ( '1', 'A', '1111' ) INSERT INTO #MyTable VALUES ( '2', 'S','2222' ) INSERT INTO #MyTable VALUES ( '3', 'D', '2345') INSERT INTO #MyTable VALUES ( '4', 'I2','1234' ) INSERT INTO #MyTable VALUES ( '5', 'C','3333' ) INSERT INTO #MyTable VALUES ( '1', 'B','4444' ) INSERT INTO #MyTable VALUES ( '2', 'X','5555' ) INSERT INTO #MyTable VALUES ( '1', 'D' ,'66666') GO
Go Create Table #MYTable2
( RowID int IDentity, RD varchar(10), RaType varchar(5), History varchar(15), PlanID varchar(6), Hosp varchar(2), ESRD varchar(2), RafctorType varchar(3), gender varchar(5), dod varchar (5) NULL
Select #MYtable2.History from #MYTable2 INNER JOIN #mytable on #myTable2.History=#mytable.history Where #MyTable2.RaType='b' And PlanID='H321' And Gender='0' And Hosp<>'1' And ESRD<>'1' AND RafctorType Not in ('g','f') AND Dod is NULL
Select #Mytable2.History from #MyTable2 INNER JOIN #mytable on #mytable2.History=#mytable.history where #mytable2.Ratype='b' AND PlanID='H321' AND Gender='0' AND(Hosp<>'1') or ((ESRD<>'1') or (RafctorType Not in ('g','f')) OR DOD is NULL)
I have listed two tables table 1 has some data. I have to update table 2 [reports] column from table 1 [reports] using self join..I should get as table 2 after updating
Hi Everyone, Can you please let me know what is wrong with the following code? I run the following code using path: http://localhost/jimmy/may_30th_2006/vcalendar_cs/sb_PAYMENTS_page.aspx?LoginID=admin public void searchResultsWithClinic() { SqlConnection myConnection; string conString; conString = ConfigurationManager.AppSettings["calendarString"]; myConnection = new SqlConnection(conString); String cmdStr1, cmdStr2, cmdStr3; cmdStr1 = "SELECT sb_clinic_name FROM sb_client_and_clinic WHERE sb_client_id = '" + Request.Params.Get("LoginID") + "'"; cmdStr2 = "SELECT sb_client_id FROM sb_client_and_clinic WHERE sb_clinic_name = '" + cmdStr1 + "'"; SqlDataAdapter myCommand = new SqlDataAdapter(cmdStr2, myConnection); DataSet DS = new DataSet(); myCommand.Fill(DS, "SearchPaymentResults"); repeaterSearchPaymentResults.DataSource = DS; repeaterSearchPaymentResults.DataBind(); myConnection.Close(); }
Incorrect syntax near 'admin'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'admin'.Source Error:
Line 90: SqlDataAdapter myCommand = new SqlDataAdapter(cmdStr2, myConnection); Line 91: DataSet DS = new DataSet(); Line 92: myCommand.Fill(DS, "SearchPaymentResults"); Line 93: Line 94: repeaterSearchPaymentResults.DataSource = DS;Source File: d:Inetpubwwwrootjimmymay_30th_2006vcalendar_cssb_SearchPaymentResults.ascx.cs Line: 92 --------------------------------------------------------------------------------- Please note that the 'admin' in the error message comes from http://localhost/jimmy/may_30th_2006/vcalendar_cs/sb_PAYMENTS_page.aspx?LoginID=admin thanks,May
I have a SP returning the following result The select statement for this is
Code:
SELECT dbo.TEST1.[OFFICE NAME], COUNT(dbo.TEST1.[ACCOUNT ID]) AS AccountCount FROM dbo.Test2 INNER JOIN dbo.test3 INNER JOIN dbo.Test4 ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code] INNER JOIN dbo.TEST1 ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID] ON dbo.Test2.[Model ID] = dbo.test3.ID INNER JOIN dbo.[Inquiry Details] ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID WHERE (dbo.Test2.InquiryDate BETWEEN CONVERT(DATETIME, @startDate, 102) AND CONVERT(DATETIME, @endDate, 102)) AND dbo.Test1.[Account ID] IN(SELECT [account id] FROM test5 WHERE [Contact ID] = @contactId) GROUP BY dbo.TEST1.[OFFICE NAME] ORDER BY COUNT(dbo.TEST1.[ACCOUNT ID]) DESC name id count case1 226 320 case2 219 288 case3 203 163 case4 223 90 case5 224 73 i have another select stnat which returns like this The select statement is
Code:
Select test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code] Group by test1.[Office Name] order by count(test1.[office name]) DESCname count case6 10 case2 56 case4 66 case1 74 case3 88 case7 100 case5 177 How can i combine this select stament with the SP, so that, i get a fourth column with case1 226 320 74 case2 219 288 56 .......................... ........................... Hope i am not confusing you all Please help me, if someone knows how to combine this? Thanks
I only want to count the titleids that are on loan can I join these two statements or write the stored procedure a different way? I hope this makes sense. select count(libraryrequest.titleid) as [Presently on Loan], libraryrequest.titleid, media.[description]as Media from libraryrequestjoin titles on titles.titleid = libraryrequest.titleidjoin resources on resources.titleid = titles.titleidjoin media on media.mediaid = resources.mediaidgroup by libraryrequest.titleid , media.[description] select distinct requestors.Requestorid, titles.title, resources.quantityowned,requestors.requestorEmail,Requestors.requestdate, fname, lname, phonenum,StreetAddress1, City, State, Zip, libraryrequest.shipdate,libraryrequest.duedate, libraryrequest.returndate from Requestorsjoin Titles on titles.Titleid = requestors.Titleidjoin libraryRequest on LibraryRequest.requestorid = Requestors.requestoridjoin resources on resources.titleid = titles.titleid
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')
I am writing a stored procedure to select some information from two tables and I would also like to Execute a function using the userid information from the processing in the where clause of the Select statement. Is the syntax below possible?? If yes, could you please help me understand exactly what I may be doing wrong here.. If no, can you please help with better syntax. Thanks in advance
SELECT M.UserID, M.FullName, (EXEC calcPoints M.UserID) as 'UserPoints' FROM MissionUsers M LEFT OUTER JOIN MissionUserInfo MU ON M.UserID = MU.UserID WHERE M.EMAIL = @UserEmail
Im trying to perform the following two select statements on the one table. I have been trying innerjoins etc but keep getting errors. The basis of what im trying to do is this.
SELECT column1 FROM table1 WHERE column2 = (select column2 from table1 where column3 = 14)
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!
I am having problems using IF statements. In fact, I don't even know if it is possible to use "IF" in a SELECT statement. Here's my dilemma:
For each employee in my database, I have a numeric rating. I want to assign the rating to a category (green, yellow, red), based on the range the rating falls into.
My data currently looks like this: EmpName Rating employeeX 100 EmployeeY 85 EmployeeZ 25
After the magic of SQL, I want the data to end up looking like this (actually, my boss wants it like this):
EmpName Green Yellow Red employeeX 100 EmployeeY 85 EmployeeZ 25
The numeric rating would be inserted into the appropriate column, based on these ranges: 95 to 100 = green 80 to 94 = yellow < 80 = red
I thought I could use a CASE statement, but I just learned that I can't do comparisons (I'm obviously a beginner). And I can't figure out how to use an If statement (or IIF).
I need help nesting select statements. Here's what I'm trying to do:
select (select e.emp_name_lf as employee, e.emp_id from employee e, install_payroll_detail ipd where e.emp_id = ipd.emplno) e.emp_name_lf as username from employee e, install_payroll_master ipm where e.emp_id = ipm.entered_by
I just want one row with both the employee and username, however I cannot get the syntax. Any help is greatly appreciated.
If any one could help me in Joining these two select statements:
How Can I have Order by and Having Clauses in my final select list ?
*********** SELECTMIN(DISTINCT x.ProductionDate) as ProductionDay,COUNT(DISTINCT v.Number) as Timelost FROMmaster..spt_values AS v INNER JOIN ( SELECTDATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),StartDate,108)) AS StartMinute, DATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),EndDate,108)) AS EndMinute, Convert(varchar(10),StartDate,102) as ProductionDate --,COUNT(AlarmMessage) As Occurances FROM RPMS.dbo.List_PaintShopAlarms WHERE --Convert(varchar(10),StartDate,102) = @vcProductionDay and Convert(varchar(10),StartDate,102) = Convert(varchar(10),EndDate,102) AND (AlarmMessage LIKE '7FM%' OR AlarmMessage LIKE 'CONV%' OR AlarmMessage LIKE 'EMG%' ) ) AS x ON x.StartMinute <= v.Number AND x.EndMinute > v.Number WHEREv.Type = 'p' AND Number < 1440 and (x.ProductionDate BETWEEN Convert(varchar(10),@dtProductionDayStart,102) AND Convert(varchar(10),@dtProductionDayEnd,102)) group by x.ProductionDate order by x.ProductionDate asc *********** and *********** SELECT Convert(varchar(25),[StartDate],102)as ProductionDay ,COUNT(AlarmMessage) As Occurances FROM [RPMS].[dbo].[List_PaintShopAlarms] WHERE ([AlarmMessage] LIKE '7fm%' or [AlarmMessage] LIKE 'Conve%') AND Convert(varchar(25),[StartDate],102) BETWEEN Convert(varchar(25),@dtStartDate,102) AND Convert(varchar(25),@dtEndDate,102) and DATEPART(dw, [ProductionDay]) <> 1
GROUP BY Convert(varchar(25),[StartDate],102) HAVING SUM([Duration]) < 86400 ORDER BY Convert(varchar(25),[StartDate],102) asc *************
different Users are running select statements under their own login. they have readonly permission to the database. My goal is to track all those queries and send notification which tables were opened and which columns were displayed...Looks like I need trigger on select statement for the table...( Unfortunetely it doesn't exists) I collect all those statements in trace file and in the night time I stop trace, move file data to table and then step by step try to massage sql queries using VB.net... Most of those queries are so complicated, have derived tables inside and it is really hard to parse these sql statements... 1.If anybody has an idea how to say: User B opened table tbl_A col_B twice per today...using different methodology rather then go through SQL.(Other words how to create an event on a table which will be fired in a case of request select any column of that table?) 2.If somebody know where we can take good parser which will help with this job? Thanks
I tried this:IF Exists (Select 1 from TESTA..POP10100 where PONumber=@PONumber) and(Select 1 from TESTA..POP30300 where POPType<>2 andPOPRCTNM=@ReceiptNo)BEGINENDIt says Incorrect Syntax error? I tried to cover the whole thing withan extra parantheses. It wont work eitherWhat am i doing wrongThanks, Girish
I have to write a stored procedure.I have a search page having four fields.Giving any of the field should fetch the whole record and display in the gridview. My trouble starts here I have a button field in gridview1 . when i click on the button there should be another gridview which displays refunds of particular customer i.e from another table.There is only one common colum in the two tables. based on that colum value we have to fetch from second table. now my question is : how to capture the colum value of first select statement and give it as input to second select staement.
my code is here :
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[search] (@val1 varchar(225), @val2 varchar(50), @val3 varchar(50), @val4 varchar(50)) AS BEGIN Select*From customer where ((@val1 IS NULL) or (CNo = @val1)) AND ((@val2 IS NULL) or(LastName = @val2)) AND ((@val3 IS NULL) or(FirstName = @val3)) AND ((@val4 IS NULL) or(PhoneNumber = @val4)) Select * From refunds where CNo = @val1 END
here if i fill CNo text box in my search page its giving the value but all the time user may not give CNo.He may search customers based firstname ,lastname etc. so what should i do to capture CNo from first select statement and give it as input to second select statements
I have a SP having three differnet select statements like,
IF @reporttype ='A'
SELECT Column1, column2 FROM table1
IF @reporttype = 'B'
SELECT Column3, column4 FROM table2
IF @reporttype = 'C'
SELECT column5, Column6 FROM table3
Now I need three different reports for all three reporttypes. If i creating first report for @reporttype A, it is executing fine and giving me the Columns which i need. But while creating reprots for reporttype B and C, I m getting columns column1 and column2 . Its not displaying Column3, Column4, Column5, Column6.
I have two select statements on a single table as follows:
SELECT * FROM DOCUMENTS
WHERE FILEDATE LIKE '%1987'
SELECT DOCNUM, COUNT(*) AS TOTALS FROM DOCUMENTS
GROUP BY DOCNUM
HAVING (COUNT(*)>1)
I want to combine the them to provide a list of rows in the table that have duplicate "DocNum" but only within the subset of rows LIKE %1987. When I first looked into how this is done I was sure that you would use a subquery. I tried to combine them using the first one as the outer query and the COUNT as the subquery. However, this gave an error that the subquery was returning a value that did not match what the outer query was expecting. So my questions are: Do I really need a subquery in the first place since this is all within one table? And, if not, how can this be done?
