[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

SQL JOIN when no columns are in common

Just wondering how to join 2 tables when no columns are in common.

Like

Table A => name, place, item
TABLE B=> type, rule , value

Now I want to join table A and B. Item in Table A has same data and datatype same as type in table B. But their column names are not same.
0
pal2k
Asked:
pal2k
4 Solutions
 
ajexpertCommented:
TRY UNION ALL

SELECT NAME, PLACE, ITEM
FROM TABLEA
UNION ALL
SELECT TYPE_RULE_VALUE
FROM TABLEB
0
 
pal2kAuthor Commented:
thks...

Wondering if UNION will be still good if I need to add a WHERE clause in TABLE A by column Item
0
 
pal2kAuthor Commented:
also the no of columns are not same in both tables...
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
NewDan526Commented:
Column names don't have to be the same when joining.  The USING clause or a "NATURAL JOIN" use the column names, but you should use the "ON" clause here.


SELECT a.*, b.*
  FROM a JOIN b
    ON (a.item = b.type)
 WHERE item = "xxx"
;
0
 
ajexpertCommented:
There should not be any issues if you include WHERE clause in TABLEA.

You can still work with UNION ALL even if number of columns in both tables are not same, provided you make sure that you list the column names AND it should have same datatypes in TABLEA and TABLEB
0
 
ajexpertCommented:
Let me ask you the following before we comment.

1.  Is the data in TABLEA and TABLEB is same even though the column names are different?  If yes, which columns in TABLEA and TABLEB have the same data?

2.  How you exepect the output? listing all columns from TABLEA and TABLEB or just combining the records from TABLEA and TABLEB?
0
 
SharathData EngineerCommented:
Try CROSS JOIN.
SELECT *
  FROM Table_A, Table_B

Open in new window

0
 
jaiminpsoniCommented:
Select * from table_A a , table_B b where a.item = b.type;

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now