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


ADVERTISEMENT

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

'Left Outer Merge Join' Failing To Join Valid Row

Aug 10, 2007

Scenario:

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?

View 1 Replies View Related

Left Join Vs Left Outer Join

Apr 7, 2008

Is there any difference between left join and left outer join in sql server 2000?please reply with example if any?
Thanks in advance

View 13 Replies View Related

Left Join Vs Left Outer Join

May 14, 2008

Hi,

Whats the diference between a left join and a left outer Join

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

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

Left Outer Join Problem

Mar 21, 2008

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 

View 14 Replies View Related

Left Outer Join Problems !

May 15, 2004

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

any help ?

thanks !

View 7 Replies View Related

Any Other Choice Than Left Outer Join??

Apr 13, 2008

i have three tables company as co, Procurement as po,contracts as cr
Co_id as primary for co
co_id forign for po
co_id forign for cr

i want the query to get me only one record for every co_id if it has a company in certain PO or CR ( i used max fnctn to get me only one record)

result:

CO_ID,PODOCNO,CRDOCNO
----- ------- -------
1 350 400
2 355 1064
3 NULL 500
4 600 NULL

I used the left outer join in this manner


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

View 8 Replies View Related

Left Outer Join And Conditions

Mar 17, 2008

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:

select
b.gl7accountsid,
b.accountnumber,
t.description,
t.category,
t.postdate,
t.poststatus,
t.transactiontype,
t.transamount,
coalesce(t.transamount,0) as TransactionAmount,
t.encumbrancestatus,
t.gl7fiscalperiodsid,
b.budamount,
b.gl7budgetscenariosid

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.

Can anyone help?

Thanks very much ,

Sue

View 5 Replies View Related

Left Outer Join Vs. Pkey In (...

Jul 23, 2005

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.

View 1 Replies View Related

Left Outer Join Query?

Sep 12, 2006

Hello all,

I am stuck in a bit of a mess, and i really don't know how to get out of it.

I am a dot.net developer, working with MS SQL server 2000.

I am trying to write a query which essentially gives the amount of stock available for a particular stock_code.

select Stock_code, description from stock

Now what i want to do is, for each one of the stock_code that apears in the table above i want to look into another table called pop_item, and get the closest delivery_date for that particular stock_code. So i have top 1 and order it by delivery_date desc.

Individually for a particular stock_code, i can do this no problem. And here is my code for stock_code='E0016/6'

select top 1
stock_code, delivery_date, order_number,qty_order-qty_delivered as onorder
from pop_item
where
stock_code='E0016/6' and
qty_order>qty_delivered
order by delivery_date desc

But I can't seem to be able to do this for all the stock_code, and not a specific one, cause even though i try and left outer join it, i can't access the outer stock_code from the first query into the next...

i.e

select stock.Stock_code, description, tempp.stock_code, tempp.delivery_date, tempp.onorder from stock

left outer join

(select top 1
stock_code, delivery_date, order_number,qty_order-qty_delivered as onorder
from pop_item
where

--Can't say this(stock_code= stock.stock_code and )
qty_order>qty_delivered
order by delivery_date desc) as tempp

on tempp.stock_code=stock.stock_code

Now my question is, is there anyway to access stock.stock_code within the second query? Casue the whole query on top returns only one value for delivery_date, only of the highest delivery date in the whole of pop_item. which make sense... but i don;t know how to get around this...

OOOOOOOOOOOOOOOOOOhhhhhhhhhhhhhhhhhhhhhhhhhhhhh!

Hope someone can help me.

Regards,

Munira

ps- should i be using a cursor, can i call cursors from asp.net. every where i read about cursors they adivice us not to use them.

View 8 Replies View Related

Sub-select Vs. LEFT OUTER JOIN

May 11, 2007

I think it is quite often when you need to view some records, which refer (by key) to data in other tables. For instance, a user belongs to a group but it is preferable to show group name in the user data rather than group id. The options are

1) LEFT OUTER JOIN:
SELECT users.id, groups.name FROM users LEFT OUTER JOIN groups ON users.[group] = groups.id

2) A Subselect:
SELECT id, [group] = (SELECT [name] FROM groups WHERE id = users.[group]) FROM users

Which is better and why?

View 2 Replies View Related







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