Hi,

I have a table called "ports" of city names index on the name which is
unique. I have a second table called "jobs" where each job is for a
single city. The relationship is that only some cities can be used as
jobs but more than one job can have the same cities

I'd like to query the "ports" table to retrieve only those cities that
appear in the jobs table so I used the following select query:

select ports.portcode, ports.name from ports
left join jobs on jobs.destinationport=ports.portcode
where ports.portcode=jobs.destinationport order by name

While the above query works, I get duplicate entries of city names
whereas I'd like only one. Any ideas on how to modify the above query
to achieve this? Using MySQL 5.0.18. Thanks in advance.

example of what I get back
--------------------------------------
Aachen
Antwerp
Antwerp
Antwerp
Hong Kong
Singapore
Singapore
Singapore
Singapore

example of what I want
---------------------------------
Aachen
Antwerp
Hong Kong
Singapore

donpro schreef:
> Hi,
>
> I have a table called "ports" of city names index on the name which is
> unique. I have a second table called "jobs" where each job is for a
> single city. The relationship is that only some cities can be used as
> jobs but more than one job can have the same cities
>
> I'd like to query the "ports" table to retrieve only those cities that
> appear in the jobs table so I used the following select query:
>
> select ports.portcode, ports.name from ports
> left join jobs on jobs.destinationport=ports.portcode
> where ports.portcode=jobs.destinationport order by name
>
> While the above query works, I get duplicate entries of city names
> whereas I'd like only one. Any ideas on how to modify the above query
> to achieve this? Using MySQL 5.0.18. Thanks in advance.
>
> example of what I get back
> --------------------------------------
> Aachen
> Antwerp
> Antwerp
> Antwerp
> Hong Kong
> Singapore
> Singapore
> Singapore
> Singapore
>
> example of what I want
> ---------------------------------
> Aachen
> Antwerp
> Hong Kong
> Singapore
>

use

SELECT distinct .....

http://dev.mysql.com/doc/refman/5.0/...imization.html

On 8 Feb, 15:40, Luuk <L...@invalid.lan> wrote:
> donpro schreef:
>
>
>
>
>
> > Hi,
>
> > I have a table called "ports" of city names index on the name which is
> > unique. I have a second table called "jobs" where each job is for a
> > single city. The relationship is that only some cities can be used as
> > jobs but more than one job can have the same cities
>
> > I'd like to query the "ports" table to retrieve only those cities that
> > appear in the jobs table so I used the following select query:
>
> > select ports.portcode, ports.name from ports
> > left join jobs on jobs.destinationport=ports.portcode
> > where ports.portcode=jobs.destinationport order by name
>
> > While the above query works, I get duplicate entries of city names
> > whereas I'd like only one. Any ideas on how to modify the above query
> > to achieve this? Using MySQL 5.0.18. Thanks in advance.
>
> > example of what I get back
> > --------------------------------------
> > Aachen
> > Antwerp
> > Antwerp
> > Antwerp
> > Hong Kong
> > Singapore
> > Singapore
> > Singapore
> > Singapore
>
> > example of what I want
> > ---------------------------------
> > Aachen
> > Antwerp
> > Hong Kong
> > Singapore
>
> use
>
> SELECT distinct .....
>
> http://dev.mysql.com/doc/refman/5.0/...mization.html- Hide quoted text -
>
> - Show quoted text -

or group by ports.name

strawberry wrote:
> On 8 Feb, 15:40, Luuk <L...@invalid.lan> wrote:
>> donpro schreef:
>>
>>
>>
>>
>>
>>> Hi,
>>> I have a table called "ports" of city names index on the name which is
>>> unique. I have a second table called "jobs" where each job is for a
>>> single city. The relationship is that only some cities can be used as
>>> jobs but more than one job can have the same cities
>>> I'd like to query the "ports" table to retrieve only those cities that
>>> appear in the jobs table so I used the following select query:
>>> select ports.portcode, ports.name from ports
>>> left join jobs on jobs.destinationport=ports.portcode
>>> where ports.portcode=jobs.destinationport order by name
>>> While the above query works, I get duplicate entries of city names
>>> whereas I'd like only one. Any ideas on how to modify the above query
>>> to achieve this? Using MySQL 5.0.18. Thanks in advance.
>>> example of what I get back
>>> --------------------------------------
>>> Aachen
>>> Antwerp
>>> Antwerp
>>> Antwerp
>>> Hong Kong
>>> Singapore
>>> Singapore
>>> Singapore
>>> Singapore
>>> example of what I want
>>> ---------------------------------
>>> Aachen
>>> Antwerp
>>> Hong Kong
>>> Singapore
>> use
>>
>> SELECT distinct .....
>>
>> http://dev.mysql.com/doc/refman/5.0/...mization.html- Hide quoted text -
>>
>> - Show quoted text -
>
> or group by ports.name
>

SELECT DISTINCT is the correct way to go. Grouping is non-standard SQL
and only works with MySQL.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Feb 9, 3:38 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> strawberry wrote:
> > On 8 Feb, 15:40, Luuk <L...@invalid.lan> wrote:
> >> donpro schreef:
>
> >>> Hi,
> >>> I have a table called "ports" of city names index on the name which is
> >>> unique. I have a second table called "jobs" where each job is for a
> >>> single city. The relationship is that only some cities can be used as
> >>> jobs but more than one job can have the same cities
> >>> I'd like to query the "ports" table to retrieve only those cities that
> >>> appear in the jobs table so I used the following select query:
> >>> select ports.portcode, ports.name from ports
> >>> left join jobs on jobs.destinationport=ports.portcode
> >>> where ports.portcode=jobs.destinationport order by name
> >>> While the above query works, I get duplicate entries of city names
> >>> whereas I'd like only one. Any ideas on how to modify the above query
> >>> to achieve this? Using MySQL 5.0.18. Thanks in advance.
> >>> example of what I get back
> >>> --------------------------------------
> >>> Aachen
> >>> Antwerp
> >>> Antwerp
> >>> Antwerp
> >>> Hong Kong
> >>> Singapore
> >>> Singapore
> >>> Singapore
> >>> Singapore
> >>> example of what I want
> >>> ---------------------------------
> >>> Aachen
> >>> Antwerp
> >>> Hong Kong
> >>> Singapore
> >> use
>
> >> SELECT distinct .....
>
> >>http://dev.mysql.com/doc/refman/5.0/...tion.html-Hide quoted text -
>
> >> - Show quoted text -
>
> > or group by ports.name
>
> SELECT DISTINCT is the correct way to go. Grouping is non-standard SQL
> and only works with MySQL.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

OK - but we're still left with one question. Seeing as no new
information is gleaned from the (left) join, what's the point of the
OP's sql query?

strawberry wrote:
> On Feb 9, 3:38 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> strawberry wrote:
>>> On 8 Feb, 15:40, Luuk <L...@invalid.lan> wrote:
>>>> donpro schreef:
>>>>> Hi,
>>>>> I have a table called "ports" of city names index on the name which is
>>>>> unique. I have a second table called "jobs" where each job is for a
>>>>> single city. The relationship is that only some cities can be used as
>>>>> jobs but more than one job can have the same cities
>>>>> I'd like to query the "ports" table to retrieve only those cities that
>>>>> appear in the jobs table so I used the following select query:
>>>>> select ports.portcode, ports.name from ports
>>>>> left join jobs on jobs.destinationport=ports.portcode
>>>>> where ports.portcode=jobs.destinationport order by name
>>>>> While the above query works, I get duplicate entries of city names
>>>>> whereas I'd like only one. Any ideas on how to modify the above query
>>>>> to achieve this? Using MySQL 5.0.18. Thanks in advance.
>>>>> example of what I get back
>>>>> --------------------------------------
>>>>> Aachen
>>>>> Antwerp
>>>>> Antwerp
>>>>> Antwerp
>>>>> Hong Kong
>>>>> Singapore
>>>>> Singapore
>>>>> Singapore
>>>>> Singapore
>>>>> example of what I want
>>>>> ---------------------------------
>>>>> Aachen
>>>>> Antwerp
>>>>> Hong Kong
>>>>> Singapore
>>>> use
>>>> SELECT distinct .....
>>>> http://dev.mysql.com/doc/refman/5.0/...tion.html-Hide quoted text -
>>>> - Show quoted text -
>>> or group by ports.name
>> SELECT DISTINCT is the correct way to go. Grouping is non-standard SQL
>> and only works with MySQL.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
>
> OK - but we're still left with one question. Seeing as no new
> information is gleaned from the (left) join, what's the point of the
> OP's sql query?
>

It's supposed to show a list of city names (from the ports table) which
have at least one entry in the jobs table.

I don't think he'll want a LEFT JOIN in this case. But one problem at a
time.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================