[SOLVED] Query and Left Join

[lucasandbobbi]

There is probably a easy solution to this I just do not know it.

 

I have 2 tables, one is called parts and the other is called locations, the parts table has several columns, 2 of them are called current_location and storage_location. Both of these columns relate to the locations table. When I do a query  to display all the parts in a while loop I left join the locations.locations_id column with the storage_location column but I can't do the same for the current_location column in the same query and I have to resort to nesting another query in the while loop for each result to get the current locations to display in the output, below is a simple example of the table, how would a query this to show all parts and both locations.

 

        Locations Table                                    Parts Table

location_id | location_name    part_id | part_name | current_location | storage_location

--------------------------    ---------------------------------------------------------

      1        |    Office              1      |    Printer  |          1            |          1

      2        |    Garage              2      |  Mouse    |          2            |          1

      3        |    Backyard            3      |  Pen        |          1            |          2

      4        |    Home                4      |    Wallet  |          4            |          1

                                            5      |  Case      |          3            |          1

                                            6      |  Shoes    |        2              |          3

 

 

 

 

 

[kickstart]

Hi

 

Use aliases for the table names and the column names.

 

SELECT a.part_id, a,part_name, b.location_id AS CurrentLocationId, b.location_name AS CurrentLocationName, c.location_id AS StorageLocationId, c.location_name AS StorageLocationName
FROM Parts a
LEFT OUTER JOIN Locations b ON a.current_location = b.location_id
LEFT OUTER JOIN Locations c ON a.storage_location = c.location_id

 

All the best

 

Keith

[lucasandbobbi]

OK this is starting to make sence to me but I'm still a bit confused, I have never used alias's before.

 

In you code you have " SELECT a.part_id, a,part_name, b.location_id " in the beginning, what are the " a. and a, " doing and how can you select "location_id" when further in the query we are gettting info from Parts.

 

I guess I just need a little further explanation of what is going on so i can apply it to my full database, thanks again for all the help.

[lucasandbobbi]

NM got it working, was a typo I think "a,part_name" should be "a.part_name"