Join two table without common field?

Question

0.00/5 (No votes)

See more:

SQL

How Can i join two table without any common field?

Posted 28-Apr-14 6:19am

SRKarn

Updated 13-Dec-17 22:16pm

Add a Solution

Comments

NeverJustHere 28-Apr-14 12:50pm

Yes. You can do a cross join. It will join all rows of table A with all rows of table B.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

JagB · Answer 1 · 2014-04-28T07:59:00

This is really unorthodox and hopefully it's not anything in production. But, there a couple of ways to do this - one mentioned by NeverJustHere - most accepted way of achieving your scenario.

Here are some ways I know (there are possibly more) would be:
1. Cartesian Product - basically matching every row in the first table with every in the second table. I don't know if this is what you need, since if you have 100 rows in the first table and 100 rows in the second table, it will give you 10,000 rows! To achieve this:

SQL

SELECT * FROM table1, table2

2. A union of the two tables.

SQL

SELECT column1, column2, etc FROM table1
UNION
SELECT column1, column2, etc FROM table2

You'll need to ensure that the column datatypes match up here.
3. Cross Join (as already mentioned)

SQL

SELECT table1.Column1, table2.Column1 FROM table1 
CROSS JOIN table2 WHERE table.Column1 = 'Some value'

4. I would even say you could use an inner join as well with a condition that's true.

SQL

SELECT table1.Column1, table2.Column2 FROM table1 
INNER JOIN table2 ON 1 = 1

Best bet would be to go with a cross join as already mentioned by another user.

Hope that helps!
SS

Santosh kumar Pithani · Answer 2 · 2017-12-13T22:16:00

Solution 3

;WITH T1 AS (select EmpName,Salary,ROW_NUMBER()OVER(ORDER BY (SELECT 1))AS ID FROM table1),
     T2 AS (select Address1,StreetName,ROW_NUMBER()OVER(ORDER BY (SELECT 1))AS ID FROM table1)

SELECT T1.EMPNAME,
       T2.StreetName,
       T2.Address1 
 FROM T1 FULL JOIN T2 ON(T1.ID=T2.ID)

Posted 13-Dec-17 22:16pm

Santosh kumar Pithani

Updated 14-Dec-17 17:40pm

v2

Comments

CHill60 14-Dec-17 11:11am

I'm guessing you answered this because of Solution 2 bringing this back into the active posts list after 3 years, but you're actually selecting stuff from a single table via two CTEs (not two tables) so not really on the mark anyway

Santosh kumar Pithani 15-Dec-17 1:48am

Two table returning from CTE(derived tables also possible) and joining data using row_num(id) as relation to avoid M*N rows(duplicate rows) ,i trust this query make sense

CHill60 15-Dec-17 5:10am

Point - missed. You are querying on the same table so your ORDER BY works. If the tables are unrelated then ORDER BY will not necessarily work, you can get rubbish. You are not answering the question that was asked, and it was fully answered 3 years ago!