Return 4 rows, not 11 rows. - 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 2008 Forums

Transact-SQL (2008)

Return 4 rows, not 11 rows.

Author

Topic

ugh3012
Yak Posting Veteran

62 Posts

Posted - 2013-07-25 : 13:11:34

I have two tables. Table A has 4 rows. Table C has 11 rows and I only need one field from table C.


My query looks something like this like this. 
SELECT 
A.field1
,A.field2
,C.field24
FROM Table A
inner join Table C
on A.field1 = C.field3
and C.field.StartDate Between A.EffDate and ISnull(A.TermDate , CAST('12/31/2078 23:59:59' as Datetime))
Where C.field24 not in (‘531’, …)

It returns 11 rows as there are 11 rows in Table C. I need the query to ignore all other rows and only get infomation from field24 as they are the same and put them into 4 rows. I also need the query not to include any rows from table A based on this “Where C.field24 not in (‘531’, …).”

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-25 : 13:20:21

Try something like this. This would give you one row per combination of field1 and field2, but whether the value of field24 that you get is the right one or not - I don't know

SELECT 
A.field1
,A.field2
,MAX(C.field24) AS field24
FROM Table A
inner join Table C
on A.field1 = C.field3
and C.field.StartDate Between A.EffDate and ISnull(A.TermDate , CAST('12/31/2078 23:59:59' as Datetime))
Where C.field24 not in ('531', ...)
GROUP BY 
	A.field1
	,A.field2

ugh3012
Yak Posting Veteran

62 Posts

Posted - 2013-07-25 : 13:27:30

No joy :(

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-07-25 : 14:48:27

Not knowing the values of field24 have you tried DISTINCT?

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources