I'd like to return the following result set:
CompanyModules.CompanyID | Modules.Name | Present
1 | A | True
1 | B | True
1 | C | True
2 | A | True
2 | B | False
2 | C | False
What would be the query for this? Thanks.
Edit: This is the query I have tried:
select CompanyModules.CompanyID, Modules.Name, count(Modules.ID) as Present from
CompanyModules RIGHT outer Join Modules on CompanyModules.ModuleID = Modules.ID
group By CompanyModules.CompanyID, Modules.Name
Order by CompanyID
However, it only returns a partial result set:
CompanyModules.CompanyID | Modules.Name | Present
1 | A | 1
1 | B | 1
1 | C | 1
2 | A | 1
I have a SELECT Statement that I am using that is pulling from two tables. There won't always be results in the second table so I made a LEFT OUTER JOIN. The problem I am having is that I need to have three conditions in there:WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))The two conditions from the request table are causing the entire query to return NULL as the value. I need help trying get a value whether or not there are any results in the request table.Here is the full select statement:SELECT (SELECT SUM(ISNULL(request.request_duration, '0')) AS Expr1 FROM employee LEFT OUTER JOIN request AS request ON employee.emp_id = request.emp_id WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE())) AS dayspending FROM employee AS employee_1 LEFT OUTER JOIN request AS request_1 ON employee_1.emp_id = request_1.emp_id WHERE (employee_1.emp_id = @emp_id) GROUP BY employee_1.emp_id, employee_1.emp_begin_accrual, employee_1.emp_accrual_rate, employee_1.emp_fname, employee_1.emp_minitial, employee_1.emp_lname
SELECT PR.WBS1, PR.WBS2, ledgerar.invoice, SUM(CASE WHEN ledgerar.transtype = 'in' AND ledgerar.period = '200405' THEN ledgerar.amount * - 1 END) AS amount FROM PR LEFT OUTER JOIN LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND LedgerAR.WBS3 = PR.WBS3 WHERE (LedgerAR.wbs1 = '001-333') AND ledgerar.period = '200405' GROUP BY pr.wbs1, pr.wbs2, ledgerar.invoice
But I don't know how to get the query to display all of wbs2 whether it is null or not. So, for each invoice number there should be five records according to the wbs2 codes (500, 100, 140, 150, 160)
SELECT [tblSections].[pageTitle], [tblSections].[sectionURL], [tblSectionContents].[articleID], [tblSectionContents].[fileID], [tblSectionContents].[linkID], [tblCopy].[copyText], [tblFiles].[fileName], [tblFiles].[fileCaption], [tblGroupings].[grouping], [tblLinks].[linkURL] FROM [tblSections] LEFT JOIN [tblSectionContents] ON [tblSectionContents].[sectionID] = [tblSections].[id] LEFT JOIN [tblCopy] ON [tblSectionContents].[articleID] = [tblCopy].[id] LEFT JOIN [tblFiles] ON [tblSectionContents].[fileID] = [tblFiles].[id] LEFT JOIN [tblGroupings] ON [tblFiles].[groupingID] = [tblGroupings].[id] LEFT JOIN [tblLinks] ON [tblSectionContents].[linkID] = [tblLinks].[id] WHERE [tblSections].[id]=2 ORDER BY [tblSectionContents].[articleID], [tblSectionContents].[fileID], [tblSectionContents].[linkID]
If I pass it the ID of a section that has files or copy or [stuff in other tables] attached, then I get a result set that makes sense.
But if I pass it a section ID that doesn't reference any other content tables (ie: the section just has a title and a link URL), I don't get anything back.
Shouldn't it should still get me the fields from the row in tblSections that matches the ID I'm passing it?
SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.id instead of
SELECT * FROM a LEFT JOIN b ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
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.
OLEDB source 1 SELECT ... ,[MANUAL DCD ID] <-- this column set to sort order = 1 ... FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2 SELECT ... ,[Bo Tkt Num] <-- this column set to sort order = 1 ... FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above. join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column) ... 4-400-8000119 4-400-8000120 4-400-8000121 4-400-8000122 <--row not joining 4-400-8000123 4-400-8000124 ...
result of source2 (..tkt num column) ... 4-400-1000118 4-400-1000119 4-400-1000120 4-400-1000121 4-400-1000122 <--row not joining 4-400-1000123 4-400-1000124 4-400-1000125 ...
All other rows are joining as expected. Why is it failing for this one row?
Hello! I need to write a query using left outer join and I'm having trouble with it. I have 2 tables:customer and cust_info. I want to pull all records from customer table and cust_info table even if there is no related data in cust_info table. But I need one condition in this query:only records from customer table where cus_type in ("A","B","C"). I don't need all other types ("D","E").
So my query looks like this:
select customer.cus_name,customer.cus_address,customer.cu s_type,cus_info.status from customer left outer join cus_info ON customer.sxdat_pk = cus_info.sxdat_pk and cus_type in ("A","B","C") AND cus_info.cus_table = 'CUSTOMER'
The result should be like this:
cus_name cus_address cus_type status Amoco 457 bent A new Bingo 47 lone oak C NULL Sears 1020 Magic dr. B exist
But my query pulls records for customers with type "D" and "E" that I'm trying to exclude from result.
a job can be done with 1 or more operators I want to be able to show user both assigned and unassigned operator on the front end on a certain job so I haev only parm being passed in @jobID.
How do I got about showing all the operators but somehow differentiate ones already assigned to job
SELECT e.rec_id,e.EMP_ID,e.FIRST_NAME,e.PROJECT_ID, e.DEPARTMENT_CODE, e.POSITION_CODE,e.WORKING_STATUS, d.department_name,p.position_name, c.country_code, c.country_name FROM employee e LEFT OUTER JOIN position p ON e.position_code = p.position_code LEFT OUTER JOIN department d ON e.department_code = d.department_code left outer join country c ON e.country_code = c.country_code
where I use where e.working_status <>'left company'
it retreive few records, it shoud display all records except where e.working_status <> 'left company' i.e. left outer join is not working,
we have approx 1000 employee records, 100 employee left the company. it should show 900 employee records but is show few records, how can use where clause in left outer join it show all records except where e.working_status <> 'left company'
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three"Left Outer Joins" in order to return every transaction for a specificset of criteria.Using three "Left Outer Joins" slows the system down considerably.I've tried creating a temp db, but I can't figure out how to executetwo select commands. (It throws the exception "The column prefix'tempdb' does not match with a table name or alias name used in thequery.")Looking for suggestions (and a lesson or two!) This is my first attemptat SQL.Current (working, albeit slowly) Query BelowTIASELECTLEDGER_ENTRY.entry_amount,LEDGER_TRANSACTION.credit_card_exp_date,LEDGER_ENTRY.entry_datetime,LEDGER_ENTRY.employee_id,LEDGER_ENTRY.voucher_explanation,LEDGER_ENTRY.card_reader_used_ind,STAY.room_id,GUEST.guest_lastname,GUEST.guest_firstname,STAY.arrival_time,STAY.departure_time,STAY.arrival_date,STAY.original_departure_date,STAY.no_show_status,STAY.cancellation_date,FOLIO.house_acct_id,FOLIO.group_code,LEDGER_TRANSACTION.original_receipt_idFROMmydb.dbo.LEDGER_ENTRY LEDGER_ENTRY,mydb.dbo.LEDGER_TRANSACTION LEDGER_TRANSACTION,mydb.dbo.FOLIO FOLIOLEFT OUTER JOINmydb.dbo.STAY_FOLIO STAY_FOLIOONFOLIO.folio_id = STAY_FOLIO.folio_idLEFT OUTER JOINmydb.dbo.STAY STAYONSTAY_FOLIO.stay_id = STAY.stay_idLEFT OUTER JOINmydb.dbo.GUEST GUESTONFOLIO.guest_id = GUEST.guest_idWHERELEDGER_ENTRY.trans_id = LEDGER_TRANSACTION.trans_idAND FOLIO.folio_id = LEDGER_TRANSACTION.folio_idAND LEDGER_ENTRY.payment_method='3737******6100'AND LEDGER_ENTRY.property_id='abc123'ORDER BYLEDGER_ENTRY.entry_datetime DESC
What is the result of adding an or into a left outer join?For example:Selectl.list_id,l.request_idFrom List lleft outer join Request ron l.request_id = r.request_id or l.list_id = r.list_idThanks :)
I am trying to get a query to work and having trouble. I have two tables. I want all records in my left table plus null in the right table if no matching records exist or one record only if there are matching records. What I don't want is multiples if there is more than one match in the right table.
So if I use this: select * from contacts left outer join sales on contacts.id = sales.contactid
I will get more than one record per contact if there is more than one sale. I only want one record per contact even if there is more than one sale.
Greetings! I have a problem converting *= to LEFT OUTER JOIN. Here's my code that will retrieve a single record when executed:
SELECT * FROM ar_t_memo_hdr A, ar_r_trbal_cust_tmp B, common..fs_currency_master E, ar_reval_history rev
WHERE A.company_no = 'RAM' AND A.company_locn = 'BANG' AND E.fs_company_code = 'RAM' AND E.fs_locn_code = 'BANG' AND A.currency = E.fs_curr_code AND A.memo_type = 'DM' AND A.cust_no = B.cust_no AND A.so_locn = B.so_locn AND B.host_id = 100 AND A.memo_dt <= '2006-09-25 00:00:00.000' AND A.unapplied_amt >= 0 AND ( (CONVERT(varchar,A.reversal_dt,101) > '2006-09-25 00:00:00.000' AND A.status = 'V') OR (a.status ='A') ) AND A.company_no *= rev.company_no AND A.company_locn *= rev.company_locn AND A.cust_no *= rev.cust_no AND B.cust_no *= rev.cust_no AND B.so_locn *= rev.so_locn AND A.memo_type *= rev.document_type AND A.memo_no *= rev.document_no AND rev.created_date = (SELECT MAX(his.created_date) FROM ar_reval_history his WHERE his.company_no = rev.company_no AND his.company_locn = rev.company_locn AND his.document_no = rev.document_no AND his.document_type = rev.document_type AND his.rev_dt <= DATEADD(dd,1,'2006-09-25 00:00:00.000'))
After replacing *= with LEFT OUTER JOIN, the above query returns nothing. Could some one please help me resolve the problem??
Need some assist - not sure I am using right join? I am using a Left Outer join - Table A - has Cust # and Cust Name Table B - has Cust #
If there are 2 entries with same Cust # and a slight difference in spelling of cust name, I get 2 entries. Looking to get 1 row based on cust #, regardless of spelling of name?
Please help... I've got Two tables, Problem and Breach, in DB Support. The main Table (Problem) has a primary key on field Number and I retrieve most of my data from this table but the other fields are irellevant. The Breach Table includes fields: Number & Keyword. I use an Left Outer Join to connect from Problem.Number to Breach.Number and retrieve the Breach.Keyword if any. If there's no records in Breach it still retrieves all the Problem.Numbers and that's great but when there's more than one Breach.Keyword for the Problem.Number I retrieve more than one record at this stage but I would only like to retrieve one. My Query:
Select
Support_Problem.Number, Breach.Keyword
From
Support_Problem Support_Problem
Left Join Support_Breach Support_Breach ON Support_Problem.Number=Support_Breach.Number
Having a little trouble with this sql... UPDATE tblCalibrationReview SET poNumber = CalibrationReview.[PONumber], calCertNumber = CalibrationReview.[CalCertNumber], q1Review = (CASE CalibrationReview.ReviewQ1 WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 WHEN 'N/A' THEN 3 ELSE 3 END), followUp = CalibrationReview.[Followup] FROM CalibrationReview LEFT OUTER JOIN tblEquipments ON tblCalibrationReview.equipmentId = tblEquipments.ID WHERE CalibrationReview.[EquipmentNumber] = tblEquipments.AssignedID The left outer join. I need to read CalibrationReview.EquipmentNumber, look at tblEquipments and get the ID field where that number matches and update tblCalibrationReview.equipmentId with that number. I'm getting the error when running: The column prefix 'tblCalibrationReview' does not match with a table name or alias name used in the query. Thanks for any input, Zath
my table isCustomer Customer Id--------- ----------------Mary 1Jhon 2Anna 3OrderId CustomerId Product ProductDesc------- --------- --------- -----------1 1 video bla bla2 1 tv bla bala3 2 video bla bla4 2 cd bla blaI want to see-------marry tv bla blaJohn cd bla blaanna
I need a small help in creating a join query in sql.
My two tables are A and B. A has got columns id, dname, counter and date. B has got two columns link and date. The primary key of A is the column Id whereas in table B, both are primary keys(composite key).
I need to get the records (id,dname,counter) from A which has no corresponding link in table B.
To be precise, If the table A has fields (id,dname,counter) (1,abc,2000) and if table B has no record for abc, this row should be returned from A.I hope using left outer join will help me in getting my desired result.I hope someone will be able to help me out.
Hi, I want to get join of two tables A and B in such a way that all the Data from A for a particular Date should be extracted and then from table B Reqired column should selected against that data (it can be null). But if i use the following query: 1) Select A.*,B.Reqired from A Left Outer Join B on A.Same=B.Same Where A.Date = '1/22/2008' it first joins the Table A and B (A contains millions of records) which takes a lot of time and then where is applied which takes a lot of time. And if I use the following query: 2)Select A.*,B.Reqired from A Left Outer Join B on A.Same=B.Same AND A.Date = '1/22/2008' in this case A.Date = '1/22/2008' has no effect and all the data from the table A comes. I have also tried first making the temp table for A.Date = '1/22/2008' and the joining with B but its cost is same as 1. For this purpose Oracle allows the use of (+) operator like: 3)Select A.*,B.Reqired from A,B Where A.Same=B.Same(+) AND A.Date = '1/22/2008' which means all data from A for '1/22/2008' Plus B.Required against it whether its null or not. Please let me know if there is a way around in MS SQL SERVER 2005. Thanx.
Dear All, I am not sure this is the correct forum for my issue, still im going on..... I have a sql query which uses left outer join and where clause select I.ir_id, I.ir_title,I. ir_b_type, I.ir_label, I.ir_createdby, I.ir_createdon, B.mstatus, B.bstatus from BR_IR I left outer join BR_BS Bon I.ir_id = B.bs_ir_id where I.ir_tr_rel = 'V03.03.06' order by I.ir_id desc this works perfect. Now I want to exclude few records from the result on condn 'bstatus <> 'Yes'. So now my query will be select I.ir_id, I.ir_title, I.ir_b_type, I.ir_label, I.ir_createdby, I.ir_createdon, B.mstatus, B.bstatus from BR_IR I left outer join BR_BS Bon I.ir_id = B.bs_ir_id where I.ir_tr_rel = 'V03.03.06' and B.bstatus <> 'Yes'order by I.ir_id desc The below query will not return the left table records which doesnt have a matching one in the right table. It works something like inner join. Could you please anyone tell me,where I am wrong? Thanks, Girija
Hi friends,i've one table as pro_area. it consist two fields pro_area_key and pro_area_name.i've second table as pro_access it consist three fields pro_access_key, pro_access_name, user_key and pro_area_key.i've perform the left outer join on both these tables, query is as followSelect pa.pro_area_key, pa.pro_area_name from pro_area pa left outer join pro_access pr on pa.pro_area_key = pr.pro_area_key this query gives perfect result but now i want the same result according to user_keylike i can give user_key fields valueany help for it,Thanx in advance
Hello ! I want to make two left outer joins. The first on table A with table B and the second on table B with table C. The problem is that an error occurs and it tells me that left outer join on a field that is part of a left outer join is prohibited.
This is what i want : select a.product , b.description , c.description from products as a , media as b , topics as c where a.media*=b.media and b.topic*=c.topic
or
select a.product , b.description , c.description from products as a left outer join b on a.media=b.media left outer join c on b.topic=c.topic
SELECT CO.CO_ID, MAX(PR.DOCNO) AS pO_DOCNO ,MAX(CR.DOCNO) AS CR_DOCNO FROM COMPANY CO lEFT OUTER JOIN PROCUREMENT PR ON OG.CO_ID=PR.CO_ID lEFT OUTER JOIN CONTRACT CR ON CO.CO_ID=CR.CO_ID GROUP BY OG.CO_ID
the result is ok but the problem its taking infinte time if i add more tables to the outer join, i have more tables and each with huge number of records
any better way to do this ?? its true performance is a big deaaaal
I am using Transact-Sql 2005, and I'm trying to do a left outer join, including only certain accounts. The account number is x-xxx-xxxx-xxxx. I want to include only accounts where the last 4 digits are > 7149, and the first 5 digits are between 2-110 and 2-999, or are equal to 8-001.
This is my code, based on 2 temporary tables I have previously populated:
from #budgets b left outer join #transactions t on t.accountnumber=b.accountnumber And right(t.accountnumber,4) > 7149 and (left(t.accountnumber,5) between '2-110' and '2-999' or left(t.accountnumber,5)='8-001') order by b.accountnumber
I keep getting accounts with the last 4 digits > 7149, and also accounts whose numbers don't fall into the desired group of the first 5 digits.
I'm fairly new to SQL, and I'm thinking there must be a way to do what I want to do, but I don't know what it is.
We've got this whole mess trying to export data from Lotus Notes toGreat Plains. As part of the process, we need to ensure that a Notesrecord is exported only once, but have the ability to re-export it ifsomething breaks. So, for that reason (and some others) I decided touse a simple log table:create ExpLog (ExpDateTime Datetime not null,DBID varchar(16) not null,docUNID varchar(32) not null,)So, the code records the records datetime, and the DBID and docUNID(which uniquely identifies every Lotus Notes "record"). My question isin the sql that exports the records. Which is likely to be moreefficient?selectfield1,...from LotusNotes LN left outer join ExpLog EL on LN.DBID = EL.DBID andLN.docUNID = EL.docUNIDwhere EL.docUNID is nullorselectfield1,...from LotusNotes LNwhereln.DBID not in (select DBID from EL)andln.docUNID not in (select docUNID from EL)How would one go about figuring this out assuming that the SQL is goinginto a DTS package. (I think that means the plan isn't saved?)Thanks.
