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

| improve this question | | | | |
  • 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 at 8:43
  • Use ROW_NUMBER and enumerate records in each output (some ordering recommended strongly). Then join by this number. – Akina Feb 19 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 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 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 at 17:44


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).

| improve this answer | | | | |
  • thank you man very much Akina, this is amazing. this was beyond my level of thinking. I got the exact results. – rkrathor Feb 19 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 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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