Look up if PersonID (PID) is in a group
Hi
[EDIT - RM9 database)
Since Groups are made with start end values (for consecutive values) -- cant use normal joins or lookups?
How you check to see if Person is in a group?
Also count total people in a group?
I know how to create SQL groups
I thought I saw a post about this before but I count not find it
JOIN's work just fine for the GroupTable. Here are two very simple examples.
SELECT G.GroupID, P.*
FROM PersonTable AS P
JOIN GroupTable AS G ON G.GroupID = 2
AND P.PersonID >= G.StartID
AND P.PersonId <= G.EndID;
SELECT G.GroupID, P.*
FROM PersonTable AS P, GroupTable AS G
WHERE G.GroupID = 2
AND P.PersonID >= G.StartID
AND P.PersonId <= G.EndID;
I used GroupID = 2 in the examples because it was quick and easy and I had a group in my database with GroupID = 2. You could use GroupID's from your own database, or instead you could add a JOIN to TagTable to give the name of the group instead of the GroupID.
The second example uses a CROSS JOIN rather than any of the more typical JOIN's you are accustomed to using. Usually you are warned against using CROSS JOIN's because the result will match every row from one table with every row from the other table, producing too many rows in the result. But with judicious use of the WHERE clause, the CROSS JOIN works just fine with excellent performance. To me, the CROSS JOIN syntax seems very apt for accessing the GroupTable. But as my first example shows, you can also use the JOIN's that you know and love rather than using a CROSS JOIN.
My guess (and I could be wrong) is that under the covers the CROSS JOIN is really the only kind of JOIN that is supported by relational database engines. I suspect that all of the LEFT and RIGHT and INNER and OUTER kinds of JOIN's you can do are just CROSS JOIN's in disguise. The ON syntax rather than the WHERE syntax is usually easier for the programmer to set up. And in the case of LEFT and RIGHT, the engines default to handling the non-matching rows for you. But I think the basic underlying process used by the database engines is the same for all JOIN's. It's just that the syntax used by the programmer can be different for all the other kinds of JOIN than it is for the CROSS JOIN.
Thanks -- I need to do this with DAX and M code (not SQL per say)
Crossjoin can be done in PBI so I think I can work with that
(using Start & end dates should be basic the same as group and tag)
Uploaded files:
Forgot to give you an SQLite query for the counts of people in each group. Here is an example. It uses both an INNER JOIN and a CROSS JOIN.
I don't like this style of SQL very much because the WHERE clause for the CROSS JOIN has to be separated from the CROSS JOIN itself. The style would have been ok if I had included the TagTable in the CROSS JOIN and if I had included the TagTable criteria in the WHERE clause. Or the style would have been ok if I had not used the CROSS JOIN at all and instead had used two INNER JOIN's.
SELECT COUNT(*), T.TagName
FROM PersonTable AS P, GroupTable AS G
JOIN TagTable AS T ON TagType = 0 AND T.TagValue = G.GroupID
WHERE P.PersonID >= G.StartID AND P.PersonId <= G.EndID
GROUP BY G.GroupID
ORDER BY T.TagName
Thanks -- I can work with that query although because of the joins -- slow refresh in Power Query. -- I use dashboard to filter for Missing Citations for Find-a-grave of Relatives or spouse of relative (currently 80 people) - on 2nd tab the people are listed. Now I can see the people in the groups (and filter by also group if/when needed). The 2nd screen is also filter for starting with C
Uploaded files:
