Solved

SQL JOIN when no columns are in common

Posted on 2011-03-06
8
455 Views
Last Modified: 2012-05-11
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
Comment
Question by:pal2k
8 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 35047227
TRY UNION ALL

SELECT NAME, PLACE, ITEM
FROM TABLEA
UNION ALL
SELECT TYPE_RULE_VALUE
FROM TABLEB
0
 

Author Comment

by:pal2k
ID: 35047293
thks...

Wondering if UNION will be still good if I need to add a WHERE clause in TABLE A by column Item
0
 

Author Comment

by:pal2k
ID: 35047483
also the no of columns are not same in both tables...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Accepted Solution

by:
NewDan526 earned 125 total points
ID: 35048735
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 35048772
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
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 125 total points
ID: 35049057
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
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 125 total points
ID: 35050314
Try CROSS JOIN.
SELECT *
  FROM Table_A, Table_B

Open in new window

0
 
LVL 5

Assisted Solution

by:jaiminpsoni
jaiminpsoni earned 125 total points
ID: 35060312
Select * from table_A a , table_B b where a.item = b.type;

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Get the parent node - XMLTYPE 9 98
SQL Query 34 115
Shredding xml into an oracle 11g Database 2 58
join a table with user_tab_columns in oracle 3 48
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question