September 4, 2008 at 1:53 am
Hi,
I have 2 tables 1 parent and one child.
I want to select all the rows from the parent table and for each row i want to summarize a bit column in the child table.
So, if any of the related child records contain a 1 in the bit column i would like a 1 returned otherwise a 0.
Essentially I want to find the MAX or MIN of the column but SQL Server doesn't seem to think this is a reasonable request.
I actually have 3 columns i'd like to summarize this way in the child table.
Can anyone help me?
Owen
September 4, 2008 at 1:57 am
cab u post tables structure and some sample data.
"Keep Trying"
September 4, 2008 at 2:11 am
ParentTable
------------------------------------------------------
ParentIDCol SomeDataCol
------------------------------------------------------
1 information
2 more info
ChildTable
---------------------------------------------------------
ChildIDCol SomeChildData BitCol ParentID
---------------------------------------------------------
1 chidinfo 0 1
2 somethign 0 1
3 related record 1 2
4 something important 0 2
And what i want as my output is
ResultTable
----------------------------------------------------------
ParentIDCol SomeDataCol BitColMax
----------------------------------------------------------
1 information 0
2 more info 1
So the select statment would start like:
SELECT ParentTable.*, MAX(ChildTable.BitCol) as BitColMax......
I have actually just solved the problem by casting my bit column to a smallint before finding the max but it seems like an inefficient way to do things.
Is there a better way?
September 4, 2008 at 3:34 am
select ParentTable.ParentID, ParentTable.SomeDataCol,
CASE WHEN A.Parent > 0 THEN 1 ELSE 0 END AS Bitcolumn
FROM ParentTable
LEFT OUTER JOIN (select parentid as parent from ChildTable where bitcol = 1
group by parentid) A ON ParentTable.ParentID = A.Parent
"Keep Trying"
September 4, 2008 at 3:59 am
Hi,
unfortunately there are 3 columns on my child table i want to summarize so that query would get pretty cumbersome. I think casting the bits will probably be a more effective solution for what i want.
Cheers
September 4, 2008 at 4:02 am
ie.
SELECT ParentTable.*, MAX(CAST(ChildTable.bitcol1 AS smallint)), MAX(CAST(ChildTable.bitcol2 AS smallint)), MAX(CAST(ChildTable.bitcol3 AS smallint))......
September 8, 2008 at 12:47 am
Can u post the whole query?
"Keep Trying"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy