Need help with MySQL Join

[shardul]

I have a table called h1 that has a column called username, besides other columns.

 

If I were to get the data about particular user, I would do

SELECT * FROM h1 where username='robert'.

 

Users can write experiences about this particular entry for this particular user, stored in experiences table.

experiences table has

resourceName - Who this experiences are about  (such as for username 'robert' in above case.)

category - Which category these experiences belog to (such as h1 in above case.

Like h1, I have other tables like f1, l1, m1 etc.)

 

To get the count of all the experiences for h1 entry for robert,  I would do

select count(experiences.id) from experiences where experiences.resourceName='rboert' and experiences.category=1007;  (1007 specifies that is for h1 table, 1008 for l1, 1009 for m1 and so on).

 

However, how do I combine these two queries so that I can get the h1 entry and count of its corresponding experiences in one query?

 

I tried

select h1.*, count(experiences.id) as numComments from h1, experiences where h1.username='robert' and experiences.resourceName='robert' and experiences.category=1007 GROUP BY NULL;

 

[Ken2k7]

Wow, what a horrible unorganized setup.

 

Also, what the heck is GROUP BY NULL?

 

SELECT h.*, COUNT(e.id) AS numComments
FROM h1 h
INNER JOIN experiences e
     ON h.username = e.resourceName
     AND e.category = 1007

[shardul]

1.  Do you have suggestions for better set up? I admit I am not good at databases.

 

2.

I tried your query and I get the error message

 

Mixing of GROUP columns (MIN(), MAX(), COUNT()...) with no GROUP columns is illegal if there is no GROUP BY classifier.

 

3. Also, please note that I need to get the row from h1 column for a specific user. Your query does not specify any particular user. Don't we need to specify somewhere in the query, the user 'robert' in my example?

[Ken2k7]

1. Um.. only thing I suggest is to read up on DB normalization.

2. You haven't told me how you want it displayed.

 

Try this -

 

SELECT h.*, COUNT(e.id) AS numComments
FROM h1 h
INNER JOIN experiences e
     ON h.username = e.resourceName
     AND e.category = 1007
WHERE h.username = 'robert'
GROUP BY h.username

[shardul]

I tried it. It does not give any error.

 

There is one entry for user robert in table h1. There are no entries for user robert in table experiences in h1 (1007) category.

 

Your query shows empty result set because there are no experiences corresponeding to h1 entry for user robert in the database currently. In that case, I would like numComments to be 0.  I need to display the contents of h1 entry even if currently there are no experiences regarding it. People can add their experiences about this entry and the count would go up eventually.

[shardul]

I believe I have found much easier way

 

SELECT *, (select count(id)  from experiences where resourceName='robert' and category=1007) as numComments FROM h1 WHERE username='robert';