[RESOLVED] Need help with the correct Join

jawa

I have the following two tables:

[FONT=Courier New]
Table 1:
Name Other Irrelevant Fields

Jeff
John
Rick

Table 2:
Name Skills

Jeff Php
Jeff Mysql
John Php
John Mysql
Rick Php

[/FONT]

I need a report that shows a listing of all People with a count of mysql skills only.
So, in other words, I need to get:

[FONT=Courier New]
Name count(skills)

Jeff 1
John 1
Rick 0
[/FONT]

When I create the report, Rick always gets left off...he doesn't even show.

Now, I understand I need an outer join, but I can't seem to make Mysql give me what I need. I've tried left outer, right outer, left, right, straight, but nothing seems to work.

I'm sure I'm missing something very easy...can somebody shed some light please???

Thanks!
-J

Drakla

SELECT
t1.name
FROM
t1, t2
WHERE
t1.name = t2.name AND
skill = 'mysql';

jawa

Thanks for the quick response!

But, what about the count of Mysql skills? That's the main part I'm having problems with...

Any ideas?

Drakla

Ah the 'ubiquitous left join'

Here's a query of mine that finds out how many notes are on each user, just change the fields for your stuff

SELECT name, count(ab_notes.id)
FROM ab_users
LEFT JOIN ab_notes
ON ab_users.id = ab_notes.owner
GROUP BY ab_users.id;

jawa

Drakla wrote:
Ah the 'ubiquitous left join'

Here's a query of mine that finds out how many notes are on each user, just change the fields for your stuff

SELECT name, count(ab_notes.id)
FROM ab_users
LEFT JOIN ab_notes
ON ab_users.id = ab_notes.owner
GROUP BY ab_users.id;

That almost works. That counts all values (skills) and groups them by person. I'm still missing the 0 value I need for the report. Refer to the first post...this is what I need:

[FONT=Courier New]
Name count(skills)

Jeff 1
John 1
Rick 0
[/FONT]

This is what I'm getting:

[FONT=Courier New]
Name count(skills)

Jeff 1
John 1
[/FONT]

When I throw in the where statement to pull only MySQL values, Rick gets dropped off the report and I need him to show as a 0.

Thanks for all the help!

-J

Drakla

Can you show your converted query, as that works exactly as you'd need it with users with no notes still being shown.

jawa

Drakla wrote:
Can you show your converted query, as that works exactly as you'd need it with users with no notes still being shown.

Sure thing!

SELECT a.name, count(b.skills)
FROM names a
LEFT JOIN skills b
ON a.name = b.name
WHERE b.skills = 'MySQL'
GROUP BY a.name

In the first post, Table 1 = Names, Table 2 = Skills.

It's acting like an inner join, and only returning the values that match the where statement. I need it to return a listing of all names, even if those names don't have the matching skills. In that case, I need the name to be listed with a 0 for number of matching skills.

Hope that explains it!

-J

Drakla

I see what you're doing - you're still including the mysql thing as part of the query - ok, well that has to be part of the ON clause, too, not WHERE

SELECT a.name, count(b.skills)
FROM names a
LEFT JOIN skills b
ON a.name = b.name
AND b.skills = 'MySQL'
GROUP BY a.name

There's a really good article about the differences between ON and WHERE out there somewhere which I'll post if I find.

EDIT FOUND:

http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html

Let me know if that's the ticket, dude.

jawa

Drakla wrote:
I see what you're doing - you're still including the mysql thing as part of the query - ok, well that has to be part of the ON clause, too, not WHERE

SELECT a.name, count(b.skills)
FROM names a
LEFT JOIN skills b
ON a.name = b.name
AND b.skills = 'MySQL'
GROUP BY a.name

There's a really good article about the differences between ON and WHERE out there somewhere which I'll post if I find it.

Let me know if that's the ticket, dude.

FREAKIN' SONOFA... Change one word and it works flawlessly! That IS the ticket, exactly what I needed! I really appreciate the help!

-J