SQL question

[Ruslan Zasukhin]

On 10/10/08 11:53 PM, "william humphrey" <shoreagent at gmail.com> wrote:

Hi William, 

> I have a problem with SQL that I hope you can help me with.
> I have one table "my_clients" which which has a field "clients" and another
> field "client_ID"
> 
> I have another table "bill_of_ladings" which has two fields "shipper" and
> "consignee" the contents of these two fields are a "client_ID" number. There
> are two links one called "shippers" for the shipper field and one called
> "consignees" for the consignee field.
> 
> The following SQL works fine:
> 
> select clients as shipper from my_clients join info_billoflading on shippers
> 
> And the following SQL works fine:
> 
> select clients as consignee from my_clients join info_billoflading on
> consignees
> 
> But what I want is
> 
> select clients as consignee, clients as shipper from my_clients join
> info_billoflading on consignees join info_billoflading on shippers (and this
> SQL does not work)
> 
> The only way I can get it to work is a complicated "union":
> ------
> 
> select clients as shipper from my_clients join info_billoflading on shippers
> union
> select clients as consignee from my_clients join info_billoflading on
> consignees
> 
> ----
> 
> But the problem with the union as the result is in all one column and I want
> it in two columns. Can you help?

You need use power of Table Alias

    From T1 a join T1 b

Now it will looks like you have TWO tables a and b
Instead of T1.

This allow unroll your two tables into three

                    my_clients
        
    tblShipper                       tblConsignees


Having such schema in head, can you resolve task?

SELECT 
    tblConsignees.clients as consignee,
    tblShipers.clients as shipper
FROM 
         bill_of_ladings AS tblShipers JOIN my_clients ON shippers
    JOIN bill_of_ladings AS tblConsignees ON consignees


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]