mysql use the selected variable in the same query

Question

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

Can you create a sqlfiddle example with the table structures, its tough to write a join query without knowing the actual fields — skv
– skv, Commented Aug 26, 2013 at 13:09
table-1 - id,type (type = sea or air) table_air and table_sea are the other two tables. Now i query table-1 to fetch the type first. Now depending on the type ,i have to either join table_sea or table_air. That is the logic i cant seem to apply. — user1411837
– user1411837, Commented Aug 26, 2013 at 13:14
Can you create the example there, I want the structure of table_sea and table_air to see if there are opportunities to join there — skv
– skv, Commented Aug 26, 2013 at 13:17

ffflabs · Accepted Answer · 2013-08-26 13:17:48Z

1

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')";

answered Aug 26, 2013 at 13:17

ffflabs

17.6k5 gold badges57 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1411837 Over a year ago

crazy..this worked perfectly fine. Thanks a lot. @skv - thanks for the help.

skv Over a year ago

@user1411837 happy that it worked, but did you say my suggestion also worked?

ffflabs Over a year ago

@user1411837 be aware that you're actually doing BOTH joins and just picking the column afterwards. It saves you one query at the cost of pulling more data from the engine. This is an issue if you're billed on bandwidth.

skv · Accepted Answer · 2013-08-26 13:22:49Z

0

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.

answered Aug 26, 2013 at 13:22

skv

1,8033 gold badges20 silver badges27 bronze badges

Collectives™ on Stack Overflow

mysql use the selected variable in the same query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related