Perform subquery based on a query

Question

Perform subquery based on a query

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

1

As the title indicates, I would like to query a Table "Table1" with the results obtained from another Table "Table2"

CONSULTATION 1

To get the data from the Table1

SELECT id FROM box_12.esale_rc WHERE kind=30;

which gives me as a result the fields "id" (INT) of 107 lines:

| id  |
|-----|
| 86  |
| 436 |
| 438 |
| ... |
| ... |

CONSULTATION 2

Before I should mention that the field involved in this query "detail" (VARCHAR) is a text string ("eSale invoice at De Test # 4 ") that inside contains the " id " that I get from Table1 so I must convert and extract the necessary data < strong> "OrderId" .

To get the results of the Table2

SELECT id, st, detail,
 CONVERT(SUBSTR(detail,(LOCATE('#',detail) +1)), UNSIGNED INTEGER)AS OrdenId
FROM box_12.warehouse_rc
WHERE LOCATE('#',detail)>0 
ORDER BY OrdenId DESC;

Which gives me as a result 4271 lines:

| id   |  st |              detail               | OrdenId  |
|:----:|:---:|:---------------------------------:|:--------:|
| 4273 | 16  | eSale invoice # 5117 (Traje Baño) | 5117     |
| 4272 | 16  | Sale invoice # 5116 (Traje Baño)  | 5116     |
| 4270 | 16  | eSale invoice # 5115 (Outlet)     | 5115     |
| 4271 | 16  | eSale invoice # 5114 (Traje Baño) | 5114     |
| ...  | ... | ...                               | ...      |
| ...  | ... | ...                               | ...      |

THE EXPECTED

I need to be able to make a query where the results of the Query1 the (107) lines are the conditions for the Query2 to give me only the ids of the 107 expected results .

all this in order to do a UPDATE after the st

field

Greetings, I hope you have explained me well.

mysql query mysql-workbench

asked by Héctor 05.09.2018 в 19:06

source

3 answers

Problem with accents in "git rebase" Why can not I send the value of the qlineedit

score 0 · Answer 1

To make subqueries, based on your question, you can do it in the following way:

Using SubQuery within the SELECT: but you will not be served the order By

    SELECT  
        (SELECT id, st, detail,
                CONVERT(SUBSTR(detail,(LOCATE('#',detail) +1)), UNSIGNED INTEGER)AS OrdenId
            FROM box_12.warehouse_rc
           WHERE LOCATE('#',detail)>0
             AND box_12.warehouse_rc.id =  box_12.esale_rc.id)
        ORDER BY OrdenId DESC) 
  FROM box_12.esale_rc 
 WHERE kind=30;

Using Subquery on the WHERE and / or HAVING

SELECT id, st, detail,
       CONVERT(SUBSTR(detail,(LOCATE('#',detail) +1)), UNSIGNED INTEGER)AS OrdenId
FROM box_12.warehouse_rc
WHERE LOCATE('#',detail)>0 
AND (SELECT id FROM box_12.esale_rc  
      WHERE kind=30
        AND box_12.esale_rc.id = box_12.warehouse_rc.id);
ORDER BY OrdenId DESC;

LAST USING Inner Join the latter will be the fastest

SELECT id, 
       st, 
       detail,
       CONVERT(SUBSTR(detail,(LOCATE('#',detail) +1)), UNSIGNED INTEGER)AS OrdenId
  FROM box_12.warehouse_rc 
 INNER JOIN box_12.esale_rc  on (box_12.esale_rc.kind=30 AND box_12.esale_rc.id = box_12.warehouse_rc.id)
 WHERE LOCATE('#',detail)>0 
 ORDER BY OrdenId DESC;

score 0 · Answer 2

Well I would do the following query:

SELECT id, st, detail, CONVERT(SUBSTR(detail,(LOCATE('#',detail) +1)), UNSIGNED INTEGER)AS OrdenId FROM box_12.warehouse_rc WHERE LOCATE('#',detail)>0 AND id in (SELECT id FROM box_12.esale_rc WHERE kind=30) ORDER BY OrdenId DESC;

add the following line to your second query:

AND id in (SELECT id FROM box_12.esale_rc WHERE kind=30)

score 0 · Answer 3

I solved yesterday thanks to @JackNavaRow

You can do select query2 where id in (query1), is what you want? - JackNavaRow

as follows:

SELECT id AS wrcId, st AS wrcSt, detail AS wrcDetail,
    CONVERT(SUBSTR(detail,(LOCATE('#',detail) +1)), UNSIGNED INTEGER)AS ordenId
FROM box_12.warehouse_rc
WHERE LOCATE('#',detail)>0 AND id IN(
SELECT id FROM box_12.esale_rc WHERE kind=30)
ORDER BY OrdenId DESC;