Writing SQL query without subselect

[Steven Stern]

I'm stuck on a host that is still running MYSQL version 3.

I need to flatten out a relationship to AND a set of criteria.

In a nutshell

User
UserID
UserEmail
UserOther

UserSkills
UserID
SkillCode

Any user may have zero, one, or many UserSkills.

I need to find a list of UserIDs for Users who have SkillCodes A and SkillCode
B.

How do I write the query (queries)?

[Chris Hope]

Lemming wrote:

On Thu, 14 Oct 2004 14:49:03 +1300, Chris Hope
<bl*******@elec trictoolbox.com > wrote:

[Aargh! The amended fu got me. Reposting]

Gene Wirchenko wrote:

This gets much messier when the number of skill codes is variable -
you probably end up having PHP construct a query in a loop.

select userid from userskills
group by userid
where skillcode in ('A','B','C','D ')
having count(*)=4

Nice solution :)

Minor syntax quibble: does MySQL allow "where" after "group by"?

Looks like he got the query in the wrong order, because you are correct,
group by must be after the where clause. Therefore his query should be:

select userid from userskills
where skillcode in ('A','B','C','D ')
group by userid
having count(*)=4

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Lemming]

On Fri, 15 Oct 2004 14:07:51 +1300, Chris Hope
<bl*******@elec trictoolbox.com > wrote:

Looks like ...

[snip]

You keep changing the followup-to line and removing most of the
groups. Please don't do that. I don't read comp.lang.php, so if I
was to not change followup-to back to include the full list of groups
I would be posting to groups I don't read, so wouldn't see either my
own post nor any replies.

Lemming
--
Curiosity *may* have killed Schrodinger's cat.