We help IT Professionals succeed at work.

All I wanna know is what's in rows !!??!!

Imaginx
Imaginx used Ask the Experts™
on
I have a mysql table looks like this ...

address_id      bigint(11)      AUTO_INCREMENT
family_id      bigint(11)
CFOREIGNKE      varchar(40)
type            enum('M','S')
add_1            varchar(120)
add_2            varchar(120)
apt                  varchar(30)
city            varchar(60)
state            varchar(2)
zip                  varchar(5)
count_id      int(4)
created_in      varchar(40)
created_on      datetime
created_by      varchar(30)

Each family (family_id) has two addresses.
A Service and Mailing (type enum)

That means each family should have two address_id's.
(Should also be sequential bc the data is always entered two rows @ a time. The service add & mail add)

My issue that there are 23054 distinct family id's, but there are only 46080 addresses entered. (by count, not address id).

There should be 46108 if there were two rows for each id.

How can I run a report that will tell me every family id, or address_id that belongs to a row where the family id in that rows has anything but 2 rows entered .. ??

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT FamilyID, COUNT(*) AS AddressID
FROM myTable
GROUP BY FamilyID
HAVING COUNT(*) <> 2

Author

Commented:
I get a strange error : Table `mfcs`.'myTable'  doesn't exist.
What could cause that ??


just kidding.
Thats the perfect solution.
Can you explain it so I understand better why that works?
If you want to see a list of all the familyID and how many entries they have in the table you would use:

SELECT FamilyID, COUNT(*) AS AddressCount
FROM myTable
GROUP BY FamilyID

This makes 1 entry for each FamilyID, with the familyID and the number of times it appears as the two columns.
To only show the columns that are not 2, we use the HAVING clause, which is to aggregates as WHERE is to a normal query.
So adding
HAVING COUNT(*) <> 2 will show only those entries that do not have exactly two entries in the table (but at least one)

Author

Commented:
Thanks Lludden !