I want to show the result as combination of two. Table 1

SELECT au.invoice`.`InvoiceID`, au.invoice`.`UserID` 
FROM au.`invoice` 
WHERE au.`invoice`.`InvoiceID` > 11126 
-- total results 757

enter image description here

Table 2

SELECT nz.`invoice`.`InvoiceID`, nz.`invoice`.`UserID` 
FROM nz.`invoice` 
WHERE nz.`invoice`.`InvoiceID` > 11236 
-- total results 757

enter image description here

I want result combined as table 1 result and table 2 results

SELECT au.invoice`.`InvoiceID`, au.invoice`.`UserID`, nz.`invoice`.`InvoiceID`, nz.`invoice`.`UserID` 
FROM au_._invoice` 
JOIN nz.`invoice` ON nz.`invoice`.`UserID` = au.invoice`.`UserID` 
WHERE au.invoice`.`InvoiceID` > 11126 
  AND nz.`invoice`.`InvoiceID` > 11236 
-- total results 1000+

enter image description here

What I want it should be the same no of results because I just assigned new invoiceId to table 2, all the users have the same no of invoices.

Can you help me to achieve this result?

Thanks Rajesh

  • a join wirks so, that it combines every every hit, so if there are two or more rows thet fit the join you must think about grouping or having another column that identifies the link between them better. So see if you git a date or something to identify the rows that should link
    – nbk
    Feb 19, 2020 at 8:43
  • Use ROW_NUMBER and enumerate records in each output (some ordering recommended strongly). Then join by this number.
    – Akina
    Feb 19, 2020 at 8:45
  • @nbk you are right , if I use 3rd column , it works in my other queries, Here i didn't use because that (3rd column may duplicate) may change. but using 3rd column I get the correct 757 rows.
    – rkrathor
    Feb 19, 2020 at 8:54
  • @Akina only 3rd column check worked, which I have to use but 3rd column may duplicate most of the time so I was looking if there any other way to do. I used order by each column and groupby (in which I found the no of rows vary each time), that is the reason I avoid that.
    – rkrathor
    Feb 19, 2020 at 9:13
  • How are the items for user 456 supposed to be paired up? There seem to be 3 in each table, hence 9 (3*3) in the result.
    – Rick James
    Feb 24, 2020 at 17:44

1 Answer 1



SELECT t1.InvoiceID, t1.UserID, t2.InvoiceID, t2.UserID
FROM ( SELECT InvoiceID, UserID, @num1 := @num1 + 1 rownumber
       FROM au.invoice, (SELECT @num1 := 0) variable
       WHERE InvoiceID > 1234
       ORDER BY UserId ) t1
JOIN ( SELECT InvoiceID, UserID, @num2 := @num2 + 1 rownumber
       FROM nz.invoice, (SELECT @num2 := 0) variable
       WHERE InvoiceID > 1234
       ORDER BY UserId ) t2 USING (rownumber)


If the list of UserID values in both subqueries is the same with guarantee (although it is unclear why) you may use JOIN ... USING (rownumber, UserID). Or even use the enumeration while partitioning by UserID (using 2 variables in each subquery).

  • thank you man very much Akina, this is amazing. this was beyond my level of thinking. I got the exact results.
    – rkrathor
    Feb 19, 2020 at 9:46
  • @rkrathor - But is it the exact results? "rownumbers" don't really exist in SQL tables. Tables are unordered sets; it is often just a coincidence that the rows show in the "right" order.
    – Rick James
    Feb 24, 2020 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.