Combing 2 Queries into 1

I have spent several hours hurting my brain tring to solve the following. I am hoping one of you SQL gurus can help me out.

I have two tables (each with two fields):

Group with GroupName and GroupDescription

Here is some sample data:

HR HumanResources
IT Information Technology
Boston BostonOffice
NJ NewJerseyOffice

GroupMember with GroupName and UserID

Here is some sample data:

IT CMessineo
NJ CMessineo
Boston JSmith
IT JSmith

What I want is a single stored procedure that when passed a UserID will return a result set that lists all the groups and a 1 or ) if the UserID is a member of that group.

For example calling the procedure and passing CMessineo would produce this result:

HR 0
IT 1
Boston 0
NJ 1

Can this be done in a stored procedure?

This is what I have so far, but I am in over my head:

(
@.UserID varChar(40)
)
As
SELECT"GROUP".GROUPNAME, (SELECT ?
FROM"Group", GroupMember
Where"Group".GroupName = GroupMember.GroupName and GroupMember.UserID = @.UserID)

FROM"GROUP"

Thanks in advance,

ChrisI figured it out - it required an outer join (my first):

SELECT"Group".GroupName, COUNT(UserID) AS MEMBER
FROM"Group" Left Outer Join GroupMember
ON"Group".GroupName = GroupMember.GroupName and GroupMember.UserID=@.UserID
GROUP BY"Group".GroupName

Column based query

Hello all,
This one evades but I don't think it's possible. I thought I would
send it out the brain trust.
I have 4 columns that are bits. Is it possible to make a query that
will return the columns where the column is true?
Here is an example:
Col1 Col2 Col3 Col4
t t f f
t f f t
Is it possible to return two separate resultsets that gives me col1,
col in the first query
col 1 and col4 in the second?"axwack" <axwack@.gmail.com> wrote in message
news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
> Hello all,
> This one evades but I don't think it's possible. I thought I would
> send it out the brain trust.
> I have 4 columns that are bits. Is it possible to make a query that
> will return the columns where the column is true?
> Here is an example:
> Col1 Col2 Col3 Col4
> t t f f
> t f f t
> Is it possible to return two separate resultsets that gives me col1,
> col in the first query
> col 1 and col4 in the second?
Not sure exactly what you're asking for here, but based on my initial
reading it sounds like you want the names of the columns returned. Does
this do what you want? Note that I'll leave all the naming convention and
ANSI (nee ISO) quibbles for Celko to take up with you:
CREATE TABLE #bits (col1 bit,
col2 bit,
col3 bit,
col4 bit);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 1, 0, 0);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 0, 0, 1);
SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
FROM #bits;
DROP TABLE #bits;|||On Dec 24, 10:41=A0pm, "Mike C#" <x...@.xyz.com> wrote:
> "axwack" <axw...@.gmail.com> wrote in message
> news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
>
>
>
>
>
>
>
> Not sure exactly what you're asking for here, but based on my initial
> reading it sounds like you want the names of the columns returned. =A0Does=[/vbcol
]
[vbcol=seagreen]
> this do what you want? =A0Note that I'll leave all the naming convention a=[/vbcol
]
nd[vbcol=seagreen]
> ANSI (nee ISO) quibbles for Celko to take up with you:
> CREATE TABLE #bits (col1 bit,
> =A0 col2 bit,
> =A0 col3 bit,
> =A0 col4 bit);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 1, 0, 0);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 0, 0, 1);
> SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
> =A0 CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
> =A0 CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
> =A0 CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
> FROM #bits;
> DROP TABLE #bits;
Hi that will work...I thought you could get the database to return the
coumns but this actually suits my needs because it is for gui
generation.|||"axwack" <axwack@.gmail.com> wrote in message
news:c715a12b-8b96-44d5-b14a-f489e774bc48@.j20g2000hsi.googlegroups.com...
> Hi that will work...I thought you could get the database to return the
> coumns but this actually suits my needs because it is for gui
> generation.
Not sure what you mean by "get the database to return the columns", but I
have a strong feeling that you're working here with a table that's not
properly normalized. I'm still not 100% sure on what you're trying to
accomplish, but if you're trying to do what I believe you are try looking at
normalization... you may find a better way to accomplish these tasks.

Column based query

