MySQL Join two querys with same number of rows as results-CodePudding

I'm trying to Join two query results as one with no luck so far.

I tried Union but that just adds the second query result after the first query result. Tried scipping the second query alltogether and use something like cross join but that always returned the same row data from the table in the second query.

The first query is this:

SELECT 
    `namelist`.`id`,
    `name`.`id` AS  `name_id`, 
    `name_item`.`content`,
    `order`.`create_time`
    FROM
    `namelist`
    LEFT JOIN `name` ON `name`.`namelist_id` = `namelist`.`id`
    LEFT JOIN `name_item` ON `name_item`.`name_id` = `name`.`id`
    LEFT JOIN `order` ON `namelist`.`order_id` = `order`.`id`
    LEFT JOIN `items` ON `items`.`id` = `name_item`.`items_id`
    WHERE
    `namelist`.`order_id`=1380 AND `items`.`key`='Name'
    GROUP BY `name_item`.`content`
    ORDER BY `name`.`id`

The second query:

SELECT `validity`, `code`, `image` FROM `code` WHERE `code`.`order_id` = 1380 ORDER BY `id`

And as a result I would like to get something like this:

id | name_id | content | create_time | validity | code | image
--------------------------------------------------------------
1  | 1       | nameone | 2022-10-01  | somedate | 123  | 123.png
1  | 2       | nametwo | 2022-10-01  | somedate | 567  | 567.png

The querys return the same number of rows but they have no common identifier, and the reseult of the second query can not be duplicated because they have unique code colum.

CodePudding user response：

SELECT id,
       name_id,
       content,
       create_time,
       validity,
       code,
       image
FROM
  (-- first query
SELECT `namelist`.`id`,
       `name`.`id` AS `name_id`,
       `name_item`.`content`,
       `order`.`create_time`,
       ROW_NUMBER() OVER (ORDER BY `name`.`id`) AS rn
   FROM `namelist`
   LEFT JOIN `name` ON `name`.`namelist_id` = `namelist`.`id`
   LEFT JOIN `name_item` ON `name_item`.`name_id` = `name`.`id`
   LEFT JOIN `order` ON `namelist`.`order_id` = `order`.`id`
   LEFT JOIN `items` ON `items`.`id` = `name_item`.`items_id`
   WHERE `namelist`.`order_id`=1380
     AND `items`.`key`='Name'
   GROUP BY `name_item`.`content` 
-- ORDER BY `name`.`id`
) AS subquery1
JOIN
  (-- second query
SELECT `validity`,
       `code`,
       `image` ,
       ROW_NUMBER() OVER (ORDER BY `id`) rn
   FROM `code`
   WHERE `code`.`order_id` = 1380 
-- ORDER BY `id`
) AS subquery2 USING (rn)
ORDER BY rn