A Left Join in Need of a Column 2

[Genimuse]

It's weird how I can cruise along, writing dozens of queries, and suddenly find myself stumped by something that I'm nearly certain is straightforward, but some kind of block in my brain is in the way. I appreciate very much your help.

4 tables (that matter here):

[b]Section[/b]
ID [i](PK)[/i]
Name

[b]Feature[/b]
ID [i](PK)[/i]
Name
SectionID [i](FK to Section table, ID column)[/i]

[b]User[/b]
ID [i](PK)[/i]
Name

[b]UserFeature[/b] [i]("joining" table)[/i]
UserID [i](FK to User table, ID column)[/i]
FeatureID [i](FK to Feature table, ID column)[/i]
Icon

My goal is to create a query that include, basically, the whole combination of Feature and Section, and then for a given user, a column that indicates to me whether there's a linking between User and Feature for that table. So, for example, here's some example data:

[b]Section[/b]
[u]ID[/u]  [u]Name[/u]
 1  Apps
 2  Depts

[b]Feature[/b]
[u]ID[/u]  [u]Name[/u]        [u]SectionID[/u]
 1  Calendar        1
 2  Search          1
 3  Accounting      2
 4  HR              2
 5  Sales           2

[b]User[/b]
[u]ID[/u]  [u]Name[/u]
 1  Bob
 2  Jan

[b]UserFeature[/b]
[u]UserID[/u]  [u]FeatureID[/u]  [u]Icon [/u]
   1        1      True
   1        2      True
   1        4      False

So, in this case, we'd want all of the Features to be returned along with their section name, and and indicator whether a given user had that feature associated (and whether Icon is true or false).

The result of the query, if I specify UserID = 1, would look something like this:

[u]Section.Name[/u]  [u]Feature.Name[/u]  [u]UserLinked[/u]  [u]UserFeature.Icon[/u]
    Apps       Calendar        True           True
    Apps       Search          True           True
    Depts      Accounting      False
    Depts      HR              True           False
    Depts      Sales           False

"UserLinked" is a made-up field that indicates whether there's a matching UserFeature record for a given User and Feature. I'd guess that UserFeature.Icon would need to always be "True" or "False" (as a Yes/No field), and it's fine if it just defaults to False instead of Null.

So... I've easily managed the list of all Features with their sections (simply inner join), but when I try to add in the UserFeature field without specifying "Icon" or anything else in the columns I request, it compresses the results so that I only get the columns where all 3 have something (even with a LEFT join). If I add anything from the UserFeature table to the columns I want returned (like UserFeature.Icon), it just blows on syntax. And none of this even touches the need for a true/false added "UserLink" field.

Sorry, that was a lot. Any clues on where to head with this?

 

[GingerR]

Since you want to show all combinations of USERS + FEATURES, you have to first make a query using those two tables, with NO joins, resulting in a table that has every possible combination of the two:

UserID   FeatureID
1      1
1      2
1      3
1      4
1      5
2      1
2      2
2      3
2      4
2      5

Then you can either put this query in the query you already wrote, or make a third query with this new one and your existing one in it, with the join arrows starting at the new combo query UserID and FeatureID fields. Bring down the UserID and FeatureID fields from the new combo query. That way every combination of UserID and FeatureID will be the result. Just filter this final query for the desired UserID.


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Genimuse]

Hey Ginger, thanks so much for the pointer. I'd guess you're suggesting the full User + Feature table in order to be able to apply the filter, but I need to do everything via SQL, and can't add a table for this Select.

I've been working on the SQL for the last many hours and finally came up with what I needed, with a parameter for the UserID. (I need SQL in the end because I'm calling it from an outside app.)

I know, however, that this is VERY UGLY. If anyone can suggest a cleaner version, I'd surely appreciate it:

PARAMETERS ThisUser Long;
SELECT 
    Section.Name, 
    Feature.Name, 
    (IIf([Feature.ID] In (SELECT FeatureID FROM UserFeature WHERE UserID = ThisUser),
        True,
        False
    )) AS UserLink,
    (IIf([Feature.ID] In (SELECT FeatureID FROM UserFeature WHERE UserID = ThisUser),
        (SELECT Icon From UserFeature WHERE UserID = ThisUser AND FeatureID = Feature.ID),
        False
    )) AS Icon
FROM [Section] INNER JOIN Feature ON Section.ID = Feature.SectionID

3 little subqueries = yuck.

 

[GingerR]

It wasn't adding a table. It was adding a query based on your two tables Users and features. "adding a query" = writing it in SQL. Same thing....Your query is fine. Mine's the same, but not as cool--I wrote a query based on a second query.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Genimuse]

Ah, I see, I misread it. Thanks very much!

 

[PHV]

And what about this ?
PARAMETERS ThisUser Long;
SELECT Section.Name, Feature.Name
, (UF.UserID Is Not Null) AS UserLink
, UF.Icon
FROM [Section] INNER JOIN (Feature LEFT JOIN (
SELECT FeatureID, UserID, Icon FROM UserFeature WHERE UserID = ThisUser
) AS UF ON Feature.ID = UF.FeatureID) ON Section.ID = Feature.SectionID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Genimuse]

Awesome, PHV, much better and more efficient. Thanks very much.