Hello all,
This one evades but I don't think it's possible. I thought I would
send it out the brain trust.
I have 4 columns that are bits. Is it possible to make a query that
will return the columns where the column is true?
Here is an example:
Col1 Col2 Col3 Col4
t t f f
t f f t
Is it possible to return two separate resultsets that gives me col1,
col in the first query
col 1 and col4 in the second?
"axwack" <axwack@.gmail.com> wrote in message
news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
> Hello all,
> This one evades but I don't think it's possible. I thought I would
> send it out the brain trust.
> I have 4 columns that are bits. Is it possible to make a query that
> will return the columns where the column is true?
> Here is an example:
> Col1 Col2 Col3 Col4
> t t f f
> t f f t
> Is it possible to return two separate resultsets that gives me col1,
> col in the first query
> col 1 and col4 in the second?
Not sure exactly what you're asking for here, but based on my initial
reading it sounds like you want the names of the columns returned. Does
this do what you want? Note that I'll leave all the naming convention and
ANSI (nee ISO) quibbles for Celko to take up with you:
CREATE TABLE #bits (col1 bit,
col2 bit,
col3 bit,
col4 bit);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 1, 0, 0);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 0, 0, 1);
SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
FROM #bits;
DROP TABLE #bits;
|||On Dec 24, 10:41Xpm, "Mike C#" <x...@.xyz.com> wrote:
> "axwack" <axw...@.gmail.com> wrote in message
> news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
>
>
>
>
> Not sure exactly what you're asking for here, but based on my initial
> reading it sounds like you want the names of the columns returned. XDoes
> this do what you want? XNote that I'll leave all the naming convention and
> ANSI (nee ISO) quibbles for Celko to take up with you:
> CREATE TABLE #bits (col1 bit,
> X col2 bit,
> X col3 bit,
> X col4 bit);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 1, 0, 0);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 0, 0, 1);
> SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
> X CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
> X CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
> X CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
> FROM #bits;
> DROP TABLE #bits;
Hi that will work...I thought you could get the database to return the
coumns but this actually suits my needs because it is for gui
generation.
|||"axwack" <axwack@.gmail.com> wrote in message
news:c715a12b-8b96-44d5-b14a-f489e774bc48@.j20g2000hsi.googlegroups.com...
> Hi that will work...I thought you could get the database to return the
> coumns but this actually suits my needs because it is for gui
> generation.
Not sure what you mean by "get the database to return the columns", but I
have a strong feeling that you're working here with a table that's not
properly normalized. I'm still not 100% sure on what you're trying to
accomplish, but if you're trying to do what I believe you are try looking at
normalization... you may find a better way to accomplish these tasks.

Column based query

Hello all,
This one evades but I don't think it's possible. I thought I would
send it out the brain trust.
I have 4 columns that are bits. Is it possible to make a query that
will return the columns where the column is true?
Here is an example:
Col1 Col2 Col3 Col4
t t f f
t f f t
Is it possible to return two separate resultsets that gives me col1,
col in the first query
col 1 and col4 in the second?"axwack" <axwack@.gmail.com> wrote in message
news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
> Hello all,
> This one evades but I don't think it's possible. I thought I would
> send it out the brain trust.
> I have 4 columns that are bits. Is it possible to make a query that
> will return the columns where the column is true?
> Here is an example:
> Col1 Col2 Col3 Col4
> t t f f
> t f f t
> Is it possible to return two separate resultsets that gives me col1,
> col in the first query
> col 1 and col4 in the second?
Not sure exactly what you're asking for here, but based on my initial
reading it sounds like you want the names of the columns returned. Does
this do what you want? Note that I'll leave all the naming convention and
ANSI (nee ISO) quibbles for Celko to take up with you:
CREATE TABLE #bits (col1 bit,
col2 bit,
col3 bit,
col4 bit);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 1, 0, 0);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 0, 0, 1);
SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
FROM #bits;
DROP TABLE #bits;|||On Dec 24, 10:41=A0pm, "Mike C#" <x...@.xyz.com> wrote:
> "axwack" <axw...@.gmail.com> wrote in message
> news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
>
> > Hello all,
> > This one evades but I don't think it's possible. I thought I would
> > send it out the brain trust.
> > I have 4 columns that are bits. Is it possible to make a query that
> > will return the columns where the column is true?
> > Here is an example:
> > Col1 =A0 =A0Col2 =A0 Col3 Col4
> > t =A0 =A0 =A0 =A0 =A0 =A0t =A0 =A0 =A0 =A0 =A0 =A0f =A0 =A0 =A0 f
> > t =A0 =A0 =A0 =A0 =A0 =A0f =A0 =A0 =A0 =A0 =A0 =A0f =A0 =A0 =A0 t
> > Is it possible to return two separate resultsets that gives me col1,
> > col in the first query
> > col 1 and col4 in the second?
> Not sure exactly what you're asking for here, but based on my initial
> reading it sounds like you want the names of the columns returned. =A0Does=
> this do what you want? =A0Note that I'll leave all the naming convention a=nd
> ANSI (nee ISO) quibbles for Celko to take up with you:
> CREATE TABLE #bits (col1 bit,
> =A0 col2 bit,
> =A0 col3 bit,
> =A0 col4 bit);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 1, 0, 0);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 0, 0, 1);
> SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
> =A0 CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
> =A0 CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
> =A0 CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
> FROM #bits;
> DROP TABLE #bits;
Hi that will work...I thought you could get the database to return the
coumns but this actually suits my needs because it is for gui
generation.|||"axwack" <axwack@.gmail.com> wrote in message
news:c715a12b-8b96-44d5-b14a-f489e774bc48@.j20g2000hsi.googlegroups.com...
> Hi that will work...I thought you could get the database to return the
> coumns but this actually suits my needs because it is for gui
> generation.
Not sure what you mean by "get the database to return the columns", but I
have a strong feeling that you're working here with a table that's not
properly normalized. I'm still not 100% sure on what you're trying to
accomplish, but if you're trying to do what I believe you are try looking at
normalization... you may find a better way to accomplish these tasks.