Problem with an SQL Statement

[Panthaur]

I have 2 tables which look as follows:
[tt]
CarrierApt Table
---------------------
carrierid varchar(12)
aptcode varchar(3)
zone int

Example Data for CarrierApt
carrierid aptcode zone
--------- ------- ----
FA MSP 1
FA MKE 2
FA ATL 3

CarrierScl Table
---------------------
carrierid varchar(12)
fromzone int
tozone int
scale varchar(3)

Example Data for CarrierScl
carrierid fromzone tozone scale
--------- -------- ------ -----
FA 1 1 A
FA 1 2 B
FA 1 3 C
FA 2 1 B
FA 2 2 A
FA 2 3 D
FA 3 1 A
FA 3 2 D
FA 3 3 E
[/tt]
What I want to be able to do is return a single SQL statement which relates the fromzone of 1 to MSP, and the tozone of 1 to MSP. Basically, I need a single SQL statement which returns the following recordset:
[tt]
carrierid fromzone tozone scale
--------- -------- ------ -----
FA MSP MSP A
FA MSP MKE B
FA MSP ATL C
FA MKE MSP B
FA MKE MKE A
FA MKE ATL D
FA ATL MSP A
FA ATL MKE D
FA ATL ATL E
[/tt]
I tried using the following sql statement, but it didn't work. I'm thinking I may need some sort of Join, but am not sure how to do it.

select carrierapt.aptcode as originapt, carrierapt.aptcode as destapt, carrierscl.scale from carrierapt,carrierscl where carrierscl.fromzone=carrierapt.zone and carrierscl.tozone=carrierapt.zone and carrierscl.carrierid='FA' order by originapt

The problem I'm running into is that I need to specify carrierapt.aptcode for the tozone, but I also need to specify it for the fromzone.

Any ideas anybody????

Thanks,
Pan

 

[ericbrunson]

Reference the table twice using different aliases ("as tablealias") in the where clause, then use the aliases as if they were two different tables.

 

[Panthaur]

I'm not sure what you mean? Can you give me an example based on my sql statement above?

Pan

 

[r937]

select carrierscl.carrierid   
     , ca1.aptcode as originapt
     , ca2.aptcode as destapt
     , carrierscl.scale 
  from carrierscl 
inner
  join carrierapt as ca1
    on carrierscl.fromzone = ca1.zone 
inner
  join carrierapt as ca2
    on carrierscl.tozone = ca2.zone 
 where carrierscl.carrierid = 'FA' 
order 
    by originapt

r937.com | rudy.ca

 

[Panthaur]

Thanks for the code snippet. I tried it, but the system hangs and freezes. I am using visual basic 6 with MySQL.

Any ideas?

Pan

 

[r937]

hangs and freezes? yikes!

select carrierscl.carrierid   
     , ca1.aptcode as originapt
     , ca2.aptcode as destapt
     , carrierscl.scale 
  from carrierscl 
inner
  join carrierapt as ca1
    on carrierscl.carrierid = ca1.carrierid 
   and carrierscl.fromzone = ca1.zone 
inner
  join carrierapt as ca2
    on carrierscl.carrierid = ca2.carrierid 
   and carrierscl.tozone = ca2.zone 
 where carrierscl.carrierid='FA' 
order 
    by originapt

r937.com | rudy.ca

 

[Panthaur]

Well, it's not hanging now, but i'm not getting the same number of values returned. In my example, this query:

select fromzone,tozone,scale from carrierscl where carrierid='FA' order by fromzone,tozone

returns this:

[tt]carrierid fromzone tozone scale
--------- -------- ------ -----
FA 1 1 A
FA 1 2 B
FA 1 3 C
FA 2 1 B
FA 2 2 A
FA 2 3 D
FA 3 1 A
FA 3 2 D
FA 3 3 E
[/tt]

when my goal to return is this:

[tt]carrierid fromzone tozone scale
--------- -------- ------ -----
FA MSP MSP A
FA MSP MKE B
FA MSP ATL C
FA MKE MSP B
FA MKE MKE A
FA MKE ATL D
FA ATL MSP A
FA ATL MKE D
FA ATL ATL E[/tt]

In my real life database, the first query is returning 89 lines, whereas the query you gave me returns 515 lines.

Is there a duplication somewhere?

Pan

 

[r937]

is there a duplication somewhere?

only you can say

this query should help --

select fromzone,tozone
  from carrierscl 
 where carrierid='FA' 
group
    by fromzone,tozone
having count(*)>1

r937.com | rudy.ca