SQL two Tables Join Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL two Tables Join Problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-16 : 08:08:55

Teknosoft writes "I'm Usin MS SQL 7.0

I have two Tables Items, Item_User

I'm Using The follwing Statement

SELECT dbo.Items.ItemCode, dbo.Items.ItemDesc,
dbo.Items.ItemNotes, dbo.Items_User.UserCode
FROM dbo.Items LEFT OUTER JOIN
dbo.Items_User ON
dbo.Items.ItemCode = dbo.Items_User.ItemCode

I need to list All Items from Items.Table and filling Items_User.UserCode for a spesific user

'Note if I add
WHERE (dbo.Items_User.UserCode = N'10')
it will not list all records from Items Table!

to visualize this more please visit
http://www.globalnetusa.com/sql/sql_help.htm

I could get that done by creating Views that will filter UserCode from Item_User. But I'm trying to see if this could be done in one single statement"

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-16 : 08:29:58

It'll read easier if you use an Alias in your syntax like this:

SELECT A.ItemCode, A.ItemDesc, A.ItemNotes, B.UserCode

FROM dbo.Items A

LEFT OUTER JOIN dbo.Items_User B ON A.ItemCode = B.ItemCode

WHERE B.UserCode = N'10'

--
I've got to run. I've reviewed your example post very quickly and following the logic would take more time than I have. I do see that you're using Enterprise Manager.

Try this query using Query Analyzer. You might get better behavior. It's the tool of choice for query construction.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-01-16 : 08:36:07

A common misunderstanding. When you use a LEFT OUTER JOIN, applying a WHERE condition on the right side table (the "insignificant" table) will cause the query to run as an INNER JOIN not an OUTER JOIN. To work around this problem, specify the condition as part of the JOIN:

SELECT A.col1, A.col2, B.col1 FROM
A LEFT OUTER JOIN B
ON A.col1 = B.col1
AND B.someCol = 10

OS

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-01-18 : 18:28:24

mohdowais is exactly right on this. For a more detailed explanation, see my article on this subject at http://www.sqlteam.com/item.asp?ItemID=11122

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources