Writing SQL query without subselect

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 SkillCo
de
B.

How do I write the query (queries)?

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 - http://www.electrictoolbox.com/

>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 SkillC
ode
>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

Gordon Burditt wrote:
 
>
> 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 - http://www.electrictoolbox.com/

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

>Gordon Burditt wrote:
> 
>
>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.

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,Regu
larMember,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 m
r
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.=")";
}

gordonb.peuhl@burditt.org (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.

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

Nice solution :)

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

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, timr@probo.com
Providenza & Boekelheide, Inc.

Tim Roberts wrote:

> Chris Hope <blackhole@electrictoolbox.com> wrote: 
>
> 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 - http://www.electrictoolbox.com/