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.
pal2kAsked:
Who is Participating?
 
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:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pal2kAuthor Commented:
also the no of columns are not same in both tables...
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.