Unexpected results when I Join, use View or Cross Apply - mysterious

Question

Unexpected results when I Join, use View or Cross Apply - mysterious

brosspaxedi

SSCommitted

Points: 1591
More actions
June 21, 2012 at 6:34 am

#392574

If I do a simple select from my table independently, I get the correct number (I confirmed this outside of Sql Server).
If I do a Join, a View or a Cross Apply, I get the wrong number. (I'm not unexperienced with TSQL). What am I missing? Here's a picture, reduced to bare bones simplicity:
https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png
I've done the same with a CTE, View and even a Cursor, and I always get 14. The only time I get 5, the correct answer, is in a straight Select.
Thanks.
*******************
What I lack in youth, I make up for in immaturity!
June 21, 2012 at 7:26 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

drew.allen SSC Guru Points: 76990 More actions · Answer 1

First, I can't access that link. Second, a picture is not a useful way to post code, because we can't copy and paste it into SSMS to test it. Check out the Forum Etiquette[/url] to see the best way to post data and code.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

brosspaxedi SSCommitted Points: 1591 More actions · Answer 2

Thank you both for your responses.

I created a test with fake data, but since it works correctly, so it isn't much good:

CREATE TABLE [dbo].[t1](

[provider_id] [varchar](15) NULL,

[Total Members] [int] NULL

) ON [PRIMARY]

GO

INSERT INTO t1

VALUES

('000125651',5)

USE [pec_prod]

GO

CREATE TABLE [dbo].[t2](

[pcp_id] [varchar](15) NOT NULL,

[Total Members] [int] NULL

) ON [PRIMARY]

GO

INSERT INTO t2

VALUES('000198899',15);

INSERT INTO t2

VALUES('000125651',5);

INSERT INTO t2

VALUES('000199999',50);

SELECT DISTINCT

t1.provider_id ,

t2.pcp_id,

t1.[Total Members]

from dbo.t2 AS t2

INNER JOIN dbo.t1 AS t1

ON t1.provider_id = t2.pcp_id

DROP TABLE [dbo].[t1]

GO

DROP TABLE [dbo].[t2]

GO

I tried to show the table contents by the query results before the final output, then showed the output.

I'm not sure why you couldn't open the image. It is a .png, which might not be supported by your browser.

*******************
What I lack in youth, I make up for in immaturity!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I had to zoom in on the picture which made it harder to see (blurry), but from what I could make out, your sample code and data doesn't match what you showed in the picture.

drew.allen SSC Guru Points: 76990 More actions · Answer 4

brosspaxedi (6/21/2012)
I'm not sure why you couldn't open the image. It is a .png, which might not be supported by your browser.

The site is blocked by my company.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

brosspaxedi SSCommitted Points: 1591 More actions · Answer 5

I'll have to just report back when I find the answer, I think.

Thanks.

*******************
What I lack in youth, I make up for in immaturity!

brosspaxedi SSCommitted Points: 1591 More actions · Answer 6

Okay, false alarm - it is an orderly universe after all...

The 14 was the correct number after all! It was a case of not having my expected results correctly documented.

Moderator, it is fine with me if you delete this thread... txtPost_CommentEmoticon(':hehe:');

Have a great day.

*******************
What I lack in youth, I make up for in immaturity!