SQL help - can this be done with one query?

[eluser]crikey[/eluser]
I'm wondering if someone might be able to help me with an SQL query, based on the following tables:

tags: id, name, user_id

items_tags: id, item_id, tag_id

I'm trying to construct a query which will return, for a given user_id, their tags (id and name) from the tags table and the item count for each tag from the items_tags table.

I tried a query which joins the two tables, selecting id, name and count(*), grouping by tag_id in the items_tags table, but some tags don't have any rows in the items_tags table. For these tags I'd like to get zero for the item count. The query which I wrote (I'm afraid I don't have the code with me at the moment) only returns the tags that have corresponding rows in the items_tags table.

Can this be done in one query, or will I need to query the tags table, then loop through each row and query the items_tags table to get the count?

Thank you.

[eluser]Armchair Samurai[/eluser]
Best guess, but no guarentees:

Code:

SELECT t.id, t.name, COUNT(i.tag_id) AS count
FROM tags t
LEFT JOIN items_tags i ON i.tag_id = t.id // I'm a little fuzzy how the tables are joined here...
WHERE t.id_user = ?
GROUP BY t.id

[eluser]crikey[/eluser]
Thanks.

I know I tried it using a left join, but tags with no entries in the items_tags table returned 1 for the item count - presumably because it existed in the tags table?

And the inner join didn't return any rows at all for tags with no entries in the items_tags table.

Although that was using count(*) and not count(tag_id). Not sure if that'll make a difference, but I'll try your suggestion when I get home.

[eluser]Hockeychap[/eluser]
You want to use some conditional logic in you sql statement. The exact statement will depend on your database.
If I've read your email correctly then:

1. You want the count of all tags for each user
2. If there are no tags then you want a count of 0 returned
3. If there is a count of 1 or more then you want the count value returned.

Assuming you're using Mysql your statement should use a left join and a if condition are look something like:

Code:

select t.name,t.user_id, if(it.tag_id is not null, sum(1), 0) as "Items"
from tags as t
left join item_tags as it on ( it.tag_id = t.id) // change to match your table join :)
group by t.name,t.user_id

The logic says, if there is a non-null join (which is what mysql returns when it can find a record on the joined table) add one to the total count (the sum function). If there is a null then return a 0;

Hope this helps
Justin

[eluser]crikey[/eluser]
Hi Justin,

Not quite. I don't want the count of the tags for a user. What I want is all of the tags for a user, and for each tag a count of the number of items that have that tag (ie. the number of rows in the items_tags table that have that tag id). And if no rows exist, I want the count to be 0.

I'll look into the conditional logic you mentioned. I didn't know you could use conditional statements like that in SQL.

Thanks for your help.
Grant

[eluser]Sarfaraz Momin[/eluser]
Hey Grant,
I donno if the following would help you much but I had a similar situation and I found a way to get pass it. The query is as follows

MarterLinks = tags
CountLinks = item_tag

Code:

select masterLinks.masterLinkID, masterLinks.postUser,".
"masterLinks.postPic,masterLinks.postLink,".
"masterLinks.postID,masterLinks.postText,".
"masterLinks.postDate,masterLinks.postContent,masterLinks.postTitle,".
"substring(max(concat(countLinks.published,'|',countLinksID)),21) as countLinksID, ".
"count(countLinksID) as numCountLinks from masterLinks left join countLinks ".
"using(masterLinkID) group by masterLinkID order by masterLinkID;

You can add a simple userid in the where clause to get result based on users.

Have a good day !!!