CODEWITHSUNDEEP
sqlbit-manipulation
subrama6
subrama6

Reputation: 517

SQL Logical AND operator for bit fields

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?

Upvotes: 0

Views: 1907

Answers (7)

Dylan
Dylan

Reputation: 2432

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

Upvotes: 0

codemonkey
codemonkey

Reputation: 2665

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

Upvotes: 0

DevinB
DevinB

Reputation: 8316

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

Upvotes: 0

subrama6
subrama6

Reputation: 517

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)

Upvotes: 1

Miles D
Miles D

Reputation: 8040

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.

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135011

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

Upvotes: 1

devio
devio

Reputation: 37215

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.

Upvotes: 0

Related Questions