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]

Steven Stern wrote:

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

Well even if you had a host with 4.0.x you still wouldn't be able to do
subqueries. They won't be available until 4.1.x is released, and it's still
in beta (or is it gamma now?) Even then I suspect not many hosts will start
to host it until at least another couple of minor revisions have passed.

> 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)?

You need to join the table to itself (which is often more efficient than
using a subquery anyway) along the lines of this:

SELECT UserID
FROM User u
INNER JOIN UserSkills us1 ON u.UserID = us1.UserID
INNER JOIN UserSkills us2 ON u.UserID = us2.UserID
WHERE us1.SkillCode = 'A'
AND us2.SkillCode = 'B'

I have *not* tested this but it should work.

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

[Gordon Burditt]

>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)?

Untested, but this should work.

SELECT u.UserID
FROM User u, UserSkills a, UserSkills b
WHERE u.UserID = a.UserID and a.UserID = b.UserID and
a.SkillCode = 'A' and b.SkillCode = 'B';

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

Gordon L. Burditt

[Chris Hope]

Gordon Burditt wrote:

>>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)?

>
> Untested, but this should work.
>
> SELECT u.UserID
> FROM User u, UserSkills a, UserSkills b
> WHERE u.UserID = a.UserID and a.UserID = b.UserID and
> a.SkillCode = 'A' and b.SkillCode = 'B';
>
> This gets much messier when the number of skill codes is variable -
> you probably end up having PHP construct a query in a loop.

This is essentially the same as the inner join query I posted. The only
difference is I find the inner join type queries much easier to read
because it's more clear where the relationships between the tables are.

I used to always write my queries the way that Gordon has here but then
started working for about a year at a Microsoft shop using SQL Server, and
they had some great standards documents and always wrote their queries
using inner joins. I found it a little odd to start with but then quickly
adopted the style for all my database work because it makes more complex
queries much easier to read.

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

[Steven Stern]

On Thu, 14 Oct 2004 09:48:41 +1300 (more or less), Chris Hope
<blackhole@electrictoolbox.com> wrote:

>Gordon Burditt wrote:
>

>>>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)?

>>
>> Untested, but this should work.
>>
>> SELECT u.UserID
>> FROM User u, UserSkills a, UserSkills b
>> WHERE u.UserID = a.UserID and a.UserID = b.UserID and
>> a.SkillCode = 'A' and b.SkillCode = 'B';
>>
>> This gets much messier when the number of skill codes is variable -
>> you probably end up having PHP construct a query in a loop.

>
>This is essentially the same as the inner join query I posted. The only
>difference is I find the inner join type queries much easier to read
>because it's more clear where the relationships between the tables are.
>
>I used to always write my queries the way that Gordon has here but then
>started working for about a year at a Microsoft shop using SQL Server, and
>they had some great standards documents and always wrote their queries
>using inner joins. I found it a little odd to start with but then quickly
>adopted the style for all my database work because it makes more complex
>queries much easier to read.

Thanks. I'm off to give it a shot.

[Steven Stern]

On Thu, 14 Oct 2004 09:26:23 +1300 (more or less), Chris Hope
<blackhole@electrictoolbox.com> wrote:

>SELECT UserID
>FROM User u
>INNER JOIN UserSkills us1 ON u.UserID = us1.UserID
>INNER JOIN UserSkills us2 ON u.UserID = us2.UserID
>WHERE us1.SkillCode = 'A'
>AND us2.SkillCode = 'B'

BINGO!

Here's a full-fledged working query

SELECT DISTINCT
mp.UserId,mp.LastName,FirstName,City,State,Email,R egularMember,AlumniMember,HomePhone,
ShowResume, mp.SearchObjective, mp.BackgroundFunction, mp.BackgroundTitles,
mp.BackgroundIndustries,mp.PreviousCompanies, mr.TagLine, mr.ExecSummary,
mr.JobHistory, mr.Education, fj.comments, fj.NewTitle, fj.NewCompany,
fj.NewIndustry, fj.comments FROM ((MemberProfile mp left join MemberResume mr
on mp.UserId=mr.UserID) left join FoundJob fj on mp.UserId=fj.UserId) INNER
JOIN fcodes fc0 ON mp.UserID = fc0.UserID INNER JOIN fcodes fc1 ON mp.UserID =
fc1.UserID INNER JOIN fcodes fc2 ON mp.UserID = fc2.UserID INNER JOIN fcodes
fc3 ON mp.UserID = fc3.UserID WHERE (fc0.fcode='A' AND fc1.fcode='B' AND
fc2.fcode='C' AND fc3.fcode='E') ORDER BY mp.LastName,FirstName

The inner joins are built by

$cat_query_add_joins="";
if ($cat != '') {
$ccount=count($cat);
$cat_query_add = "(";
for ($i=0;$i<$ccount;$i++) {
if ($i>0) $cat_query_add.=" AND ";
$cat_query_add_joins.=" INNER JOIN fcodes fc$i ON mp.UserID =
fc$i.UserID ";
$cat_query_add.="fc$i.fcode='$cat[$i]'";
}
$cat_query_add.=")";
}

[Gene Wirchenko]

[email]gordonb.peuhl@burditt.org[/email] (Gordon Burditt) wrote:

[snip]

>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

