Limiting Results

I am trying to limit results pulled from two tables, each with all states
and congressional districts, to specified congressional districts. I want
each college to appear once matched to each congressional district (that
is in one table and is easy to do) and also include a column from a second
table with the Representative. My syntax, below, produces far too many
results, matching college and district and Representative many times.

The syntax:

select a.college, a.state, a.cd, b.rep from a, b

where

a.state = "al" and a.cd = "3" or
a.state = "az" and a.cd = "1" and

a.state = b.state and
a.cd = b.cd

What do I do to limit to single match for each college in the specified
congressional districts?

Ken

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Limiting Results

------=_Part_1373_6926262.1116456199481
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

select distinct?

On 5/18/05, kc68@cornell.edu wrote:
>=20
> I am trying to limit results pulled from two tables, each with all states
> and congressional districts, to specified congressional districts. I want
> each college to appear once matched to each congressional district (that
> is in one table and is easy to do) and also include a column from a secon=
d
> table with the Representative. My syntax, below, produces far too many
> results, matching college and district and Representative many times.
>=20
> The syntax:
>=20
> select a.college, a.state, a.cd , b.rep from a, b
>=20
> where
>=20
> a.state =3D "al" and a.cd =3D "3" or
> a.state =3D "az" and a.cd =3D "1" and
>=20
> a.state =3D b.state and
> a.cd =3D b.cd
>=20
> What do I do to limit to single match for each college in the specified
> congressional districts?
>=20
> Ken
>=20
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=3Drandy.lovin@gmail.co=
m
>=20
>

------=_Part_1373_6926262.1116456199481--

Re: Limiting Results

I put the word "distinct" after select, but the result is each college
shows up with each
Representative on the list (though with the correct state and cd for each
college each time). Near the end it gives individual colleges for one cd
with the right Rep.

********
On Wed, 18 May 2005 18:43:19 -0400, Randy Lovin
wrote:

> select distinct?
>
> On 5/18/05, kc68@cornell.edu wrote:
>>
>> I am trying to limit results pulled from two tables, each with all
>> states
>> and congressional districts, to specified congressional districts. I
>> want
>> each college to appear once matched to each congressional district (that
>> is in one table and is easy to do) and also include a column from a
>> second
>> table with the Representative. My syntax, below, produces far too many
>> results, matching college and district and Representative many times.
>>
>> The syntax:
>>
>> select a.college, a.state, a.cd , b.rep from a, b
>>
>> where
>>
>> a.state = "al" and a.cd = "3" or
>> a.state = "az" and a.cd = "1" and
>>
>> a.state = b.state and
>> a.cd = b.cd
>>
>> What do I do to limit to single match for each college in the specified
>> congressional districts?
>>
>> Ken
>>
>> --
>> MySQL Windows Mailing List
>> For list archives: http://lists.mysql.com/win32
>> To unsubscribe: http://lists.mysql.com/win32?unsub=randy.lovin@gmail.com
>>
>>



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Limiting Results

I'm just guessing here, but possibly a "GROUP BY a.college" would do it?

Chris.

At 23:40 18/05/2005, kc68@cornell.edu wrote:
>I am trying to limit results pulled from two tables, each with all states
>and congressional districts, to specified congressional districts. I want
>each college to appear once matched to each congressional district (that
>is in one table and is easy to do) and also include a column from a second
>table with the Representative. My syntax, below, produces far too many
>results, matching college and district and Representative many times.
>
>The syntax:
>
>select a.college, a.state, a.cd, b.rep from a, b
>
>where
>
>a.state = "al" and a.cd = "3" or
>a.state = "az" and a.cd = "1" and
>
>a.state = b.state and
>a.cd = b.cd
>
>What do I do to limit to single match for each college in the specified
>congressional districts?
>
>Ken
>
>--
>MySQL Windows Mailing List
>For list archives: http://lists.mysql.com/win32
>To
>unsubscribe:
>http://lists.mysql.com/win32?unsub=crobinson@manchester.act aris.com
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Limiting Results

--=_alternative 004719CA85257006_=
Content-Type: text/plain; charset="US-ASCII"

kc68@cornell.edu wrote on 05/18/2005 06:40:45 PM:

> I am trying to limit results pulled from two tables, each with all
states
> and congressional districts, to specified congressional districts. I
want
> each college to appear once matched to each congressional district (that

> is in one table and is easy to do) and also include a column from a
second
> table with the Representative. My syntax, below, produces far too many
> results, matching college and district and Representative many times.
>
> The syntax:
>
> select a.college, a.state, a.cd, b.rep from a, b
>
> where
>
> a.state = "al" and a.cd = "3" or
> a.state = "az" and a.cd = "1" and
>
> a.state = b.state and
> a.cd = b.cd
>
> What do I do to limit to single match for each college in the specified
> congressional districts?
>
> Ken
>

You need parentheses in your WHERE clause so that your OR is evaluated at
the correct time.

select a.college, a.state, a.cd, b.rep
from a, b
where (a.state = "al" and a.cd = "3") or
(a.state = "az" and a.cd = "1") and
(a.state = b.state and
a.cd = b.cd)

However, this uses the correct but easily misused comma-separated join
method of creating an INNER JOIN between the tables a and b. Here is what
it would look like if you used an explicit INNER JOIN declaration:

SELECT a.college, a.state, a.cd, b.rep
FROM a
INNER JOIN b
ON a.state = b.state and
AND a.cd = b.cd
WHERE (a.state = "al" and a.cd = "3")
OR (a.state = "az" and a.cd = "1")

By moving the JOIN conditions to their own ON clause, it makes it easier
(at least for me) to understand why the parentheses are important to
getting the correct results from this query. When they were part of the
WHERE clause, they helped to obscure the problem. As an added incentive to
get everyone to learn and use the explicit form, the explicit form JOIN is
also the only form in which you can declare an outer (LEFT or RIGHT) JOIN
in MySQL and most other database systems (Oracle is known exception,
others probably exist).

Without the parentheses, your OR operator was "naked" so it only applied
to the term next to it. With the parentheses, it is evaluated in the
correct sequence and is applied to the correct sets of terms.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



--=_alternative 004719CA85257006_=--