SQL Logical AND operator for bit fields

Question

I have 2 tables that have a many to many relationship; An Individual can belong to many Groups. A Group can have many Individuals.

Individuals basically just have their Primary Key ID

Groups have a Primary Key ID, IndividualID (same as the ID in the Individual Table), and a bit flag for if that group is the primary group for the individual

In theory, all but one of the entries for any given individual in the group table should have that bit flag set to false, because every individual must have exactly 1 primary group.

I know that for my current dataset, this assumption doesn't hold true, and I have some individuals that have the primary flag for ALL their groups set to false.

I'm having trouble generating a query that will return those individuals to me.

The closest I've gotten is:

SELECT * FROM Individual i LEFT JOIN Group g ON g.IndividualID = i.ID WHERE g.IsPrimaryGroup = 0

but going further than that with SUM or MAX doesn't work, because the field is a bit field, and not a numeric.

Any suggestions?

Answer 1

You need to include the IsPrimaryGroup condition into the JOIN clause. This query finds all individuals with no PrimaryGroup set:

SELECT * FROM Individual i
LEFT OUTER JOIN Group g ON g.IndividualID = i.ID AND g.IsPrimaryGroup = 1
WHERE g.ID IS NULL

However, the ideal way to solve your problem (in terms of relational db) is to have a PrimaryGroupID in the Individual table.

Answer 2

Don't know your data...but....that LEFT JOIN is an INNER JOIN

what happens when you change the WHERE to AND

SELECT * FROM Individual i 
LEFT JOIN Group g ON g.IndividualID = i.ID 
AND g.IsPrimaryGroup = 0

Here try running this....untested of course since you didn't provide any ample data

SELECT SUM(convert(int,g.IsPrimaryGroup)), i.ID 
FROM Individual i 
LEFT JOIN [Group] g ON g.IndividualID = i.ID 
AND g.IsPrimaryGroup = 0
GROUP BY i.ID
HAVING COUNT(*) > 1

Answer 3

Try not using a bit field if you need to do SUM and MAX - use a TINYINT instead. In addition, from what I remember bit fields can not be indexed, so you will loose some performance in your joins.

Answer 4

devio - thanks for the advice, but with my data, g.ID will never be null, so i'm not sure that that's correct. the point about having a primaryGroupID in the Individual table is well taken.

SQLMenace - thanks as well - i think that gives me all individuals that have ANY entries in the group table that are not marked as primary. not individuals that have ALL entries not marked primary.

MilesD - Thanks for the advice - I may give that a shot

Answer 5

Update: Got it working with a subselect. Select IndividualID from Group where the primary group is false, and individualID NOT IN (select IndividualID from Group where primary group is true)

Answer 6

SELECT COUNT(bitflag),individualId 
FROM Groups
WHERE bitflag = 1
GROUP BY individualId
ORDER BY SUM(bitFlag)
HAVING COUNT(bitFlag) <> 1

That will give you each individual and how many primary groups they have

Answer 7

I don't know if this is optimal from a performance standpoint, but I believe something along these lines should work. I'm using OrgIndividual as the name of the resolution table between the Individal and the Group.

SELECT DISTINCT(i.IndividualID)
FROM
  Individual i INNER JOIN OrgIndividual oi
    ON i.IndividualID = oi.IndividualID AND oi.PrimaryOrg = 0
  LEFT JOIN OrgIndividual oip
    ON oi.IndividualID = oip.IndividualID AND oi.PrimaryOrg = 1
WHERE
  oi2.IndividualID IS NULL

Answer 8

SELECT IndividualID
FROM Group g
WHERE NOT EXISTS (
    SELECT NULL FROM Group
    WHERE PrimaryOrg = 1
    AND IndividualID = g.IndividualID)
GROUP BY IndividualID