Select values from two tables with multiple requirements but only use one of them

Question

I want to join two tables based on a condition.

Table A:

+--------+-------------+------+
| prefix | Destination | rate |
+----------------------+------+
| 56     |  Monn       |  25  |
| 5602   |  Monn M1    |  23  |
| 5604   |  Monn M3    |  44  |
| 5607   |  Monn M1    |  23  |
| 5625   |  Monn M2    |  22  |
| 23     |  Xpia       |  0.3 |
| 238    |  Xpia F3    |  0.9 |
+--------+-------------+------+

Table B :

+--------+-------------+------+
| prefix | Destination | rate |
+----------------------+------+
| 56     |  Monn       |  75  |
| 560    |  Monn M1x   |  49  |
| 5607   |  Monn M1    |  03  |
| 56254  |  Monn M2    |  9.5 |
| 23     |  Xpia       |  1.3 |
| 2301   |  Xpia T1    |  2.4 |
| 2302   |  Xpia T2    |  3.5 |
| 2381   |  Xpia F     |  8.9 |
+--------+-------------+------+

Desired output:

Table C:

+--------+-------------+------+
| prefix | Destination | rate |
+----------------------+------+
| 56     |  Monn       |  75  |
| 5602   |  Monn M1    |  49  |
| 5604   |  Monn M3    |  49  |
| 5607   |  Monn M1    |  03  |
| 5625   |  Monn M2    |  9.5 |
| 23     |  Xpia       |  1.3 |
| 238    |  Xpia F3    |  8.9 |
+--------+-------------+------+

Notice the following conditions:

