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
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
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";
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.
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?
Hi,
I'm executing a nested queries consisting of LEFT OUTER JOIN for:
Duration = 27 sec;
Reads = 1690;
Number of users = 1.
This is extremely slow for a small set of data. I'm afraid that when we have a larger dataset and more concurrent users that query will take forward.
What am I doing wrong for my query to be taking too long?
Here's the query:
SELECT B.Business_Id as Business1_0_,
B.Place_Of_Business_Id as Place2_30_0_,
B.Business_Type_Id as Business3_30_0_,
B.Business_Name as Business4_30_0_,
B.Business_Description as Business5_30_0_,
B.Last_Update_Timestamp as Last6_30_0_
FROM Busines as B
LEFT OUTER JOIN Business_Service as BS
ON B.Business_Id = BS.Business_Id
LEFT OUTER JOIN Business_Service_Category as BSC
ON B.Business_Id = BSC.Business_Id
LEFT OUTER JOIN Business_Sub_Category as BSSC
ON B.Business_Id = BSSC.Business_Id
where B.Business_Id IN (
Select B.Business_Id from Busines as B
JOIN Business_Address as BA ON B.Business_Id = BA.Business_Id
JOIN Address as A ON A.Address_Id = BA.Address_Id
WHERE A.City_Name like '%New York%'
and A.state = 'NY') AND ( FREETEXT(B.Business_Name, 'Designer')
or BSC.Service_Category_Id IN
( Select SC.Service_Category_Id from Service_Category as SC
where FREETEXT(SC.Service_Category_Name, 'Designer') )
or BSSC.Sub_Category_Id IN (
Select SSC.Service_Sub_Category_Id from Service_Sub_Category as SSC
where FREETEXT(SSC.Service_Sub_Category_Name, 'Designer') )
or BS.Service_Id IN (
Select S.Service_Id from Service as S
where FREETEXT(S.Service_Name, 'Designer') ) )
----------------------------------------------------------------------------------------------------------------------------------------
Here's the SQL Query Plan:
|--Nested Loops(Left Semi Join, OUTER REFERENCES.[Business_Id], [BS].[Service_Id], [BSC].[Service_Category_Id], [BSSC].[Sub_Category_Id]))
|--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Sub_Category].[Business_Id] as [BSSC].[Business_Id]))
| |--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Service_Category].[Business_Id] as [BSC].[Business_Id]))
| | |--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Service].[Business_Id] as [BS].[Business_Id]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES.[Business_Id]))
| | | | |--Sort(DISTINCT ORDER BY.[Business_Id] ASC))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[BA].[Business_Id]))
| | | | | |--Hash Match(Inner Join, HASH.[Address_Id])=([BA].[Address_Id]), RESIDUAL[PB].[dbo].[Address].[Address_Id] as .[Address_Id]=[PB].[dbo].[Business_Address].[Address_Id] as [BA].[Address_Id]))
| | | | | | |--Clustered Index Scan(OBJECT[PB].[dbo].[Address].[PK__Address__0519C6AF] AS ), WHERE[PB].[dbo].[Address].[State] as .[State]='NY' AND [PB].[dbo].[Address].[City_Name] as .[City_Name] like '%JayVille%'))
| | | | | | |--Index Scan(OBJECT[PB].[dbo].[Business_Address].[ClusteredIDX_Business_Address] AS [BA]))
| | | | | |--Index Seek(OBJECT[PB].[dbo].[Busines].[UI_ukBusiness] AS ), SEEK.[Business_Id]=[PB].[dbo].[Business_Address].[Business_Id] as [BA].[Business_Id]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT[PB].[dbo].[Busines].[PK_Busines] AS ), SEEK.[Business_Id]=[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]) ORDERED FORWARD)
| | | |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Service].[ClusterIDX_Business_Service] AS [BS]))
| | |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Service_Category].[ClusterIDX_Business_Service_Category] AS [BSC]))
| |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Sub_Category].[ClusterIDX_Business_Sub_Category] AS [BSSC]))
|--Concatenation
|--Filter(WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id] = [Full-text Search Engine].[KEY]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
| |--Clustered Index Seek(OBJECT[PB].[dbo].[Service_Category].[PK__Service_Category__15502E78] AS [SC]), SEEK[SC].[Service_Category_Id]=[PB].[dbo].[Business_Service_Category].[Service_Category_Id] as [BSC].[Service_Category_Id]) ORDERED FORWARD)
| |--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Service_Category].[Service_Category_Id] as [BSC].[Service_Category_Id]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
| |--Clustered Index Seek(OBJECT[PB].[dbo].[Service_Sub_Category].[PK_Service_Sub_Category] AS [SSC]), SEEK[SSC].[Service_Sub_Category_Id]=[PB].[dbo].[Business_Sub_Category].[Sub_Category_Id] as [BSSC].[Sub_Category_Id]) ORDERED FORWARD)
| |--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Sub_Category].[Sub_Category_Id] as [BSSC].[Sub_Category_Id]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
|--Clustered Index Seek(OBJECT[PB].[dbo].[Service].[PK__Service__117F9D94] AS ), SEEK.[Service_Id]=[PB].[dbo].[Business_Service].[Service_Id] as [BS].[Service_Id]) ORDERED FORWARD)
|--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Service].[Service_Id] as [BS].[Service_Id]))
|--Remote Scan(OBJECTFREETEXT))
Please Help....
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.
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.
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign
then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033
4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators.
The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
Any ideas on the principles involved here.
Anyone know why using
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".
Any enlightenment is very appreciated.
Thanks
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.
Please help.
Thanks,
lena
Hi there,
I have three tables
Job
JobID
WeightIn
WeightOut
Operator
OperatorID
NameFirst
NameLast
JobOperator
JobOperatorID
JobID
OperatorID
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
Thanks!
Good evening!
Well,in relational algebra we have left outer join.
In SQL is any command for that?
How can i use left outer join?
I'm relatively new to SQL and I am wondering about when to use left outer joins. When should you use left outer joins?
View 2 Replies View RelatedHow can user left outer join with three tables ?
it retreive all employees name if position and department
are not found
fields
--------
department_code
position_code
position_code and department_code include in employee table
SELECT e.name,e.position_code
FROM employee e LEFT OUTER JOIN position p
ON e.position_code = p.position_code
regards
Mateen
I am using following query
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'
Thanks
Mateen
Hi All,
This is my first post to the forums. Can anyone tell me what is wrong with the following statement: -
SELECT V.VNUMBER,
V.PARAMETERID,
V.SEQUENCENUM,
V.VALUE,
V.GROUPID,
G.DESCRIPTION,
V.ENTRYDATE,
V.COMMENTS,
P.PARAMETERLABEL,
P.CATEGORY,
P.FIELDSCALE
FROM VBS_PARAMETERSP, (VBS_VOLUNTEER3 V LEFT OUTER JOIN VBS_GROUPS G)
ON V.GROUPID = G.GROUPID
AND P.PARAMETERID= V.PARAMETERID
WHERE V.PROSAMPLEID= 1
I'll appreciate a response.
Many Thanks,
I.A
Hi iam new to sql forum can you send sample code for left outjoin
and right outer join
Desikankannan
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
View 1 Replies View RelatedWhat 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 :)
View 1 Replies View RelatedHi,
I have come across this type of a query.
SELECT T1.*, T2.*, T3.*, T4.*
FROM T1
LEFT OUTER JOIN T2 ON T1.aaa = T2.bbb
LEFT OUTER JOIN T3 ON T2.bbb = T3.ccc
LEFT OUTER JOIN T4 ON T3.ccc = T4.ddd
WHERE T1.aaa = @p1
Is this correct?
For the first join T1 is the left table. What about second and third joins? are the left tables T2 and T3 resp or T1 itself?
Thanks in advance
P
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.
How would I accomplish this?
Hi All,
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??
Thank you,
Chaithanya
Hi,
I have come across this type of a query.
SELECT T1.*, T2.*, T3.*, T4.*
FROM T1
LEFT OUTER JOIN T2 ON T1.aaa = T2.bbb
LEFT OUTER JOIN T3 ON T2.bbb = T3.ccc
LEFT OUTER JOIN T4 ON T3.ccc = T4.ddd
WHERE T1.aaa = @p1
Is this correct?
For the first join T1 is the left table. What about second and third joins? are the left tables T2 and T3 resp or T1 itself?
Thanks in advance
P
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?
Thanks
Hi All Dudes and Dudesses
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
I have been using an outer join to display information in a form. I would like to edit the rows that are returned. The join shows all of the rows in the 1st table, and the corresponding rows in the 2nd other. If there are no corresponding rows in the 2nd table, a NULL is displayed. The problem is when I try to edit one of the fields that is "NULL", I get an error. I know why the error is happening, because the NULL field is a row that does not exist, yet. The jet database engine will automatically insert rows into the 2nd table, but SQL server will not. I have tried to set primary key and foreign key properties for the tables in question, but it still does not work. Does anyone know how to do this?
Thanks for the help.
Davant
I have been reading about outer joins and how they are much faster to process on larger data sets than NOT EXISTS are..Did anyone find any significant performance gain..Logically, using an outer join does not make sense in this context..Can someone shed some light on this subject?
View 2 Replies View RelatedI am trying to create an outer join between two tables in a query thatincludes several other tables.When I double-click on the Join line, it presents three join options:1) ONLY records from table1 and table2 where join fields are equal2) ALL values from table1 and ONLY records from table2 where joinfields are equal3) ALL values from table2 and ONLY records from table1 where joinfields are equalIn my case, I want option 2 - all values from table1, and if there isno match to table2, I want a blank to appear in the output.When I select this option, I get the following error:"Can't have outer joins if there are more than two tables in thequery."How can I get around this, since there are other tables in my query?Thanks.Dennis HancyEaton CorporationCleveland, OH
View 1 Replies View RelatedHaving 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
View 3 Replies View RelatedHi all,
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.
Thanking all in advance for your valuable time.
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.