Home » SQL & PL/SQL » SQL & PL/SQL » How to join two table with no duplicate
How to join two table with no duplicate [message #612486] |
Sun, 20 April 2014 01:40 |
ocpsujon
Messages: 8 Registered: April 2014 Location: Gazipur, Bangladesh
|
Junior Member |
|
|
I want to join the following 2 tables. But department_id cannot be duplicate. Please help me.
select department_id , NULL department_name
FROM employees
UNION
SELECT department_id , department_name
FROM departments
department_id department_name
------------- ---------------
10 Administration
10
20 Marketing
20
30 Purchasing
30
40 Human Resources
40
50 Shipping
50
[Updated on: Sun, 20 April 2014 01:48] Report message to a moderator
|
|
|
|
|
|
|
|
Re: How to join two table with no duplicate [message #612492 is a reply to message #612491] |
Sun, 20 April 2014 04:18 |
ocpsujon
Messages: 8 Registered: April 2014 Location: Gazipur, Bangladesh
|
Junior Member |
|
|
I am sorry that I can't make you understand what I need. It's a dummy query. if I solve it, then I can solve a complex big query. I think the another following example can make you read my mind.
SELECT location_id,
department_name "Department",
To_char(NULL) "Warehouse" --(Dummy Column)
FROM departments
UNION
SELECT location_id,
To_char(NULL) "Department", --(Dummy Column)
warehouse_name
FROM warehouses;
Result shows:
LOCATION_ID Department Warehouse
----------- --------------------- --------------------------
1400 IT
1400 Southlake, Texas
1500 Shipping
1500 San Francisco
But I want this result:
LOCATION_ID Department Warehouse
----------- --------------------- --------------------------
1400 IT Southlake, Texas
1500 Shipping San Francisco
I need LOCATION_ID will be unique and others columns data replaced with null.
[Updated on: Sun, 20 April 2014 04:31] Report message to a moderator
|
|
|
|
Re: How to join two table with no duplicate [message #612494 is a reply to message #612492] |
Sun, 20 April 2014 04:44 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
ocpsujon wrote on Sun, 20 April 2014 14:48
Result shows:
LOCATION_ID Department Warehouse
----------- --------------------- --------------------------
1400 IT
1400 Southlake, Texas
1500 Shipping
1500 San Francisco
But I want this result:
LOCATION_ID Department Warehouse
----------- --------------------- --------------------------
1400 IT Southlake, Texas
1500 Shipping San Francisco
Simplest way is to use MAX for the two nullable columns(department and warehouse) and GROUP BY LOCATION_ID to merge the two rows.
NOTE : Query not tested.
with test_temp as(
SELECT location_id,
department_name "Department",
To_char(NULL) "Warehouse" --(Dummy Column)
FROM departments
UNION
SELECT location_id,
To_char(NULL) "Department", --(Dummy Column)
warehouse_name
FROM warehouses) -- Your data
select
location_id,
,max(Department) as "Department"
,max(Warehouse) as "Warehouse"
from test_temp
group by location_id
[Edit : Provided a query, though not tested]
[Updated on: Sun, 20 April 2014 04:50] Report message to a moderator
|
|
|
|
|
|
Re: How to join two table with no duplicate [message #612498 is a reply to message #612497] |
Sun, 20 April 2014 07:35 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Why Not? In fact, location_id is not unique in OE.DEPARTMENTS:
SQL> SELECT NVL(d.location_id,w.location_id) location_id,
2 department_name "Department",
3 warehouse_name "Warehouse"
4 FROM oe.departments d
5 FULL OUTER JOIN
6 oe.warehouses w
7 ON (
8 d.location_id = w.location_id
9 )
10 ORDER BY 1,2,3
11 /
LOCATION_ID Department Warehouse
----------- ------------------------------ -----------------------------------
1400 IT Southlake, Texas
1500 Shipping San Francisco
1600 New Jersey
1700 Accounting Seattle, Washington
1700 Administration Seattle, Washington
1700 Benefits Seattle, Washington
1700 Construction Seattle, Washington
1700 Contracting Seattle, Washington
1700 Control And Credit Seattle, Washington
1700 Corporate Tax Seattle, Washington
1700 Executive Seattle, Washington
LOCATION_ID Department Warehouse
----------- ------------------------------ -----------------------------------
1700 Finance Seattle, Washington
1700 Government Sales Seattle, Washington
1700 IT Helpdesk Seattle, Washington
1700 IT Support Seattle, Washington
1700 Manufacturing Seattle, Washington
1700 NOC Seattle, Washington
1700 Operations Seattle, Washington
1700 Payroll Seattle, Washington
1700 Purchasing Seattle, Washington
1700 Recruiting Seattle, Washington
1700 Retail Sales Seattle, Washington
LOCATION_ID Department Warehouse
----------- ------------------------------ -----------------------------------
1700 Shareholder Services Seattle, Washington
1700 Treasury Seattle, Washington
1800 Marketing Toronto
2000 Beijing
2100 Bombay
2200 Sydney
2400 Human Resources
2500 Sales
2700 Public Relations
3200 Mexico City
32 rows selected.
SQL>
SY.
|
|
|
Re: How to join two table with no duplicate [message #612499 is a reply to message #612498] |
Sun, 20 April 2014 07:56 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The problem comes if location_id is not unique in both tables:orclz> update warehouses set location_id=1700 where location_id=1400;
1 row updated.
orclz> SELECT NVL(d.location_id,w.location_id) location_id,
2 department_name "Department",
3 warehouse_name "Warehouse"
4 FROM departments d
5 FULL OUTER JOIN
6 warehouses w
7 ON (
8 d.location_id = w.location_id
9 );
LOCATION_ID Department Warehouse
----------- ------------------------------ -----------------------------------
1700 Administration Seattle, Washington
1700 Administration Southlake, Texas
1800 Marketing Toronto
1700 Purchasing Seattle, Washington
1700 Purchasing Southlake, Texas
2400 Human Resources
1500 Shipping San Francisco
1400 IT
2700 Public Relations
2500 Sales
1700 Executive Seattle, Washington
1700 Executive Southlake, Texas
1700 Finance Seattle, Washington
1700 Finance Southlake, Texas
1700 Accounting Seattle, Washington
1700 Accounting Southlake, Texas
1700 Treasury Seattle, Washington
1700 Treasury Southlake, Texas
1700 Corporate Tax Seattle, Washington
1700 Corporate Tax Southlake, Texas
1700 Control And Credit Seattle, Washington
1700 Control And Credit Southlake, Texas
1700 Shareholder Services Seattle, Washington
1700 Shareholder Services Southlake, Texas
1700 Benefits Seattle, Washington
1700 Benefits Southlake, Texas
1700 Manufacturing Seattle, Washington
1700 Manufacturing Southlake, Texas
1700 Construction Seattle, Washington
1700 Construction Southlake, Texas
1700 Contracting Seattle, Washington
1700 Contracting Southlake, Texas
1700 Operations Seattle, Washington
1700 Operations Southlake, Texas
1700 IT Support Seattle, Washington
1700 IT Support Southlake, Texas
1700 NOC Seattle, Washington
1700 NOC Southlake, Texas
1700 IT Helpdesk Seattle, Washington
1700 IT Helpdesk Southlake, Texas
1700 Government Sales Seattle, Washington
1700 Government Sales Southlake, Texas
1700 Retail Sales Seattle, Washington
1700 Retail Sales Southlake, Texas
1700 Recruiting Seattle, Washington
1700 Recruiting Southlake, Texas
1700 Payroll Seattle, Washington
1700 Payroll Southlake, Texas
2000 Beijing
2200 Sydney
1600 New Jersey
2100 Bombay
3200 Mexico City
53 rows selected.
orclz>
(I found the WAREHOUSES table)
|
|
|
Re: How to join two table with no duplicate [message #612500 is a reply to message #612499] |
Sun, 20 April 2014 08:26 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
What problem?
SQL> SELECT NVL(d.location_id,w.location_id) location_id,
2 department_name "Department",
3 warehouse_name "Warehouse"
4 FROM oe.departments d
5 FULL OUTER JOIN
6 ( select location_id,
7 warehouse_name
8 from oe.warehouses
9 union all
10 select location_id,
11 'Atlantis'
12 from oe.warehouses
13 ) w
14 ON (
15 d.location_id = w.location_id
16 )
17 ORDER BY 1,2,3
18 /
LOCATION_ID Department Warehouse
----------- ------------------------------ -----------------------------------
1400 IT Atlantis
1400 IT Southlake, Texas
1500 Shipping Atlantis
1500 Shipping San Francisco
1600 Atlantis
1600 New Jersey
1700 Accounting Atlantis
1700 Accounting Seattle, Washington
1700 Administration Atlantis
1700 Administration Seattle, Washington
1700 Benefits Atlantis
LOCATION_ID Department Warehouse
----------- ------------------------------ -----------------------------------
1700 Benefits Seattle, Washington
1700 Construction Atlantis
1700 Construction Seattle, Washington
1700 Contracting Atlantis
1700 Contracting Seattle, Washington
1700 Control And Credit Atlantis
1700 Control And Credit Seattle, Washington
1700 Corporate Tax Atlantis
1700 Corporate Tax Seattle, Washington
1700 Executive Atlantis
1700 Executive Seattle, Washington
LOCATION_ID Department Warehouse
----------- ------------------------------ -----------------------------------
1700 Finance Atlantis
1700 Finance Seattle, Washington
1700 Government Sales Atlantis
1700 Government Sales Seattle, Washington
1700 IT Helpdesk Atlantis
1700 IT Helpdesk Seattle, Washington
1700 IT Support Atlantis
1700 IT Support Seattle, Washington
1700 Manufacturing Atlantis
1700 Manufacturing Seattle, Washington
1700 NOC Atlantis
LOCATION_ID Department Warehouse
----------- ------------------------------ -----------------------------------
1700 NOC Seattle, Washington
1700 Operations Atlantis
1700 Operations Seattle, Washington
1700 Payroll Atlantis
1700 Payroll Seattle, Washington
1700 Purchasing Atlantis
1700 Purchasing Seattle, Washington
1700 Recruiting Atlantis
1700 Recruiting Seattle, Washington
1700 Retail Sales Atlantis
1700 Retail Sales Seattle, Washington
LOCATION_ID Department Warehouse
----------- ------------------------------ -----------------------------------
1700 Shareholder Services Atlantis
1700 Shareholder Services Seattle, Washington
1700 Treasury Atlantis
1700 Treasury Seattle, Washington
1800 Marketing Atlantis
1800 Marketing Toronto
2000 Atlantis
2000 Beijing
2100 Atlantis
2100 Bombay
2200 Atlantis
LOCATION_ID Department Warehouse
----------- ------------------------------ -----------------------------------
2200 Sydney
2400 Human Resources
2500 Sales
2700 Public Relations
3200 Atlantis
3200 Mexico City
61 rows selected.
SQL>
SY.
|
|
|
Re: How to join two table with no duplicate [message #612501 is a reply to message #612500] |
Sun, 20 April 2014 08:35 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
If you join on a column that has multiple occurrences of the same value in both tables you are multiplying the rows.
But in any case, this is completely wrong for OP: he wants only one occurrence of each location_id. And since he has not supplied the rule that should be applied to select any one row, there can be no solution.
|
|
|
|
|
Goto Forum:
Current Time: Fri Mar 29 00:42:26 CDT 2024
|