SQL rewrite and re-explained - help?

[samearle]

I need to write a SQL statement that essentially joins two tables.
"Users" and "UsersInGroup".

Users has a one to many relationship with UsersInGroup.

But I want the query to return only one record for each record in
Users, with all instances of that record in UsersInGroup - the many
relationship - to be concatinated onto the single querry record.

Right now I can get all the data:

SELECT Users.*,
UserInGroup.GroupID
FROM Users
INNER JOIN UserInGroup
ON Users.UserID = UserInGroup.UserID;

but for each new GroupID for each UserID it adds a new record. I'd like
it to only return one record per UserID, with all GroupID's added to
that user.

Tried this, but still can't get it:

SELECT u.UserID,
DMax("[GroupID]"," UserInGroup","GroupID = 1") AS group_1,
DMax("[GroupID]"," UserInGroup","GroupID = 2") AS group_2,
DMax("[GroupID]"," UserInGroup","GroupID = 3") AS group_3
FROM Users AS u, UserInGroup AS g
WHERE (((u.UserID)=[g].[UserID]) AND ((g.GroupID) In (1,2,3)))
GROUP BY u.UserID;

Thanks for the help.

Sam

 

[John Spencer]

You might post a sample of what you expect to be returned from a small
sample of records.

Do you want the UsersInGroup to consist of one field that contains all the
group names in a concatenated list,

or do you want to have several fields each holding a group name,

or would you settle for the groups being listed as a column name with a mark
in the field if the user is a member of the group (a crosstab query)?

 

[Phil]

I do not quite understand what you want to do.

Give a few examples of what your two tables might contain,

then an example of what you want your final query to return.

 

[samearle]

Thakns so much for responding John.

I'd like to have have several fields each holding a group name, up to 9
groups for each user.

Here is a sample of how the data is returned now:

UserID FName LName Email GroupID
30 John Smith (e-mail address removed) company All
30 John Smith (e-mail address removed) company NY
30 John Smith (e-mail address removed) company returns
30 John Smith (e-mail address removed) Communications
30 John Smith (e-mail address removed) Conference Call
36 Christopher Jones (e-mail address removed) company PMs
36 Christopher Jones (e-mail address removed) company All
36 Christopher Jones (e-mail address removed) company NY

And here is how I would like it returned:

UserID FName LName Email GroupID 1 GroupID 2 GroupID 3 GroupID
4 GroupID 5
30 John Smith (e-mail address removed) company All company NY company
returns Communications Conference Call
36 Christopher Jones (e-mail address removed) company PMs company
All company NY

If you can see that alright. One record returned per UserID with
several extra fields for each GroupID that person belogs to.

Thanks so much for the help!!!!

Sam

 

[Phil]

You adressed John, but replied to me. John might be the better guy for
this one, but help is help. Something that is not clear to me. Is
Group 1 anything but an on the fly construct? In other words, If Johh
Smith attaches to CompanyAll, under GroupID 1, WHY does he? Is
CompanyALL seperately referenced as Group 1, is it marked as Group 1 one
in the table that links CompanyAll to John Smith, or is Group1 simply
the 1st Match found for John smith, Group2 the second Match found, etc?

I think this last one is the case. If so, My own approach would be 2 steps.

First, take the query you have that returns one record per company,
suitably sorted,and make it a ranking query. This will put a sequential
number to each company per user. Search through this forum for RANK or
RANKING. Someone helped me out with this a couple months back, might
even even have been John. This way, each Company has a sequential
number attached, which resets at 1 for each new user.

Then build a crosstab query on that query, using the Ranking to get
generate your coloum headings, Group1, Group2, etc., with the Company
Name as the value of the crosstab.

 

[John Spencer]

That is a good description of the process needed. There may be another
solution out there, but this is the one I would try.

 

[samearle]

John and Phil - Thanks so much for your help. I beleive we are almost
there. Hope I can return the favor someday.

Yes, Phil, Group1 simply the 1st GroupID found for John smith, Group2
the second GroupID found, etc...

I'm not sure about adding this extra step of ranking??? To Elaborate:

