CODEWITHSUNDEEP
mysql
user1411837
user1411837

Reputation: 1564

mysql use the selected variable in the same query

I have a table 'booking_summary' which stores the type of method (method = Air or Sea). I have to join this table with one of the two other tables depending on the method column.

If the method is Air,then the table to join is booking_air,if sea then it is booking_sea. I do not want to run multiple queries on this particular page.

This is my latest attempt,that has obviously failed.The table_name with alias is the table i want in the same query. 

$sql = "select case when a.shipping_method = 'Sea' then 'booking_sea' else 'booking_air' end 'table_name',
                case when a.user_id ='$active_id' then 'y' else 'no' end 'generate_access',
                case when c.mbl is NULL then 'Pending' else c.mbl end 'mbl_status',
                case when c.hbl is NULL then 'Pending' else c.hbl end 'hbl_status',
                a.*,b.user_name 
                from booking_summary a
                left join registered_users b 
                on a.user_id = b.user_id
                left join table_name c
                on a.id = c.id
                where (a.user_id = '$active_id' or a.forwarder='$active_id')";

Any advice would be very helpful. Thanks

Upvotes: 0

Views: 91

Answers (2)

skv
skv

Reputation: 1803

Without fully understanding your structure, I can think of this solution

SELECT a.shipping_method 
FROM   shipping_summary AS A 
       LEFT JOIN (SELECT *, 
                          'AIR' AS METHOD 
                   FROM   shipping_air) AS B 
               ON A.shipping_method = B.method 
       LEFT JOIN (SELECT *, 
                          'SEA' AS METHOD 
                   FROM   shipping_sea) AS C 
               ON A.shipping_method = C.method

This is a high level answer as I do not have the fields to be selected and more ways to optimise the query.

Upvotes: 0

ffflabs
ffflabs

Reputation: 17481

Om I'm not sure if this is going to work but, anyhow...

$sql = "select case 
                when a.user_id ='$active_id' then 'y' 
                else 'no' end 'generate_access',
            if(a.shipping_method = 'Sea',
                case when c.mbl is NULL then 'Pending' else c.mbl end,
                case when d.mbl is NULL then 'Pending' else d.mbl end ) 'mbl_status',
            if(a.shipping_method = 'Sea',
                case when c.hbl is NULL then 'Pending' else c.hbl end,
                case when d.hbl is NULL then 'Pending' else d.hbl end ) 'hbl_status',
                 a.*,b.user_name 
                from booking_summary a
                left join registered_users b  on a.user_id = b.user_id
                left join booking_sea c  on a.id = c.id
                left join bookin_air d on a.id=d.id
                where (a.user_id = '$active_id' or a.forwarder='$active_id')";

Upvotes: 1

Related Questions