OUTER LEFT JOINs On SQL CE

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


ADVERTISEMENT

Right/left Outer Joins && Performance

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

How Do You Do Multiple Left Outer Joins???

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

Speed Up 6 Consecutive Left Outer Joins

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

Query Optimizer Problems With Many LEFT/RIGHT OUTER JOINs

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

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

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

Multiple Left Joins (2 Left Joins 1 Big Headache)

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

Joins On Views That Are Formed With Outer Joins

Nov 3, 2000

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.

View 1 Replies View Related

Left Join Vs Left Outer Join Syntax Generates Different Execution Plans

Apr 16, 2008



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

View 5 Replies View Related

Left Outer Join-please Help!

Jan 7, 2002

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

View 4 Replies View Related

Left Outer Join?

Jun 17, 2008

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!

View 5 Replies View Related

Left Outer Join

Dec 4, 2006

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?

View 20 Replies View Related

Left Outer Join

Jan 15, 2007

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 Related

Left Outer Join

Sep 4, 2007

How 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

View 4 Replies View Related

Left Outer Join With &<&>

Sep 12, 2007

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

View 6 Replies View Related

LEFT OUTER JOIN

Oct 23, 2007

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

View 14 Replies View Related

Left Outer Join

Jan 26, 2008

Hi iam new to sql forum can you send sample code for left outjoin
and right outer join

Desikankannan

View 1 Replies View Related

Need Help With Left Outer Join...

Sep 15, 2005

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 Related

Or In Left Outer Join.

Jul 20, 2005

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 :)

View 1 Replies View Related

LEFT OUTER JOIN

Dec 20, 2007

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

View 1 Replies View Related

Left Outer Join? Or Not?

Aug 7, 2007

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?

View 3 Replies View Related

LEFT OUTER JOIN

Sep 27, 2006

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

View 3 Replies View Related

LEFT OUTER JOIN

Dec 20, 2007

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

View 1 Replies View Related

Left Outer Join

Apr 18, 2008



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

View 2 Replies View Related

Left Outer Join

Apr 12, 2006

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

View 10 Replies View Related

Outer Joins

Sep 2, 1999

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

View 1 Replies View Related

Outer Joins

Apr 29, 1999

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 Related

Outer Joins

Jul 20, 2005

I 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 Related

SQL - Left Outer Join Update

Mar 14, 2007

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

View 4 Replies View Related

Left Outer Join And Last Record

Sep 21, 2007

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 Related

How To Use Left Outer Join In Two Tables?

Nov 8, 2007

Hi 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.

View 1 Replies View Related

Condition In Left Outer Join

Jan 22, 2008

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.
 
 

View 8 Replies View Related

Left Outer Join With Two Conditions

Feb 11, 2008

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

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved