Outer join not working correctly

Question

Outer join not working correctly

Viewing 5 posts - 16 through 19 (of 19 total)

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

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

cmc_dermo (9/4/2012)
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.

It has to be part of the join condition, as I pointed out earlier. If you put it into the WHERE clause, you convert

to an inner join:

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.[week] = 36

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 2

believe it or not i tried to work it out and got it working, i came here to post the result but you beat me to it.................but many thanks to all involved.

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.Week = 36

WHERE v.Venue_Code = 2

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

cmc_dermo (9/4/2012)
believe it or not i tried to work it out and got it working...

That's exactly the result that everybody here hopes to see 😀

If there's anything you're unsure about, post back.

^{“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 4

id really like to understand what the cross join does, but if it is too much time to describe i will go look it up 🙂

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

cmc_dermo (9/4/2012)
id really like to understand what the cross join does, but if it is too much time to describe i will go look it up 🙂

Every row on the left hand side gets every row from the right hand side:

CREATE TABLE #lhs (lhscolumn CHAR(1))

INSERT INTO #lhs (lhscolumn) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'

CREATE TABLE #rhs (rhscolumn INT)

INSERT INTO #rhs (rhscolumn) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

SELECT * FROM #lhs

SELECT * FROM #rhs

SELECT * FROM #lhs CROSS JOIN #rhs

^{“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