Imaginx
asked on
All I wanna know is what's in rows !!??!!
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 .. ??
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 .. ??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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)
ASKER
Thanks Lludden !
ASKER
What could cause that ??
just kidding.
Thats the perfect solution.
Can you explain it so I understand better why that works?