Nasty Join - 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)

Nasty Join

Author

Topic

Peckb
Starting Member

1 Post

Posted - 2006-04-13 : 08:12:16

I would like to have a SELECT Statement which does the following:

I got one product, many links are owned by one product.
A user owns several shops, one links is always associated to one shop.

My query lists everything 3 times when I got text filled into the link field - which is wrong.

All I want is, always show all shops for the product even if no link exists.

That's my query atm.:
"SELECT shops.shopName, shops.shopID, links.linkURL as link, links.shopID as update_shopID, links.linkID, links.produxID FROM shops LEFT OUTER JOIN links ON links.produxID = '"+ID+"' ";

I got those tables:

links:
linkID,
produxID,
shopID,
linkURL,
linkOwner

produx:
pID,
pName,
pHersteller,
pUser

shops:
shopID,
shopName

user_shops:
userID, shopID

and a User Table which is unimportant in thise case.

Anyone able to help? Thanks!

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-04-13 : 14:53:24

I think I understand what you are looking for, and I think you mean that you have an entry in the "Links" table, but that entry might not have a "linkURL" value. In that case you need to make a small adjustment to your statement, since you are not actually JOINING the links and shops tables. I've bolded the change you need to make:

SELECT shops.shopName, shops.shopID, links.linkURL as link, links.shopID as update_shopID, links.linkID, links.produxID
FROM links inner join shops on links.shopID = shops.shopId
where links.produxID = '"+ID+"' "

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources