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 Go to next message
ocpsujon
Messages: 6
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 #612487 is a reply to message #612486] Sun, 20 April 2014 02:28 Go to previous messageGo to next message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

Where you have two or more rows with the same department_id, which row would you want to keep? Without knowing the rule for that, no solution is possible.
Re: How to join two table with no duplicate [message #612488 is a reply to message #612486] Sun, 20 April 2014 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Given there is a referential dependency between employees and departments the solution is:
SELECT department_id , department_name FROM departments;

Smile

Re: How to join two table with no duplicate [message #612489 is a reply to message #612488] Sun, 20 April 2014 02:48 Go to previous messageGo to next message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
But hr.employees.department_id is nullable!

[Updated on: Sun, 20 April 2014 02:48]

Report message to a moderator

Re: How to join two table with no duplicate [message #612490 is a reply to message #612486] Sun, 20 April 2014 03:18 Go to previous messageGo to next message
ocpsujon
Messages: 6
Registered: April 2014
Location: Gazipur, Bangladesh
Junior Member

Thanks, I want to keep that row which has data. That means it will be merged with null values of employees dummy column "department_name"
Re: How to join two table with no duplicate [message #612491 is a reply to message #612490] Sun, 20 April 2014 03:31 Go to previous messageGo to next message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
So you want to keep the the rows from departments, and discard the rows from employees? If that is the case, why are you querying employees at all?

I assume this is a college homework question?
Re: How to join two table with no duplicate [message #612492 is a reply to message #612491] Sun, 20 April 2014 04:18 Go to previous messageGo to next message
ocpsujon
Messages: 6
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 #612493 is a reply to message #612492] Sun, 20 April 2014 04:38 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2405
Registered: May 2013
Location: World Wide on the Web
Senior Member
All you need to do is join both the departments and warehouses tables and get your required output. Have a look at Joins. Think which join to use and come back with what you try.
Re: How to join two table with no duplicate [message #612494 is a reply to message #612492] Sun, 20 April 2014 04:44 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2405
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 #612495 is a reply to message #612492] Sun, 20 April 2014 04:51 Go to previous messageGo to next message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
Your table WAREHOUSES does not exist in the HR demo schema. You need to provide the CREATE TABLE statement and some INSERTs for it.
Re: How to join two table with no duplicate [message #612496 is a reply to message #612494] Sun, 20 April 2014 06:31 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
Lalit Kumar B wrote on Sun, 20 April 2014 05:44
Simplest way is to use MAX for the two nullable columns(department and warehouse) and GROUP BY LOCATION_ID to merge the two rows.


Simplest way would be discarding OP's query Wink and using (full outer if location_id can be present in one table but not the other) join instead of union:

SELECT  NVL(d.location_id,w.location_id) location_id,
        department_name "Department",
        warehouse_name "Warehouse"
  FROM      departments d
        FULL OUTER JOIN
            warehouses w
          ON (
              d.location_id = w.location_id
             )
/


SY.
Re: How to join two table with no duplicate [message #612497 is a reply to message #612496] Sun, 20 April 2014 07:22 Go to previous messageGo to next message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
SY, is your query assuming that location_id is unique? If there are several departments and/or warehouses in the same location, will it work?
Re: How to join two table with no duplicate [message #612498 is a reply to message #612497] Sun, 20 April 2014 07:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
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 Go to previous messageGo to next message
John Watson
Messages: 4610
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
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 Go to previous messageGo to next message
John Watson
Messages: 4610
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.
Re: How to join two table with no duplicate [message #612508 is a reply to message #612496] Sun, 20 April 2014 09:30 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2405
Registered: May 2013
Location: World Wide on the Web
Senior Member
Solomon Yakobson wrote on Sun, 20 April 2014 17:01
Lalit Kumar B wrote on Sun, 20 April 2014 05:44
Simplest way is to use MAX for the two nullable columns(department and warehouse) and GROUP BY LOCATION_ID to merge the two rows.


Simplest way would be discarding OP's query Wink and using (full outer if location_id can be present in one table but not the other) join instead of union.


I agree. In my first post I mentioned the use of join Smile . I gave the solution to max in second post so that OP could use his union query as the data to get required output using max.
Re: How to join two table with no duplicate [message #612522 is a reply to message #612494] Sun, 20 April 2014 22:34 Go to previous message
ocpsujon
Messages: 6
Registered: April 2014
Location: Gazipur, Bangladesh
Junior Member

Many many Thanks to you Mr. Lalit Kumar B. It's working. I've got my answer. Thanks a lot again.
Previous Topic: case insensitive search UPPER/LOWER() vs. NLSSORT
Next Topic: Data Type Number
Goto Forum:
  


Current Time: Wed Oct 01 00:30:57 CDT 2014

Total time taken to generate the page: 0.10019 seconds