Jump to content
ajoo

linearize relationship

Recommended Posts

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 by ajoo

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

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 !

Share this post


Link to post
Share on other sites

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 !!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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
  • Like 1

Share this post


Link to post
Share on other sites

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 !!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.