How to nest independent 'select'?

Question

How to nest independent 'select'?

Navigation

#1 by (2 votes)

3

I have the following table with information:

What I need to show is this:

I try with this code, but I duplicate the data:

               SELECT * FROM

                  (SELECT Codigo AS LYC 
                  FROM RequerimientoDetalle
                  WHERE Codigo like '%LYC%') t1,

                  (SELECT Codigo AS MYE 
                  FROM RequerimientoDetalle
                  WHERE Codigo like '%MYE%') t2

My question is if there is any function for mysql or sqlserver engine, to be able to successfully perform this query.

Also try to group them and the result is the same.

Take into account that the table grows, MyE-3..5,6,7.etc or LyC-3..5,6,7.etc

mysql sql-server

asked by Francisco Acevedo 10.08.2018 в 00:05

source

1 answer

Scroll JSON array and fill select dynamically Reading data from the serial port hangs my GUI

score 2 · Accepted Answer

One option is: with those 2 tables that you obtained, generate a FULL OUTER JOIN by a dynamic enumeration in select . You would create an enumerated column, in each select and then make the join on that column.

You can try it here link

 SELECT TLYC.codigo as LYC, TMYE.codigo as MYE FROM (SELECT codigo,row_num FROM 
    (
    SELECT T1.codigo ,@rownum:=@rownum+1 as row_num
    FROM 
        (
        SELECT codigo
        FROM mysql_test
        WHERE codigo like 'LYC-%') T1
    , (SELECT @rownum:=0) r
    ) TLYC_IN ) TLYC
LEFT JOIN
   (
   SELECT codigo,row_num FROM 
    (
    SELECT T2.codigo ,@rownum:=@rownum+1 as row_num
    FROM 
        (
        SELECT codigo
        FROM mysql_test
        WHERE codigo like 'MYE-%') T2
    , (SELECT @rownum:=0) r2
    ) TMYE_IN) TMYE ON TLYC.row_num = TMYE.row_num
UNION
SELECT TLYC.codigo as LYC, TMYE.codigo as MYE FROM (SELECT codigo,row_num FROM 
    (
    SELECT T1.codigo ,@rownum:=@rownum+1 as row_num
    FROM 
        (
        SELECT codigo
        FROM mysql_test
        WHERE codigo like 'LYC-%') T1
    , (SELECT @rownum:=0) r
    ) TLYC_IN ) TLYC
RIGHT  JOIN
   (
   SELECT codigo,row_num FROM 
    (
    SELECT T2.codigo ,@rownum:=@rownum+1 as row_num
    FROM 
        (
        SELECT codigo
        FROM mysql_test
        WHERE codigo like 'MYE-%') T2
    , (SELECT @rownum:=0) r2
    ) TMYE_IN) TMYE ON TLYC.row_num = TMYE.row_num

Note: full outer join is changed by UNION with LEFT and RIGHT JOIN

Inserts

Result: