mdimartinoopenaccessinc.net
Date: Fri May 23 2008 - 14:04:57 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Thanks for the quick reply, however i am getting a failure on line 2.

SELECT
11.POPS AS `POPA`,
12.POPS AS `POPZ`
FROM
circuits
INNER JOIN pops.POPS as 11 WHERE circuits.popA = 11.popID
INNER JOIN pops.POPS as 12 WHERE circuits.popZ = 12.popID

Table-name = circuits
circuitID
popA (secondary key referencing pops.popID)
popZ (secondary key referencing pops.popID)

table-name = pops
popID
pops

----- Original Message -----
From: "Rob Wultsch" <wultschgmail.com>
To: mdimartinoopenaccessinc.net
Cc: "MYSQL General List" <mysqllists.mysql.com>
Sent: Friday, May 23, 2008 8:35:39 AM GMT -08:00 US/Canada Pacific
Subject: Re: Query Output Issue

On Fri, May 23, 2008 at 11:27 AM, <mdimartinoopenaccessinc.net> wrote:
> I have a table that has two has two secondary keys referencing the same primary key of another.
>
> Table-name =Circuits
> circuitID
> CircuitID
> A_loc
> Z_loc
>
> Table-name = Location
> locationID
> location
>
> circuits.A_loc references locatioin.locationID
> circuits.Z_loc references locatioin.locationID
>
>
> How do i achieve the following output.
>
> circuitID Location A LocationZ
> 98129 BOSTON ATLANTA
>
>
> Do I actually have to create two location tables one for the A location and one for the Z location.

SELECT circuitID,
              l1.location AS 'Location_A',
              l2.location AS 'Location_Z'
FROM Circuits
        INNER JOIN Location AS l1 WHERE circuits.A_loc =l1.locationID
        INNER JOIN Location AS l2 WHERE circuits.Z_loc =l2.locationID

--
Rob Wultsch
wultschgmail.com
wultsch (aim)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]