Displays the results in the query with COUNT + LEFT JOIN

The question was asked: 6 years 11 months ago   views: 21
0

There are 2 tables
1) Contacts

id | Name

2) Relatives

id | Contact1 (contact first) | Contact2 (two) | Family (who have)

1 contact can be any number of relations. It is necessary at the output of the output contacts, if the contact relationship.

SELECT contact.id, contact.name, COUNT(fam.id) FROM contact LEFT JOIN contact on fam.id = fam.contact or contact.id = fam.contact2 GROUP BY fam.id ORDER BY contact.name

Problem: everything works, but at the base 743 of the contact, and displays a maximum of 50.

Asked: angry angry 21-06-2012 в 14:03:02
It seems that gorgeous) - KARTOH KARTOH 21-06-2012 в 14:32:27

Answers   2

0

but if in GROUP BY add contact.id and contact.name, and fam.id to remove?

Answered: _perchuk _perchuk 21-06-2012 в 14:24:29
0

And if so?

SELECT contact.id, contact.name, COUNT(fam.id) FROM contact 
LEFT JOIN fam on contact.id = fam.contact1 or contact.id = fam.contact2 
GROUP BY contact.id, contact.name 
ORDER BY contact.name
Answered: msi msi 21-06-2012 в 18:53:35