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

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 2

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 3

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)

• Dylan: Just an FYI, if your db supports the EXISTS clause, it's generally faster due to it's better use of indexes. See my post below.

Answer 4

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 5

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 6

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 7

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