#FlattenTheCurve ×

# 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)

+-----+-----+
|  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
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 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.

##### Share on other sites

Hi requinix,

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.

##### 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
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.

##### 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 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
```

##### 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.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×

• #### Activity

• Chat
×
• Create New...