linearize relationship

[ajoo]

Hi all !!

 

I have the following three data tables :

 

mysql> select * from franch;

+-----+

|  fid  |

+-----+

|   3   |

|   5   |

|   7   |

+-----+

3 rows in set (0.00 sec)  

 

mysql> select * from master;

+-----+-----+

| xid  | mid |

+-----+-----+

|   5   |   4  |

|   7   |   6  |

+-----+-----+

2 rows in set (0.01 sec)

 

mysql> select * from admin;

+-----+-----+

|  xid | aid  |

+-----+-----+

|   3   |   2  |

|   4   |   2  |

+-----+-----+

 

These are connected on common fields and I was trying to linearize the relationship between them using this query :

 

mysql> SELECT vc.aid, vd.mid as mid, ve.fid as franch

FROM franch as ve

LEFT JOIN master as vd

ON ve.fid = vd.xid

LEFT JOIN admin as vc

ON vd.mid = vc.xid;

 

which gives the following , almost correct, output. 

+------------+------------+--------------+

|      aid     |      mid    |     franch   |

+------------+------------+--------------+

|   NULL    |   NULL   |         3       |

|        2      |        4     |         5       |

|   NULL    |        6     |         7       |

+------------+------------+--------------+

 

what I want to achieve as output is this ( difference HIGHLIGHTED in RED ):

 

+---------+---------+---------+

|   aid     |   mid   | franch |

+---------+---------+---------+

|    2      |  NULL |      3    |

|    2      |    4      |      5    |

| NULL  |    6      |      7    |

+---------+---------+---------+

 

In the first since there is no corresponding vale for mid in the master table it produces the null value. Here there has to be a way that when such a null value is produced, the franch should check for a corresponding value in the admin table instead. 

 

Thanks all for any help on achieving this !!!

 

 

 

 

Edited January 17, 2018 by ajoo

[requinix]

If you want to prefer values in master over values in admin then you need a second JOIN for that. Then you can get the correct value with a COALESCE

COALESCE(master's value, admin's value)

A more obvious solution would be to OR the franch table in

LEFT JOIN admin as vc
ON vd.mid = vc.xid OR fe.fid = vc.xid

but that only works if you know the value can't be in both admin tables (or else you'd get two matching rows).

 

But really you shouldn't have this I-don't-know-which-table-the-value-is-in issue to begin with. It suggests there are data problems.

[ajoo]

Hi requinix, 

 

Thanks for the reply. 

 

May I please request you to kindly show me how this is done? I have no idea of the coalesce command. 

 

I'll just try out the other one.

 

Thanks loads !

[ajoo]

Hi requinix, 

 

Works !!!

 

SELECT vc.aid, vd.mid as mid, ve.fid as franch

FROM franch as ve

LEFT JOIN master as vd

ON ve.fid = vd.xid

LEFT JOIN admin as vc

ON vd.mid = vc.xid OR ve.fid = vc.xid;

 

+---------+---------+-----------+

|     aid   |    mid  |  franch  |

+---------+---------+-----------+

|     2     |  NULL |       3      |

|     2     |     4     |       5      |

| NULL  |     6     |       7      |

+---------+---------+-----------+

3 rows in set (0.00 sec)

 

Why did I not think of that OR statement there !?? 

 

It would be great to learn how it can be achieved using the coalesce command. If you can take some time to show that too. 

 

Thanks loads !!

[requinix]

Works !!!

It gives you the result you wanted. That does not mean it works. Did you read what I said the problem with this approach was? Does it apply to you?

 

It would be great to learn how it can be achieved using the coalesce command.

Function.

 

It's like every other function out there, and, like every other function out there, the best place to start understanding it would be in the documentation.

[Barand]

You could do the query in two parts. One part that matches franch to admin and the other that matches franch to master. (I am all for aliases but I prefer to use them to aid readability whereas as you appear to use them to aid obfuscation )

SELECT a.aid
         , m.mid
         , f.fid
    FROM franch f
          INNER JOIN
        admin a ON f.fid  = a.xid
          LEFT JOIN
        master m ON a.xid = m.mid
    WHERE m.mid IS NULL
    
    UNION
    
    SELECT a.aid
         , m.mid
         , f.fid
    FROM franch f
          INNER JOIN
        master m ON f.fid  = m.xid
          LEFT JOIN
        admin a ON a.xid = m.mid

[ajoo]

Hi requinix and Guru Barand !

 

Thanks for the replies !!

 

@requinix  

 

 

 

 Did you read what I said the problem with this approach was? Does it apply to you?

Yes I read your caution. An admin will never be in the master table and vice versa since each can be either a master or an Admin alone. So I guess this approach will safely work. Still I'll add more users to the tables and test it more thoroughly.

 

@ Guru Jacques 

 

Thank you for this approach. I'll study it and I am sure it will help me in more than one way as have many such queries that you helped me with. I am also sure that this is a more foolproof solution.

 

 

 

I am all for aliases but I prefer to use them to aid readability whereas as you appear to use them to aid obfuscation 

 

Not at all Sir. I do make changes to tables and shorten queries so that the problem is clear and easy to follow but It's never been my intention to obfuscate the queries / code. I am sorry if it seems that way. Maybe you can point out what makes my query obfuscated and I'll try and be more careful about it next time.

 

Thanks loads !!