The in has the list and the count is compared to the number of
skills.

If you want more user data,

select <whatever> from user
where userid in (<above query>)
order by <whatever>

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

[Chris Hope]

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 :)

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

[Tim Roberts]

Chris Hope <blackhole@electrictoolbox.com> wrote:

>
>I used to always write my queries the way that Gordon has here but then
>started working for about a year at a Microsoft shop using SQL Server, and
>they had some great standards documents and always wrote their queries
>using inner joins. I found it a little odd to start with but then quickly
>adopted the style for all my database work because it makes more complex
>queries much easier to read.

Do you think so? Maybe you could post a good example that shows this.

For me, most SQL reads like an English sentence. The INNER JOIN syntax
disturbs that. Compare this:

SELECT r.RID, r.LastName, p.FirstName, r.Address, r.City, r.State,
u.CollegeName
FROM
((registration r INNER JOIN person p ON r.RID = p.RID)
INNER JOIN university u ON p.CollegeID=u.CollegeID)
WHERE
r.LastName = 'Smith';

to this:

SELECT r.RID, r.LastName, p.FirstName, r.Address, r.City, r.State,
u.CollegeName
FROM
registration r,
person p,
university r
WHERE r.RID = p.RID
AND p.CollegeID = u.CollegeID
AND r.LastName = 'Smith';

For me, the second example reads more naturally. It gathers the list of
affected tables into one easily identifiable place, and makes the
connections part of the WHERE selection process. Query optimizers will
make these exactly equivalent. It gets even worse as the number of tables
increases.

It's probably a personal preference thing.
--
- Tim Roberts, [email]timr@probo.com[/email]
Providenza & Boekelheide, Inc.

[Chris Hope]

Tim Roberts wrote:

> Chris Hope <blackhole@electrictoolbox.com> wrote:

>>
>>I used to always write my queries the way that Gordon has here but then
>>started working for about a year at a Microsoft shop using SQL Server, and
>>they had some great standards documents and always wrote their queries
>>using inner joins. I found it a little odd to start with but then quickly
>>adopted the style for all my database work because it makes more complex
>>queries much easier to read.

>
> Do you think so? Maybe you could post a good example that shows this.
>
> For me, most SQL reads like an English sentence. The INNER JOIN syntax
> disturbs that. Compare this:
>
> SELECT r.RID, r.LastName, p.FirstName, r.Address, r.City, r.State,
> u.CollegeName
> FROM
> ((registration r INNER JOIN person p ON r.RID = p.RID)
> INNER JOIN university u ON p.CollegeID=u.CollegeID)
> WHERE
> r.LastName = 'Smith';
>
> to this:
>
> SELECT r.RID, r.LastName, p.FirstName, r.Address, r.City, r.State,
> u.CollegeName
> FROM
> registration r,
> person p,
> university r
> WHERE r.RID = p.RID
> AND p.CollegeID = u.CollegeID
> AND r.LastName = 'Smith';
>
> For me, the second example reads more naturally. It gathers the list of
> affected tables into one easily identifiable place, and makes the
> connections part of the WHERE selection process. Query optimizers will
> make these exactly equivalent. It gets even worse as the number of tables
> increases.
>
> It's probably a personal preference thing.

I would agree with it being a personal preference because when the database
server optimises your query it's not going to make a difference either way,
as they are fundamentally the same query.

To me the inner join syntax is much more readable because to me it's more
easy to see what you are joining them, than having to work it out from
looking at the where clause.

You don't actually need those braces around the from and inner join bits in
your example, and I'd reformat it like so (which, to me, makes it a lot
more readable):

SELECT r.RID, r.LastName, p.FirstName, r.Address, r.City, r.State,
u.CollegeName
FROM registration r
INNER JOIN person p ON r.RID = p.RID
INNER JOIN university u ON p.CollegeID=u.CollegeID
WHERE r.LastName = 'Smith';

I cannot give you a better example than this as it simply reads better to
me, whereas the non inner join syntax reads better to you :)

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

[Geoff Berrow]

I noticed that Message-ID: <mvbsm01gscp0qo57o3ftl3dpljffhtvt33@4ax.com>
from Tim Roberts contained the following:

>For me, the second example reads more naturally.

I'm with you there.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs [url]http://www.ckdog.co.uk/rfdmaker/[/url]

[Lemming]

On Thu, 14 Oct 2004 14:49:03 +1300, Chris Hope
<blackhole@electrictoolbox.com> wrote:

>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"?

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

[Lemming]

On Thu, 14 Oct 2004 14:49:03 +1300, Chris Hope
<blackhole@electrictoolbox.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"?

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

[Chris Hope]

Lemming wrote:

> On Thu, 14 Oct 2004 14:49:03 +1300, Chris Hope
> <blackhole@electrictoolbox.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 - [url]http://www.electrictoolbox.com/[/url]

[Gene Wirchenko]

Chris Hope <blackhole@electrictoolbox.com> wrote:

>Lemming wrote:

[snip]

>> 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:

I beg the leniency of the court, Your Honour.

In my dialect (Visual FoxPro), it does not matter. Now that I
look at it, where then group then having makes sense from a
chronological perspective.

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

[Lemming]

On Fri, 15 Oct 2004 14:07:51 +1300, Chris Hope
<blackhole@electrictoolbox.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.