1. I want to use only Prefix and Destination columns of table A.
2. I want to use only rate column of table B.
3. If Prefix of table A = Prefix of table B, then copy rate
4. If prefix of table A is not found in table B, then copy rate of prefix in table B that starts with prefix of table A ( return only the longest strings's value ).
5. If prefix of table A is not in table B then copy rate of prefix in Table B where prefix of Table A is a starts with a prefix in Table B. ( return only longest string's value)

If condition 4 and 5 are met, return only the rate of the longest prefix.

I have this query but it is not working. I realized I need a more complicated query.

INSERT INTO Table C(prefix,destination, rate) 
       SELECT Table A.prefix, Table A.destination, Table B.rate 
       FROM Table A, Table B 
       WHERE Table B.prefix= SUBSTRING(Table A.prefix, 1, length(Table B.prefix))

Answer 1

DISCLAIMER: The following answer is very long, so I will first grab your attention by saying the query at the end produces results matching what you want. Here is the SQL Fiddle.

---

Well, the best way to approach this in my opinion is one requirement at a time.

I want to use only Prefix and Destination columns of table A.

This is just a simple select statement:

SELECT a.prefix, a.destination
FROM tableA a;

---

I want to use only rate column of table B.

We can add that in easily as well. Note that what I have right now will just create a cartesian product, but it will straighten out as we add more requirements:

SELECT a.prefix, a.destination, b.rate
FROM tableA a, tableB b;

---

If Prefix of table A = Prefix of table B, then copy rate.

I changed the above to use a correlated subquery in the select clause that pulls the rate if it has a prefix matching that of tableA. This will put null (for the moment) for any values that don't match:

SELECT a.prefix, a.destination, 
   (SELECT b.rate FROM tableB b WHERE b.prefix = a.prefix) AS rate
FROM tableA a;

---

If prefix of table A is not found in table B, then copy rate of prefix in table B that starts with prefix of table A (return only the longest string's value).

For this, I stepped away for a second and wrote the query that gets the rate from B where the prefix starts with that of table A, forgetting about the previous condition. I used the substring function just like you did, but ordered in descending order of length to get the largest one:

SELECT a.prefix, a.destination, 
   (SELECT b.rate FROM tableB b WHERE b.prefix = 
   SUBSTRING(a.prefix, 1, LENGTH(b.prefix)) ORDER BY LENGTH(b.prefix) DESC LIMIT 1) AS rate
FROM tableA a;

Now, you can take that select query and the one above them and use the COALESCE function to get the first non-null value. So the ones from step 3 that return null values will be replaces by that of step 4 (if there is a non null value still):

SELECT a.prefix, a.destination, COALESCE(
  (SELECT b.rate FROM tableB b WHERE b.prefix = a.prefix),
  (SELECT b.rate FROM tableB b WHERE b.prefix = SUBSTRING(a.prefix, 1, LENGTH(b.prefix)) ORDER BY LENGTH(b.prefix) DESC LIMIT 1)) AS rate  
FROM tableA a;

---

If prefix of table A is not in table B then copy rate of prefix in table B where prefix of table A starts with a prefix in table B. (return only longest string's value).

Well, we can do the same thing we did in the last one, except manipulate the subquery to check opposite tables. Also, the COALESCE function does not have a limit in parameters, so we can just add it as the third parameter. If the first one returns null, it will try the second. If that returns null, it will try the third. Here is the final query:

SELECT a.prefix, a.destination, COALESCE(
  (SELECT b.rate FROM tableB b WHERE b.prefix = a.prefix),
  (SELECT b.rate FROM tableB b WHERE b.prefix = SUBSTRING(a.prefix, 1, LENGTH(b.prefix)) ORDER BY LENGTH(b.prefix) DESC LIMIT 1),
  (SELECT b.rate FROM tableB b WHERE a.prefix = SUBSTRING(b.prefix, 1, LENGTH(a.prefix)) ORDER BY LENGTH(a.prefix) DESC LIMIT 1)) AS rate
FROM tableA a;

---

When comparing my results to yours, I noticed that the above does not take into consideration that both 4 AND 5 are met, in which case we want to take the longest prefix. While there may be a cleaner way to write it, I just wrote the following case statement:

SELECT a.prefix, a.destination,
  CASE WHEN 
    (SELECT b.rate FROM tableB b WHERE b.prefix = a.prefix) IS NOT NULL
  THEN
    (SELECT b.rate FROM tableB b WHERE b.prefix = a.prefix)
  ELSE
    CASE WHEN
      ((SELECT b.rate FROM tableB b WHERE b.prefix = SUBSTRING(a.prefix, 1, LENGTH(b.prefix)) ORDER BY LENGTH(b.prefix) DESC LIMIT 1) IS NOT NULL)
      AND
      ((SELECT b.rate FROM tableB b WHERE a.prefix = SUBSTRING(b.prefix, 1, LENGTH(a.prefix)) ORDER BY LENGTH(a.prefix) DESC LIMIT 1) IS NOT NULL)
    THEN
      CASE WHEN 
        (SELECT LENGTH(b.prefix) FROM tableB b WHERE b.prefix = SUBSTRING(a.prefix, 1, LENGTH(b.prefix)) ORDER BY LENGTH(b.prefix) DESC LIMIT 1)
        >
        (SELECT LENGTH(a.prefix) FROM tableB b WHERE a.prefix = SUBSTRING(b.prefix, 1, LENGTH(a.prefix)) ORDER BY LENGTH(a.prefix) DESC LIMIT 1)
      THEN
        (SELECT b.rate FROM tableB b WHERE b.prefix = SUBSTRING(a.prefix, 1, LENGTH(b.prefix)) ORDER BY LENGTH(b.prefix) DESC LIMIT 1)
      ELSE
        (SELECT b.rate FROM tableB b WHERE a.prefix = SUBSTRING(b.prefix, 1, LENGTH(a.prefix)) ORDER BY LENGTH(a.prefix) DESC LIMIT 1)
      END
    ELSE
      COALESCE(
        (SELECT b.rate FROM tableB b WHERE b.prefix = SUBSTRING(a.prefix, 1, LENGTH(b.prefix)) ORDER BY LENGTH(b.prefix) DESC LIMIT 1),
        (SELECT b.rate FROM tableB b WHERE a.prefix = SUBSTRING(b.prefix, 1, LENGTH(a.prefix)) ORDER BY LENGTH(a.prefix) DESC LIMIT 1))
    END
  END AS rate
FROM tableA a;

The logic behind it is kind of like the following:

• Check if condition 3 is met.
  • If it is, use that value.
• Check if conditions 4 AND 5 are met.
  • If they are, check if prefix from condition 4 is longer.
    • If it is, use that. If it's not, use prefix from condition 5.
  • If they are not, select the first non-null condition from conditions 4 and 5.

Answer 2

I hate sub queries but this should do the trick :

INSERT INTO TableC (prefix, destination, rate)
SELECT
    TableA.prefix,
    TableA.destination,
    (SELECT TableB.rate
        FROM TableB
        WHERE TableB.prefix = SUBSTRING(TableA.prefix, 1, LENGTH(TableB.prefix))
        OR TableA.prefix = SUBSTRING(TableB.prefix, 1, LENGTH(TableA.prefix))
        ORDER BY LENGTH(TableB.prefix) DESC LIMIT 1)
FROM TableA;