Getting Multiple Counts With Grouping And Left Joins
Mar 3, 2008
Hi, I am trying to write a query that gets the percentage of students in specific racial groups in specific schools. Some ethnicity values of students are null so I have to use left joins. My query is below, when I run it I get the error "join expression not supported", I've tried a couple different ways of doing it but I always get that error or "syntax error in from clause". Can anybody help me with formatting multiple and nested left joins in general?
Thanks in advance.
drop table percentMinorities;
create view percentMinorities as
select s1.schoolid,
round(count(s2.studentid)/count(s1.studentid),2) as percentWhite,
round(count(s3.studentid)/count(s1.studentid),2) as percentBlack,
round(count(s4.studentid)/count(s1.studentid),2) as percentHispanic,
round(count(s5.studentid)/count(s1.studentid),2) as percentAsian
from
students as s1 left join
(students as s2 left join
(students as s3 left join
(students as s4 left join students as s5
on s4.studentid is not null and s5.ethnicity = 'A')
on s3.studentid is not null and s4.ethnicity = 'H')
on s2.studentid is not null and s3.ethnicity = 'N')
on s1.studentid is not null and s1.ethnicity = 'O'
group by s1.schoolid
View 10 Replies
ADVERTISEMENT
Sep 1, 2005
Hi All,
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
View 2 Replies
View Related
Apr 27, 2004
Could somebody tell me what is the secret of being able to write a SELECT statement having mulitple LEFT or RIGHT joins, I seem to get in trouble as soon as I add the second LEFT join, as I am obviously doing it wrong.
These are my tables, would somebody mind having a go,or explaining what do i need to be aware of in a case like this
emailDetails table ( emailID_PK,emailName,emailText,emailSubject,emailN otificationTypeID)
emailRecipients table (emailID_PK, RecipientID, SentToEmail)
luEmailNotificationTypes look up table (emailNOtificationTypeID_PK, emailNotificationTypeName)
Thanks
View 2 Replies
View Related
May 31, 2006
I need to do multiple left outer join to return search profiles that could contain NULL in them that could also be foreign keys. I bolded the three IDs that could be NULL or have a foreign key for a value. An example with my code would be great I've tried decyphering the many employee and company examples on the web but I haven't figured it out yet. Right now I only get profiles that have foreign key values and it misses the rest in the search. So NULL MakeID or ModelID no result on that item my SQL statement below. Using SQL Server 2005. Pretty new this to SQL and databases but so far this has been the only trying part.
Thanks
String dbsql = "SELECT a.EquipmentID " +
" , a.SerialNo " +
" , b.Category " +
" , c.Subcategory " +
" , d.Make " +
" , e.Model " +
" , f.Status " +
" FROM tblEquipInfo a " +
" , tblEquipCat b " +
" , tblEquipSubcat c " +
" , tblEquipMake d " +
" , tblEquipModel e " +
" , tblStatus f " +
" WHERE b.Category = '" + val + "' " +
" AND a.CategoryID = b.CategoryID " +
" AND a.SubcategoryID = c.SubcategoryID " +
" AND a.MakeID = d.MakeID " +
" AND a.ModelID = e.ModelID " +
" AND a.StatusID = f.StatusID";
View 3 Replies
View Related
Sep 26, 2012
I'm using CTEs to try and get the totals of two different criteria queries that I want to group by Month depending on the associated Inquiry Date. Here is what I have right now;
Code:
;With CTE(total, InitDate) as
(
SELECT count(Inquiry.ID), Inquiry.Date from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= '9/26/2012' AND Inquiry.Date IS NOT NULL)
AND (Inquirer.Program = 'Res. Referral Coord.')AND TransferInquiryID_fk IS NULL
Group By Inquiry.Date
[code]...
I get 170 for InitCount, but for TransCount I only get 19, not 26. I assume it is with my left outer join statement grouping but I am not sure how I would change this to get the proper counts. All I want to do is group the values together depending on the month they were done in.
View 3 Replies
View Related
Apr 25, 1999
To the wise,
This may be a very simple problem but it's been racking may brains for a while and I just can't seem to think it through clearly.
I'm trying to return a query which uses a left join and where. I'm hoping to get a result set which shows - let us says all the departments in a company. I would like to see all the department but only the names of department heads that earn 20.000+.
In MS Access I used a subquery. My subquery returned only departments with department heads that earned 20.000+ - I then left joined the departments table to that query - no problem.
With MSSQL I've tried IN, ANY, ALL but my result sets only returns the departments that earn 20.000+ and the employees for those particular departments.
I'm thinking there must be some way of doing this without having to use a union clause.
Thanks for taking the time to read this message through.
Sincerely,
Arthur Lambiris
View 6 Replies
View Related
Nov 2, 2006
My understanding of relevant topics as well as SQL Books Online definition of left outer joins is that each record in the left table will be retrieved and where no associated right record exists then null values will be displayed for records in the right hand table but I've obviously misunderstood and would be grateful if someone could show me how to produce the required effect.
My scenario is pretty simple: 2 tables DiningTables and Reservations with columns as follows:
DiningTables: TBL_ID and TBL_Location - TBL_ID is the primary key
Reservationss::RES_TBL_ID and RES_Diner_Name - RES_TBL_ID is the primary key
There are 8 records in DiningTables and 4 records in Reservations and the objective is obtain the following output:
TBL_ID RES_Diner_Name
1 Jones
2 Smith
3 Bloggs
4 Mack
5 null
6 null
7 null
8 null
The SQL query I used is
SELECT Reservations.RES_Diner_Surname, DiningTables.TBL_ID
FROM DiningTables LEFT OUTER JOIN
Reservations ON DiningTables.TBL_ID = Reservations.RES_TBL_ID
That query generates 11 rows as follows:
TBL_ID RES_Diner_Name
1 Jones
1 Smith
1 Bloggs
1 Mack
2 null
3 null
4 null
5 null
6 null
7 null
8 null
I'm clealry missing something incredibly obvious and I kinda feel like the village idiot and would be extremely grateful for a clue!!
View 5 Replies
View Related
Oct 25, 2007
Hi All,
This may be a stupid question. But I just want to make sure i'm going in right direction.
I wrote the following query. My purpose is to retrieve all the members who submitted loan applications.
I just want to make sure my query is right? Can anyone veryfy this query?
Do I have to use loanApplication table first instead of members. Also Do I need to use right outer joins instead of left outer joins?
Code Block
SELECT Member.CUMemberId, LoanApplication.SubmittedOn, Member.LastName, Member.FirstName, Member.MiddleName, LoanApplication.Amount,
LoanApplication.Decision, LoanApplication.Term, Rate.InterestRate, LoanApplication.Status, Member.CuStatus
FROM Member INNER JOIN
MemberLogon ON Member.Id = MemberLogon.MemberFK INNER JOIN
LoanApplication ON Member.LastLoanApplicationFK = LoanApplication.Id AND Member.Id = LoanApplication.MemberFK LEFT OUTER JOIN
Account ON Member.Id = Account.MemberFK AND LoanApplication.LoanFK = Account.Id LEFT OUTER JOIN
Rate ON LoanApplication.RateFK = Rate.Id
WHERE (LoanApplication.Status = 'Submitted')
Thanks
View 3 Replies
View Related
Sep 21, 2007
Hello,
I am working on a query that has 11 left join statements, some are hitting against reference data that has a small amount of records, whereas others not so small. From a performance standpoint, should I look at rewriting this query, and how would I do so? What is an alternative to left joins; any examples anyone has?
Thanks.
View 2 Replies
View Related
Feb 17, 2008
Hi,
First of all, is this an appropriate place to get answers related to SQL CE? If not, do you have any recomended forums elsewhere?
I'm trying to get a list of various related tables using ResultSets on SQLCE 3. The query is something like this:
SELECT A.*
, F.Descricao AS FamiliasDescricao
, M.Descricao AS MarcasDescricao
, I.Descricao AS IVADescricao
FROM Artigos AS A
LEFT OUTER JOIN Familias AS F ON A.FamiliasUID = F.UID
LEFT OUTER JOIN Marcas AS M ON A.MarcasUID = M.UID
LEFT OUTER JOIN IVA AS I ON A.IVAUID = I.UID
INNER JOIN ArtigosTipos AS AT ON A.ArtigosTiposUID = AT.UID;
The column ArtigosTiposUID cannot be NULL, so an INNER JOIN is used, but the other UID columns can have a NULL value, and I need all the rows on Artigos to show up even if these other UIDs are NULL. The query runs fine like this in VS2005, returning NULL values for the columns if there are no rows on the other tables, both on the SQL Server 2005 database and the .sdf database used on the Windows Mobile device. But on Windows Mobile SQL CE gives me an "Unspecified error [7]", Native error 25607, an the stack trace ends with:
em System.Data.SqlServerCe.SqlCeCommand.ProcessResult s()
em System.Data.SqlServerCe.SqlCeCommand.CompileQueryP lan()
em System.Data.SqlServerCe.SqlCeCommand.ExecuteComman d()
em System.Data.SqlServerCe.SqlCeCommand.ExecuteResult Set()
If I replace all the LEFT OUTER JOINs with INNER JOINs only the rows where all UIDs have a value show up, but as I said, I want all rows on tabela Artigos. Even if I remove all JOINs except the last one and replace it with a LEFT OUTER JOIN I get the same erro, all rows having the column ArtigosTiposUID defined... it seems as if the simple presence of LEFT OUTER JOIN makes SQL CE return an error.
Is there a way to run the queries on VS using the SQL CE engine so that one can check whether the query will run successfuly on Windows Mobile?
Any other sugestions?
Thank you,
Hugo Lopes
View 6 Replies
View Related
Nov 13, 2003
Other than being much less readable, is there a downside to combining left and right outer joins in the same SELECT? I'm reviewing some generally poor code done by a contractor and it's peppered with queries with both left and right joins. I've always thought it was just a semantic difference, but I was just wondering if, other than readability, there were any performance issues.
Thanks,
Pete
View 1 Replies
View Related
Jan 12, 2005
Hi,
I am working a DTS package and I need to Join to completely differnet tables in such a way that I need to do an inline view and an Outer Join. In this current form, it drops all columns for a day if one of the inline views returns null.
SELECT 01 as WHSE_ID
, A.On_Time
, B.Early
, C.Late
, (D.AVG_Duration / (A.On_Time + B.Early + C.Late))AS AVG_Duration
, E.DelDate
, F.*
FROM
(SELECT COUNT(SDD_Status) AS On_Time
, SDD_Date as On_Time_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%' AND SDD_Status = 'On Time'
AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) a,
(SELECT COUNT(SDD_Status) AS Early
,SDD_DATE As Early_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%' AND SDD_Status = 'Early'
And SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) b,
(SELECT COUNT(SDD_Status) AS Late
, SDD_Date As Late_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%' AND SDD_Status = 'Late'
AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) c,
(SELECT SUM(CAST(SDD_Stay AS NUMERIC)) AS AVG_Duration
, SDD_Date As Stay_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%'
AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) d,
(SELECT DISTINCT(SDD_Date) AS DelDate
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date)e,
(SELECT *
FROM WAREHOUSE_METRICS
WHERE MTRC_DTE < '12/19/2004' AND MTRC_DTE > '12/10/2004'
AND WHSE_ID = 2
GROUP BY MTRC_DTE
, MTRC_ID
, WHSE_ID
, INVN_LVL_CS_CNT
, INVN_LVL_DLLR
, INVN_LVL_PLLT_CNT
, RCV_CS_CNT
, SHP_CS_CNT
, SRVC_LVL_PCT
, SCRTCH_CNT
, DROP_CNT
, RPLNSH_CNT
, DMG_CNT
, RET_CNT
, PICK_CYC_CNT
, PICK_ERR_CNT
, RSV_CHK_CNT
, ERR_CNT
, DLY_CS_VRNC
, DLY_DLLR_VRNC
, CMB_THRUPUT
, DELAY_MINS_CNT
, DELAY_PCT
, UNLOAD_AVG
, LABOR_AVG
, SELECT_HR_CNT
, CRT_USERID
, CRT_DTE_TME
, UPD_USERID
, UPD_DTE_TME) f
WHERE a.On_Time_Date = E.DelDate
AND B.Early_Date = E.DelDate
AND C.Late_Date = E.DelDate
AND D.Stay_Date = E.DelDate
AND F.MTRC_DTE = E.DelDate
GROUP BY E.DelDate
, A.On_Time
, B.Early
, C.Late
, AVG_Duration
, A.On_Time_Date
, B.Early_Date
, C.Late_Date
, D.Stay_Date
, F.WHSE_ID
, F.INVN_LVL_CS_CNT
, F.INVN_LVL_DLLR
, F.INVN_LVL_PLLT_CNT
, F.RCV_CS_CNT
, F.SHP_CS_CNT
, F.SRVC_LVL_PCT
, F.SCRTCH_CNT
, F.DROP_CNT
, F.RPLNSH_CNT
, F.DMG_CNT
, F.RET_CNT
, F.PICK_CYC_CNT
, F.PICK_ERR_CNT
, F.RSV_CHK_CNT
, F.ERR_CNT
, F.DLY_CS_VRNC
, F.DLY_DLLR_VRNC
, F.CMB_THRUPUT
, F.DELAY_MINS_CNT
, F.DELAY_PCT
, F.UNLOAD_AVG
, F.LABOR_AVG
, F.SELECT_HR_CNT
, F.CRT_USERID
, F.CRT_DTE_TME
, F.UPD_USERID
, F.UPD_DTE_TME
, F.MTRC_ID
, F.MTRC_DTE
Order By E.DelDate
Please excuse the length of the code but one of the tables has a lot of columns.
Can anyone tell me if it's possble to do a join on an inline view?
Any help would be greatly appreciated.
Thanks in advance,
John
View 5 Replies
View Related
Jan 30, 2006
Hello,
I am trying to understand the concept of left joins. I have the following query and am not sure about the left joins.
I am familiar with joins but the left join below is a little confusing.Below it seems like a third table is involved. Is this because there is no column to map to in the from table? Also, since tables sl and sc are mapped based on the SecurityID column and sl and ex do not have any common columns, table sc is mapped to ex using the left join? Which table's data will be returned based on the left join?
I checked the column type for the Exchange column(ex.LSECode) and it appears varchar(3).
SELECT SecurityID = sl.SecurityID
, Security = RTRIM(sec.Name) + ' - ' + RTRIM(sec.Description)
, Ticker = ISNULL(ids.RIC, ids.Ticker)
, Sedol = ids.Sedol
, ISIN = ids.ISIN
, Exchange = ex.LSECode
, Country = cty.iso_code
FROM #SecList sl
JOIN SecurityClassification sc
ON sl.SecurityID = sc.securityId
AND sc.source = 99
LEFT JOIN exchange_table ex
ON sc.ExchangeID = ex.exchange
Thanks in advance!!!
novicesql123
View 2 Replies
View Related
Jul 23, 2005
Hi,I'm curious about the computational complexity of a query I have. Thequery contains multiple nested self left joins, starting with a simpleselect, then doing a self left join with the results, then doing a selfleft join with those results, etc. What puzzles me is that the timerequired for the query seems to grow exponentially as I add additionalleft joins, which I didn't expect. I expected the inner select toreturn about 25 rows (it does), then I expected the self join to resultin about 25 rows (it does), etc. Each join just adds another column; itdoesn't add more rows. So the left part of the join is staying the samesize, and so is the right part of the join, since I'm always joiningwith the same table.So I would think the time for this query should be (time to join 25rows against the source table) * (num joins), but it seems to besomething like (num rows) ^ (num joins). Any ideas? I'm just trying tounderstand the system a little better. (But if you have any ideas aboutimproving the query, I'm always open to those, too.)The execution plan is what you'd expect: an index seek loop-joined withanother index seek, the results of which are merge-joined with anotherindex seek, the results of which are merge-joined with another indexseek, ad nauseum, until a final "compute scalar cost (39%)" and "select(0%)"For the brave and curious, I've pasted the query below.Thanksselect right(x.cp_yyyymm, 2)+'-'+left(x.cp_yyyymm, 4) as [Month],table0.cp_num_loans/1 as [AFCM9704], table1.cp_num_loans/1 as[AFC9104], table2.cp_num_loans/1 as [BFAT01C], table3.cp_num_loans/1 as[BFAT02B], table4.cp_num_loans/1 as [BFAT03D], table5.cp_num_loans/1 as[BFAT03E], table6.cp_num_loans/1 as [BFAT03F], table7.cp_num_loans/1 as[BFAT04A], table8.cp_num_loans/1 as [BFAT04C], table9.cp_num_loans/1 as[BFAT04D], table10.cp_num_loans/1 as [BFAT99C] from (((((((((((selectdistinct cp_yyyymm from cp_deal_history where cp_deal_id in('AFCM9704', 'AFC9104', 'BFAT01C', 'BFAT02B', 'BFAT03D', 'BFAT03E','BFAT03F', 'BFAT04A', 'BFAT04C', 'BFAT04D', 'BFAT99C') and cp_yyyymmbetween 200304 and 200504) as x left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='AFCM9704') astable0 on x.cp_yyyymm=table0.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='AFC9104') as table1on x.cp_yyyymm=table1.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT01C') as table2on x.cp_yyyymm=table2.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT02B') as table3on x.cp_yyyymm=table3.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03D') as table4on x.cp_yyyymm=table4.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03E') as table5on x.cp_yyyymm=table5.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03F') as table6on x.cp_yyyymm=table6.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04A') as table7on x.cp_yyyymm=table7.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04C') as table8on x.cp_yyyymm=table8.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04D') as table9on x.cp_yyyymm=table9.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT99C') astable10 on x.cp_yyyymm=table10.cp_yyyymm order by x.cp_yyyymm
View 4 Replies
View Related
Sep 21, 2007
Hello,
I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?
How would I rewrite left joins? Any examples?
Thanks.
View 5 Replies
View Related
Dec 16, 2002
Until today, I was always under the impression that left vs. right was determined by which side of the comparison operator the table was located.
In other words:
LEFT JOIN LeftTable.ID = RightTable.ID
would pull all the records from LeftTable and those that matched from from RightTable and that:
RIGHT JOIN RightTable.ID = LeftTable.ID
would pull exactly the same result set but I was wrong. So, if it is not the table position in relation to the comparison operator, is it simply that the tables listed first in the FROM clause aren the ones "Left" of those subsequently entered?
View 2 Replies
View Related
Jul 24, 2012
I'm trying to write a 3 table query using two LEFT JOINs. Originally, I only had one LEFT JOIN and prior to the addition of the the third table (parts) this query worked. Now it doesn't. I think it has to do with my GROUP BY.
SELECT
quote.quote_id, parts.material, machining_operations.machine, machining_operations.per_roughing, machining_operations.per_of_machining,
machining_operations.programming_time, machining_operations.setup_time, machining_operations.cycle_time, machining_operations.notes
quote.part_name, quote.revision_no, quote.quantity, quote.initial_volume, quote.final_volume, quote.material_price, machining_operations.mo_id
FROM quote
LEFT JOIN machining_operations
ON machining_operations.quote_num = quote.quote_id
LEFT JOIN parts
ON parts.package_no = quote.package_no AND parts.part_name = quote.part_name
GROUP BY quote.quote_id
View 10 Replies
View Related
Apr 28, 2008
Hi,
The following query should return a list of clr_id's that have a match in at least 1 of the other fields mentioned in the joins.
declare @keyWord varchar(40)
set @keyWord = 'merc'
set NOCOUNT on
SELECT distinct clr.clr_id
FROM CLR
LEFT OUTER JOIN CO ON CLR.CO_ID = CO.CO_ID
LEFT OUTER JOIN CLR_NM ON CLR.CLR_ID = CLR_NM.CLR_ID
LEFT OUTER JOIN CLR_USE_YR ON CLR.CLR_ID = CLR_USE_YR.CLR_ID
LEFT OUTER JOIN MODL ON CLR_USE_YR.MODL_ID = MODL.MODL_ID
LEFT OUTER JOIN PAINT_CD ON CLR.CLR_ID = PAINT_CD.CLR_ID
WHERE co.long_nm like '%'+@keyWord+'%'
OR clr_nm.clr_nm like '%'+@keyWord+'%'
OR clr_use_yr.yr_num like '%'+@keyWord+'%'
OR paint_cd.paint_cd like '%'+@keyWord+'%'
OR modl.modl_nm like '%'+@keyWord+'%'
The query runs at 3secs.
Could I improve the query somehow?
I was thinking that, since I actually need a distinct set of clr_id's, I should somehow check only the clr_id's that don't have a match in any of the previous joins.
View 19 Replies
View Related
Apr 13, 2006
I have a situation where I'm trying to add a text field to allowlarge Notes to be linked to record in an existing table (rather thansimply adding the new field into the table since relatively fewrecords will have the Note)Tables are basically:T1IDInvNumFK----------------------40|142|243|244|1T2MatDetIdEqpNote-------------------------------------------------40|text44|additional NoteI'm trying to create a subform that will allow the user to view/updaterecords in T1 (there are other fields in T1 that aren't relevant tothe problem) and also add a record into the T2.EqpNote field or edit arecord that already exists.This query works in Access (allows me to type in text into the EqpNotefield and automatically create a new record in T2 with the T1.Id valuein the T2.MatDetId field) :SELECT T1.ID, T1.InvNumFK, T2.EqpNote, T2.MatDetIdFROM T1 LEFT JOIN T2 ON T1.ID = T2.MatDetIdWHERE (((T1.ID)=10230));but when I try to 'translate' it into a version that I can use for thesubform in the adp (it's an Access 2000 project with a SQL Server 2000backend) the 'child' record does not get added into T2.Here's the record source I'm using for the form in the adpSELECT T1.ID, T1. InvNumFK, T2.MatDetId, T2.EqpNote FROM T1 LEFTOUTER JOIN dbo.T2ON T1.ID =T2.MatDetIdWHERE T1.InvNumFk = XX--'master' tableCREATE TABLE [dbo].[T1] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[InvNumFK] [int] NOT NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblBSMaterialDet] WITH NOCHECK ADDCONSTRAINT [PK_T1] PRIMARY KEY NONCLUSTERED([ID]) ON [PRIMARY]GO--Sub tableif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[T2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T2]GOCREATE TABLE [dbo].[T2] ([MatDetId] [int] NOT NULL ,[EqpNote] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[T2] WITH NOCHECK ADDCONSTRAINT [PK_T2] PRIMARY KEY CLUSTERED([MatDetId]) ON [PRIMARY]
View 1 Replies
View Related
Apr 29, 2008
Hi everybody,
In SQL Server is there a quick way to automatically insert empty value instead of NULL? I have a long Insert-SQL command with LEFT JOIN and don't want to use ISNULL for every field.
Researching on this in the meantime myself and I believe there is no way.
Thanks in advance.
View 7 Replies
View Related
Mar 14, 2008
I am having an issue with large queries using Microsoft SQL Server 2005 - 9.00.2221.00 (X64).
I have a query with many INNER/LEFT OUTER/RIGHT OUTER joins which is taking very very very long to run. This looks exactly like this problem described in http://support.microsoft.com/kb/318530. However, this doc says it was fixed in SP1, which is already installed.
Basically I have a query:
SELECT ....
FROM TABLEA
INNER JOIN TABLEB ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'
The query plan for this is using TABLEA as the "main" table and joining everything else to it. The problem is, TABLEA has 117 MILLION records. TABLEB has 10,000 records which match the WHERE. I stopped this query after it ran for 62 HOURS.
If I simply change the query to:
SELECT ....
FROM TABLEB
INNER JOIN TABLEA ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'
The query runs in 15 mins. The query plan now uses TABLEB and the WHERE clause to join all the other tables.
The problem is, this query is generated from a report writter, and I have no control over the way it creates the SQL code.
Any ideas how I can fix this?
View 1 Replies
View Related
Mar 8, 2015
We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.
So rather then the following exemplary fragment
select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.idthis now looks like
select <many items>
from A
left join (B
join C on C.id_B = B.id
) on B.id_A = A.id
Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.
I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.
View 9 Replies
View Related
Dec 12, 2006
I just inherited an app, where I have two tables that look like this:
[Owners]
--------
Owner
...
[Cases]
-------
Owner
Status
Assigned
...
I need a query to get results that look like this:
[Results]
--------
Owner
# of cases records where Status='Open'
# of cases records Where Status='Pending'
# of cases records WHERE Status<>'Closed' AND Assigned=''
I have one query that works already, but it's using several nested selects. I know I ought to be able to do this using group by instead, and I like to know how.
View 4 Replies
View Related
Jan 24, 2006
I am creating a database for a soccer league.
I would like to write a query that would give me results in a league table form .
How could I combine different count queries such as:
select hometeam, count(*) as homegames from matches where comp="en1pp" group by hometeam
order by hometeam
select hometeam, count(*) as homewins from matches where homescore>awayscore group by hometeam order by hometeam
into one query giving a three column result homteams, homegames, homewins.
Thanks
View 6 Replies
View Related
May 20, 2008
I have a table that is linked to other tables in one to many relationship.I have a query using LEFT OUTER JOINs to join the tables together.There are multiple counts in the query and count numbers are messed up.(if there are 4 records from one table and 3 from the other - it shows 12 as the count) Your help is appreciated.
View 2 Replies
View Related
Jan 26, 2004
How would I create a sql statement with 3 or 4 counts which would represent 3 or 4 different columns in a datagrid?
For example
SQL = "SELECT Count(department_id) as "totals1" FROM nonconformance WHERE department_id = '1'"
How would I make additional counts in this SQL statement that looks for when department_id=2 and 3 etc....
Thanks
View 7 Replies
View Related
Dec 8, 1999
I need to build a table to combine data into a single table but I need it to include a count on more than 1 column. For example, I have a table containing a store number, an isbn, an on hand quantity, and an on order quantity. It's probably easier to explain with an example. I have a source table containing this:
store isbn OnHand OnOrder
===== ========== ====== =======
104 0394572368 2 0
108 0394572368 0 1
109 0394572368 2 0
104 3321695545 2 1
108 3321695545 1 0
109 3321695545 3 1
And I need a table that will combine the isbns and give me sums
and counts like this:
Isbn OnHandLocations OnHandQty OnOrderLocations OnOrderQty
========== =============== ========= ================ ==========
0394572368 2 4 1 1
3321695545 3 6 2 2
Does anyone know of a query that can accomplish this? Thanks in advance.
View 1 Replies
View Related
Feb 17, 2014
I have a table called enablers , with the following data
title Raiser Assignedto
book Fred John
Apple Peter Peter
Orange Bill Roger
Cup John Fred
For each time a users name appears in the raiser column they get 1 point, for each time a users name appears in the Assignedto column they get 1 point , but if their name appears in both Raiser and Assignedto for a particular row they only get 1 point not 2 points, I then need a count of raiser points plus a count of assignedto points to give a total points score ( raised plus assignedto)..I am looking how to get the output like below
Name Total Points
Fred 2
Peter 1
Bill 1
John 2
Roger 1
View 8 Replies
View Related
May 7, 2008
Hi, my code repeats the same correct count numbers for every person in a query. I need them to count differently for each person in a table. I am using Access 2007 for now. Thanks.
SELECT tblOutlookTask.AssignedTo,DCount("*","tblOutlookTask","PercentComplete=100") AS Expr1, DCount("*","tblOutlookTask","PercentComplete<>100") AS Expr2
FROM tblOutlookTask
Group By tblOutlookTask.AssignedTo;
I also tried this but it says 'missing operator on the second line' doesn't like the when in case maybe?
SELECT
COUNT(CASE WHEN PercentComplete=100 THEN 1 ELSE NULL END) as Completed,
COUNT(CASE WHEN PercentComplete<>100 THEN 1 ELSE NULL END) as Active,
COUNT(CASE WHEN PercentComplete<>100 And [DueDate]<Date() And [DueDate]>Date()-7 And DueDate<Date() And DueDate>Date()-7 THEN 1 ELSE NULL END) as Overdue,
COUNT(CASE WHEN tblOutlookTask.PercentComplete<>100 And [DueDate]<Date() And [DueDate]>Date()-14 And tblOutlookTask.DueDate<Date() And tblOutlookTask.DueDate>Date()-14 THEN 1 ELSE NULL END) as Offtrack
FROM tblOutlookTask;
View 5 Replies
View Related
Sep 17, 2015
I am trying to insert into a row in SQL 2012 a default value for one column and then the results of some select count(*) queries into the other columns.Here is my example:
insert into x64_hourly_activity (Hour,Ingests,Restores,Proxies, Tape_Onsite, Tape_Offsite)
select (select count(*) from x64_Ingest_metrics_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59),
(select count(*) from x64_restore_metrics_all_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59),
(select count(*) from x64_proxy_metrics_raw_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59),
(select count(*) from x64_tape_metrics_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59 and Storage_Loc = 'XYZ'),
(select count(*) from x64_tape_metrics_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59 and Storage_loc = 'JKL')
What is the statement I use to insert 12AM as a value for Hour?
View 1 Replies
View Related
May 12, 2008
I'd appreciate some help with the issue below - my SQL is a bit rusty and was never that hot to be frank. I'm using SQL Server 2000 (although have a test box with 2005 Express also). I've trawled MSDN and a few forums but can't find the solution (maybe I don't know what I'mm looking for!), so any help would be marvellous...
I have a table with a field called 'IRV' containing a string of comma-separated values. I want to be able to query a point in that string and count the number of times a given value appears. So...as an example, I want to count how many times '1' appears at position 7 in the IRV. I can create SQL to do this as follows:
SELECT COUNT(X) AS is1
FROM myIRVtable
WHERE (SUBSTRING(IRV, 7, 1) = '1')
So far so good. However, it is also possible that the value at position 7 in this string could be '2' (or '3', or '4', etc) - and rather than re-running the query again and again to get these values, I'd like to do it in one hit.
How can I combine all this together - anyone have any brilliant solutions?
View 4 Replies
View Related
Jan 15, 2015
I am trying to create a query that outputs the following data from multiple tables. Here is an example of the raw data right now
Date | MachineNumber | TestName
---------------------------------------
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 620 | Test#1
Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.
Date | MachineNumber | TestName | TestOccuranceCount
-----------------------------------------------------------------
1/1/2015 | 500 | Something | 4
1/1/2015 | 510 | NewTest | 3
1/1/2015 | 620 | Test#555 | 1
I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.
View 9 Replies
View Related
Oct 8, 2015
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
View 5 Replies
View Related
