All geek questions in one place

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

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

Required Conclusion:

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

Pay attention to the following conditions:

  • I want to use only the Prefix and Destination columns of table A
  • I want to use only the rate column of table B
  • If Prefix table A = Prefix table B, copy rate
  • If the Prefix table A not found in table B , copy the rate from Prefix to table B , which starts with Prefix table A (returns only the longest value).
  • If the Prefix table A is not in table B, then the copy speed of the prefix in table B, where the prefix of table A begins with the prefix in table B. (returns only the longest string value)

If conditions 4 and 5 are met, return only the speed of the longest prefix.

I have this request, but it does not work. I realized that I needed a more complex 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))
+5
source share
4 answers

DISCLAIMER . The following answer is very long, so I first drew your attention by saying that the query at the end gives the results that match what you want. Here is the SQL Fiddle .

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

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

This is a simple select statement:

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

I want to use only the speed column of table B.

We can add this as easily. Please note that what I have now will simply create a Cartesian product, but it will straighten out as additional requirements are added:

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

If table prefix A = table B prefix, then copy speed.

I modified the above to use a correlated subquery in the select clause, which pulls the speed if it has a prefix matching the value of tableA. This will result in zero (for now) for any values ​​that do not match:

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

If the table A prefix is ​​not found in table B, then the copy speed of the prefix in table B starts with the table A prefix (returns only the longest string value).

To do this, I stepped back for a second and wrote a request that receives a bet from B, where the prefix starts with the prefix of table A, forgetting about the previous condition. I used the substring function just like you, but ordered in descending order of length to get the largest:

 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 this select query and one of them and use the COALESCE function to get the first nonzero value. Thus, those from step 3 that return null values ​​will be replaced by steps 4 (if there is a non-empty value):

 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 the prefix of table A is not in table B, then the copy speed of the prefix in table B, where the prefix of table A begins with the prefix in table B. (returns only the longest string value).

Well, we can do the same as the last, with the exception of manipulating the subquery, to check the opposite tables. In addition, the COALESCE function has no limit in the parameters, so we can simply add it as the third parameter. If the first returns null, it will try the second. If this returns null, it will try to make a third. Here is the final request:

 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 with yours, I noticed that the above does not take into account that both 4 and 5 are satisfied, in which case we want to take the longest prefix. Although 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 looks something like this:

  • Check if condition 3 is fulfilled.
    • If so, use this value.
  • Check if conditions 4 and 5 are fulfilled.
    • If so, check if the prefix from condition 4 exceeds.
      • If so, use this. If this is not the case, use the prefix from condition 5.
    • If this is not the case, select the first nonzero condition from conditions 4 and 5.
+2
source

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;
+1
source

You can join TABLE_B twice and use fancy IFNULL in your SELECT :

 INSERT INTO TABLE_C (prefix,destination, rate) SELECT TABLE_A.prefix, TABLE_A.destination, IFNULL(B1.rate, B2.rate) FROM TABLE_A LEFT JOIN TABLE_B AS B1 ON B1.prefix = TABLE_A.prefix LEFT JOIN ( SELECT * FROM TABLE_B ORDER BY LENGTH(prefix) DESC ) AS B2 ON B2.prefix = SUBSTRING(TABLE_A.prefix, 1, LENGTH(B2.prefix)) GROUP BY TABLE_A.prefix;

IFNULL returns the first argument, if one exists, otherwise returns to the second. In my experience, placing an IF condition in a SELECT performs better than putting it in a JOIN .

EDIT

Based on your table C, it looks like you also want the 6th condition or 5b where you suppress the prefix of table B instead of the prefix of table A. This will never work, since you have values ​​that can go anyway (table A where the prefix = 238 may correspond to B 23 or B 2381).

This solution will work for you, however, if you eliminate this “5b” condition.

0
source

Here's how to do it using left join and case-when

 insert into tableC (prefix,Destination,rate) select t1.prefix, t1.Destination, case when t2.prefix is null then ( select rate from tableB where tableB.prefix = SUBSTRING(t1.prefix, 1, LENGTH(tableB.prefix)) order by SUBSTRING(t1.prefix, 1, LENGTH(tableB.prefix)) desc limit 1 ) else t2.rate end as rate from tableA t1 left join tableB t2 on t1.prefix = t2.prefix where t1.prefix is not null;

Demo

Now, using logic, when you mention eg prefixes 5602 and 5604 are not in Table B. But Table B has 56 and 560 that are substrings of 5602 and 5604. But since 560 is longer than 56, return the rate of 560 instead

238 not in the 2nd table, but 23 is the largest substring in the second table, the speed comes from this row.

0
source

Source: https://habr.com/ru/post/1209374/

More articles:


All Articles