Joining Issues - I think (NEWBIE)

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 2005 Forums

Transact-SQL (2005)

Joining Issues - I think (NEWBIE)

Author

Topic

pvong
Yak Posting Veteran

58 Posts

Posted - 2012-03-06 : 15:57:05

Sorry for the simple question but I'm trying to learn. Here is an image of my table.

Here is my Select Statment

SELECT Clients.Autex, Clients.AcctName, AxysPals.Quantity
FROM AxysPals INNER JOIN
Clients ON AxysPals.Autex = Clients.Autex
WHERE (AxysPals.Ticker = 'XYZ')
GROUP BY Clients.Autex, Clients.AcctName, AxysPals.Quantity
HAVING (Clients.Autex = '6581') OR
(Clients.Autex = '6570') OR
(Clients.Autex = '6545') OR
(Clients.Autex = '6550')

What I'm trying to do is get a result where it will show me all four of the Autex and AcctName even if the qty is 0 where Ticker does not exist for XYZ. Something like below. In the below example, 6545 and 6550 does not own any XYZ in the AxysPal table but exist in the Clients table.

Autex Name Qty
6581 Acct1 100
6570 Acct2 200
6545 Acct3 <Blank because it does not have XYZ>
6550 Acct4 <Blank because it does not have XYZ>

I think there's something wrong with my join but I'm not sure. When I run this, I just get

Autex Name Qty
6581 Acct1 100
6570 Acct2 200

Thanks!
Phil

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 16:35:55

You do realize that doesn't make much sense

I want other than XYZ to be 0

but where it is equal to XYZ

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

pvong
Yak Posting Veteran

58 Posts

Posted - 2012-03-06 : 16:48:57

Sorry for the confusion.

6545 and 6550 does not have any XYZ in the AxysPals table but I still want them to show up in the result because they exist in the Clients table but I want the QTY to be blank for those two accounts.

I updated my original to try and clear it up a little more.

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

pvong
Yak Posting Veteran

58 Posts

Posted - 2012-03-08 : 10:32:39

ANYONE???

Please help a newbie out. I'm trying to learn.

Thanks!

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 10:36:44

quote:
Originally posted by pvong

ANYONE???

Please help a newbie out. I'm trying to learn.

Thanks!

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.


SELECT DISTINCT Clients.Autex, Clients.AcctName, AxysPals.Quantity
FROM Clients 
LEFT JOIN AxysPals 
ON AxysPals.Autex = Clients.Autex
AND (AxysPals.Ticker = 'XYZ')
WHERE (Clients.Autex IN ('6581','6570','6545','6550')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pvong
Yak Posting Veteran

58 Posts

Posted - 2012-03-08 : 11:14:09

visakh16

Thanks!! This worked.

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 11:45:15

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources