Outer join not working correctly

Question

Post reply

Outer join not working correctly

cmc_dermo

SSC Enthusiast

Points: 199
More actions
September 3, 2012 at 5:20 pm

#393080

In the words of David Gray, please forgive me if i have posted this in the wrong topic and have upset people.
I have a query where i would like to show all items from the items table even if no data exists for that item in the stock table.
For some reason it is not showing all the items, it looks like it is ignoring the outer join. Can anyone help an old stupid man please?
Code:
SELECT Items.Item_Desc, Items.Updated_By, m_Item_Type.Item_Type_Desc, s_Venue_Stock.Opening_Balance, m_Venue.Venue_Name
FROM m_Venue INNER JOIN
s_Venue_Stock ON m_Venue.Venue_Code = s_Venue_Stock.Venue RIGHT OUTER JOIN
m_Items AS Items INNER JOIN
m_Item_Type ON Items.Item_Type = m_Item_Type.Item_Type_ID ON s_Venue_Stock.Item = Items.Item_ID AND s_venue_stock = 1

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Sumit Rastogi SSC Eights! Points: 838 More actions · Answer 1

The statement is wrong:

You have written the Right Outer Join but this include the columns on which it have the join.

Try to post the query as prescribed in below link:

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Not far off. You need a left outer join to the items table: this will return all rows from the left hand side of the join (venue stock) whether or not there are matching rows on the right hand side (items). Next I'd change the join between items and itemtype to a LEFT JOIN - for now.

Which gives you this:

SELECT

Items.Item_Desc,

Items.Updated_By,

it.Item_Type_Desc,

vs.Opening_Balance,

v.Venue_Name

FROM m_Venue v

INNER JOIN s_Venue_Stock vs

ON v.Venue_Code = vs.Venue

LEFT OUTER JOIN m_Items AS Items

ON vs.Item = Items.Item_ID

--AND vs = 1 -- what table is this column from?

LEFT OUTER JOIN m_Item_Type it

ON Items.Item_Type = it.Item_Type_ID

Give it a whirl, see if it generates the results you are expecting.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lokesh Vij SSChampion Points: 10836 More actions · Answer 3

cmc_dermo (9/3/2012)
I have a query where i would like to show all items from the items table even if no data exists for that item in the stock table.

Just a tip - If you want to show all items then your items table should be on the left side of left outer join or on the right side of right outer join 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

cmc_dermo (9/3/2012)
...I have a query where i would like to show all items from the items table even if no data exists for that item in the stock table...

Having read the spec properly - thanks Lokesh Vij - here's attempt #2:

SELECT

Items.Item_Desc,

Items.Updated_By,

it.Item_Type_Desc,

Opening_Balance = ISNULL(vs.Opening_Balance,0),

v.Venue_Name

FROM m_Venue v

CROSS JOIN m_Items AS Items -- every venue has every item

INNER JOIN m_Item_Type it

ON Items.Item_Type = it.Item_Type_ID

LEFT JOIN s_Venue_Stock vs

ON vs.Venue = v.Venue_Code

AND vs.Item = Items.Item_ID

--AND vs = 1 -- what table is this column from?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lokesh Vij SSChampion Points: 10836 More actions · Answer 5

ChrisM@Work (9/4/2012)
Having read the spec properly - thanks Lokesh Vij - here's attempt #2:

You are welcome Chris 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

cmc_dermo SSC Enthusiast Points: 199 More actions · Answer 6

A big thank you to everyone who has put some time in to help me, thank you very much.

Ok i have run the query and it seems to be ignoring the last part of the query, i am passing the vs.venue value so that it looks for only opening balances for items linked to a specific venue, but the query is ignoring the last part of the query and returns all the venues.

SELECT

Items.Item_Desc,

Items.Updated_By,

it.Item_Type_Desc,

Opening_Balance = ISNULL(vs.Opening_Balance,0),

v.Venue_Name

FROM m_Venue v

CROSS JOIN m_Items AS Items -- every venue has every item

INNER JOIN m_Item_Type it

ON Items.Item_Type = it.Item_Type_ID

LEFT JOIN s_Venue_Stock vs

ON vs.Venue = v.Venue_Code

AND vs.Item = Items.Item_ID

AND vs.Venue = 1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

SELECT

Items.Item_Desc,

Items.Updated_By,

it.Item_Type_Desc,

Opening_Balance = ISNULL(vs.Opening_Balance,0),

v.Venue_Name

FROM m_Venue v

CROSS JOIN m_Items AS Items -- every venue has every item

INNER JOIN m_Item_Type it

ON Items.Item_Type = it.Item_Type_ID

LEFT JOIN s_Venue_Stock vs

ON vs.Venue = v.Venue_Code

AND vs.Item = Items.Item_ID

WHERE v.Venue_Code = 1

Remember - referencing a column from a LEFT-joined table in the WHERE clause converts the join to INNER, except where the comparison is to NULL.

Edit: clarification.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

cmc_dermo SSC Enthusiast Points: 199 More actions · Answer 8

cmc_dermo

SSC Enthusiast

Points: 199

September 4, 2012 at 4:52 am

#1533195

ok understood, so how do i change it to work?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Chris, are you sure about that? Referencing a column from the outer table will turn an outer join into an inner join if the reference is in a WHERE clause, but if you put the reference in the join predicate, the outer join is preserved.

John

Edit - edited the wrong post

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

cmc_dermo (9/4/2012)
ok understood, so how do i change it to work?

I think you need to change the LEFT JOIN to an INNER JOIN, but without DDL, sample data and expected results, we can't be sure. Please see the link posted by Sumit earlier on.

John

cmc_dermo SSC Enthusiast Points: 199 More actions · Answer 11

now i am getting confused 🙂

Maybe i need to explain better?

I need all the items from the items table but i would also like the query to look in the stock table and see if an opening_balance exists for that item, if not it should still show the item but with a zero value.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

John Mitchell-245523 (9/4/2012)
Chris, are you sure about that? Referencing a column from the outer table will turn an outer join into an inner join if the reference is in a WHERE clause, but if you put the reference in the join predicate, the outer join is preserved.
John
Edit - edited the wrong post

Thanks John, I've edited the post to make it clear.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

cmc_dermo (9/4/2012)
now i am getting confused 🙂
Maybe i need to explain better?
I need all the items from the items table but i would also like the query to look in the stock table and see if an opening_balance exists for that item, if not it should still show the item but with a zero value.

Did you try this:

SELECT

Items.Item_Desc,

Items.Updated_By,

it.Item_Type_Desc,

Opening_Balance = ISNULL(vs.Opening_Balance,0),

v.Venue_Name

FROM m_Venue v

CROSS JOIN m_Items AS Items -- every venue has every item

INNER JOIN m_Item_Type it

ON Items.Item_Type = it.Item_Type_ID

LEFT JOIN s_Venue_Stock vs

ON vs.Venue = v.Venue_Code

AND vs.Item = Items.Item_ID

WHERE v.Venue_Code = 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

cmc_dermo SSC Enthusiast Points: 199 More actions · Answer 14

ok that works but i want to add week as well (sorry guys) as soon as i add vs.week = 36 it doesn't work.