CODEWITHSUNDEEP
mysqlsql
Coder
Coder

Reputation: 13

Sql fetch result from multiple table having same column but no relation

Hello all how can get two same name column from two different table as single result in two different column as combine result.

Eg: customer table have column customerid

custmerId customerName
1 row
2 row

Order Table

it has also column customerid

custmerId orderName
1 order1
4 order2
5 order3

Expected Output

custmerId custmerId
1 1
2 4
5

Note: There is no relation between both table

Upvotes: 0

Views: 86

Answers (3)

Akina
Akina

Reputation: 42622

MySQL does not support FULL JOIN, so

WITH
cte1 AS ( SELECT customerId, ROW_NUMBER() OVER (ORDER BY custmerId) rn
          FROM customer ),
cte2 AS ( SELECT customerId, ROW_NUMBER() OVER (ORDER BY custmerId) rn
          FROM order ),
cte3 AS ( SELECT rn FROM cte1 UNION SELECT rn FROM cte2 )
SELECT cte1.customerId, cte2.customerId
FROM cte3
LEFT JOIN cte1 USING (rn)
LEFT JOIN cte2 USING (rn);

Remember that an outer client software which accesses the output rowset columns by the name (not by the posession) won't print the result correctly. In this case you must assign unique aliases to the output columns. For example,

.. SELECT cte1.customerId AS customer_id, cte2.customerId AS order_id ..

Upvotes: 0

SavyJS
SavyJS

Reputation: 38

it's not possible. it's better to use aliases or group by.

you can use alias, a 'SELECT AS', to seprate column names:

SELECT o.CustomerID as OCustomerID, c.CustomerId as CCustomerID
FROM Customers AS c, Orders AS o;

Upvotes: 2

lepastiche
lepastiche

Reputation: 31

If by result, you mean a simple SELECT query result, you can just indicate the column name in a SELECT clause and separate the table names with a comma in the FROM clause. see example below:

SELECT customerID FROM Customers, Orders;

You can also add a "WHERE" clause at the end if you have conditions the query needs to meet.

Upvotes: 0

Related Questions