"take the query you have that returns one record per company," I assume
you mean the querry that returns all the GroupID's? and Rank the
GroupID's? Then somehow match them up? I beleive this adds an
additional step and still does not get us to our goal.

That goal being = 1 record returned from querry per UserID with its
GroupID's in additional columns (either null or with value as needed).
Ergo - trying to match each USerID up with all of its GroupID's and
returned in one record.

Here is the SQL I've so far: Maybe we can keep trying.

Thanks,

Sam

SELECT Users.*, group_1, group_2, group_3
from
(select g.UserID,
max(IIf(GroupID=1, GroupID, null)) as group_1,
max(IIf(GroupID=2, GroupID, null)) as group_2,
max(IIf(GroupID=2, GroupID, null)) as group_3
from Users u INNER JOIN UserInGroup g ON u.UserID = g.UserID
where u.GroupID in (1,2,3)
group by u.UserID
) AS A
INNER JOIN Users ON A.UserID = Users.UserID

 

[Phil]

You have this.
UserID FName LName Email GroupID
30 John Smith (e-mail address removed) company All
30 John Smith (e-mail address removed) company NY
30 John Smith (e-mail address removed) company returns
30 John Smith (e-mail address removed) Communications
30 John Smith (e-mail address removed) Conference Call
36 Chris Jones (e-mail address removed) company PMs
36 Chris Jones (e-mail address removed) company All
36 Chris Jones (e-mail address removed) company NY

OK. This is what you have now for your base query. Turn it into a
ranking query, and this will become
RANK UserID FName LName Email GroupID
1 30 John Smith (e-mail address removed) company All
2 30 John Smith (e-mail address removed) company NY
3 30 John Smith (e-mail address removed) company returns
4 30 John Smith (e-mail address removed) Communications
5 30 John Smith (e-mail address removed) Conference Call
1 36 Chris Jones (e-mail address removed) company PMs
2 36 Chris Jones (e-mail address removed) company All
3 36 Chris Jones (e-mail address removed) company NY

Now, each UserID/GroupID combination has a number assigned to it, From 1
to X, with X being the number of GroupIds assigned to that UserID.

Based on that query, build a crosstab query. The key here will be the
COLOUM field, which would look something like

Grouping:"GroupID"&[rankingquery].[rank]

And the VALUE field, which will be your GroupID, with First or Last in
the totals row.

The result will be what you are looking for.


I went to look up the ranking thread for you, and it has "expired."
So here is the lowdown. It took me a little bit to figure it out, and I
made a really simple query to teach myself the concept.

SELECT *
FROM R1
ORDER BY r1.Warehouse, r1.Type,r1.onhand_qty DESC;

This is a basic select query. All it does is give me all TYPES of items,
with the highest QOH values first, by warehouse. I can look and see
what is teh highest, the second highest, etc., but I wanted to see what
was the highest in each warehouse, second in each warehouse, etc.

So That query becomes

SELECT (SELECT COUNT(*)
FROM R1 Q1
WHERE Q1.Warehouse = Q.Warehouse AND (Q1.Type < Q.Type OR
(Q1.Type = Q.Type AND Q1.onhand_qty > Q.onhand_qty))) AS Rank, *
FROM R1 AS Q
ORDER BY Warehouse, Type, onhand_qty DESC;


The Second inside select query threw me a bit. What it is doing is
counting as it goes, and returning the count as a field called RANK. It
runs as long as the WHERE clause is true. When it fails, it exits the
internal select clause, which will be run again when the next record
from the external select clause is processed, but Rank is reset.

So basicly, The inside query runs the same basic query as the outside
query, a full run for each record processed by the outside query,
counting up the number of records, until it the inside query reaches the
same record as the outside query, at which point the WHERE clause fails,
and it exits, with the count as RANK.
The next OUTSIDE record processed will allow the inside query to count
for one more record.

It had a couple of problems that I do not think you will run into with
your data set.

The only thing I can think of to be careful of, is to make sure that
each combination of UserID/GroupID is unique. IF the same person is a
member of the same company more that once, it may mess things up a bit.
A unique query at the front end woudl fix taht.

Good luck