以递归方式在Mysql中组合表

Question

I have a Mysql db with orders. Every order has one or several vendor invoices (tbl vendorinvoices) and also one or several customer invoices (tbl cinvoices).

I would like to list all orders and also all vendor and client invoices. It should be done in some recursive way but I cant figure out how to do it exactly.

At this time, in PHP I loop all orders, and then inside the loop I have a second query to list all vendor and customer invoices but it’s a quite amateurish way of doing it.

Is there a better way?

Order table

CREATE TABLE `inquery` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `clientid` int(11) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=424 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

Vendor invoice table

CREATE TABLE `vendorinvoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoicenumber` varchar(255) DEFAULT NULL,
  `inquery` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=146 DEFAULT CHARSET=latin1

Client invoice table

CREATE TABLE `invoice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer` varchar(255) DEFAULT NULL,
  `inqueryid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=294 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

Desired result

|---------------|---------------------|-------------------|
| Inquery id    | Client invoice  id  | Vendor invoice id |
|---------------|---------------------|-------------------|
| 1             | 1                   | 1                 |
|               | 2                   | 2                 |
|---------------|---------------------|-------------------|
| 2             | 3                   |                   |
|---------------|---------------------|-------------------|
| 3             | 4                   | 3                 |
|               | 5                   |                   |
|               | 6                   |                   |
|---------------|---------------------|-------------------|

Answer 1

This is a pain in MySQL, because it does not support row_number() or CTEs. However, you can do this with variables, UNION ALL, and aggregation:

select inquiryid, max(clientinvoiceid) as clientinvoiceid,
       max(vendorinvoiceid) as vendorinvoiceid
from ((select inquiryId, id as clientinvoiceid, NULL as vendorinvoiceid,
              (@rnc := if(@ic = inquiryId, @rnc + 1,
                          if(@ic := inquiryId, 1, 1)
                         )
              ) as seqnum
       from cinvoices cross join
            (select @ic := -1, @rnc := 0) as params
       order by inquiryId
      ) union all
      (select inquiryId, NULL as clientinvoiceid, id as vendorinvoiceid,
              (@rnv := if(@iv = inquiryId, @rnv + 1,
                          if(@iv := inquiryId, 1, 1)
                         )
              ) as seqnum
       from vendorinvoices cross join
            (select @iv := -1, @rnv := 0) as params
       order by inquiryId
      )
     ) cv
group by inquiryid, seqnum;

This enumerates the inquiries for customers and vendors -- separately. Then it aggregates by inquiryid and the enumerated value.