If A is table the i want to join A to B or C or D or E based on the value of field in table A.... it clearly means that i need to use a case or if condiation but i am not able to join ... its showing sql error.. i searched google but didnt find much help.... is it not possible... pls help
Not an easy way to do a conditional join. I'm guessing that you have a serious denormalizing problem with your table structure. I'm assuming that you have something like. If the value in column = 1 JOIN tableA, if = 2 JOIN tableB, etc... If this is the case, the only easy way to get the proper data would be to use logic within your application. You "may" be able to create a view to return the proper data, but this can get even more complicated if you don't know what you're doing. It's definitely arguable as to how to store data in seperate tables that would be conditional like this. The other option would be to combine table A, B, C etc... into a single table, and then select the data based on the value of the column. This way you can always Join the table properly, and then you can conditionally select only the columns that you need. MySQL's view documentation: http://dev.mysql.com/doc/refman/5.1/en/create-view.html
thanks jestep for ur response.... and u are right its a great denormalozation problem.... it occurred due to adding new requirements to an already existing system..... but i am suprised cant we dynamically chose which table to join to table A inside query itself i cant do that in server side as the choice of selecting which table to join is based on the value of field in table.... And combing all the reqiured tables at once by means of union solves the work for me as u said but i am asking isnt there any efficient way ... expecting a solution.... thanks...
Hi, Just write your case statement using sub-queries (left join can be represented via sub-queries: the old school style). Regards