CODEWITHSUNDEEP
sqlsql-server
user2466648
user2466648

Reputation: 9

how to join two tables in sql server with out duplication

Hi I have two tables A and B

Table A:

Order        Pick up
100          Toronto
100          Mississauga
100          Scarborough

Table B

Order              Drop off
100                Oakvile
100                Hamilton
100                Milton

Please let me know how can I can get this output (ie I just want to join the fields from in B in right hand side of A)

Order    pickup        Dropoff
100      Toronto       oakvile
100      Mississauga   Hamilton
100      Scarborough   Milton

How can I write query for the same I try to join a.rownum = b.rownum but no luck.

Upvotes: 0

Views: 99

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270883

From the use rownum, I'm presuming that you are using Oracle. You can attempt the following:

select a.Order as "order", a.Pickup, b.DropOff
from (select a.*, rownum as seqnum
      from a
     ) a join
     (select b.*, rownum as seqnum
      from b
     ) b
     on a.order = b.order and a.seqnum = b.seqnum;

(This assumes that all orders match up exactly.)

I must emphasize that although this might seem to work (and it should work on small examples), it will not work in general. And, it will not work on data that has deleted records. And, it probably won't work on parallel systems. If you have a small amount of data, I'd suggest dumping it in Excel and doing the work there -- that way, you can see if the pairs make sense.

Also, if you do have a column that specifies the ordering, then basically the same structure will work:

select coalesce(a.Order, b.Order) as "order", a.Pickup, b.DropOff
from (select a.*,
             row_number() over (partition by "order" order by <ordering field>) as seqnum
      from a
     ) a join
     (select b.*,
             row_number() over (partition by "order" order by <ordering field>) as seqnum
      from b
     ) b
     on a.order = b.order and a.seqnum = b.seqnum;

Upvotes: 1

Mike Harris
Mike Harris

Reputation: 869

I'd use a CTE along with the ROW_NUMBER windowing function.

WITH keyed_A AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS id
   ,[Order]
   ,[Pick Up]
  FROM A
), keyed_B AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS id
   ,[Order]
   ,[Drop Off]
  FROM B
)
SELECT
   a.[Pick Up]
  ,b.[Drop Off]
  FROM keyed_A AS a
  INNER JOIN keyed_B AS b
    ON a.id = b.id
;

The CTE can be thought of as a virtual table with an id that crosses the two tables. The OVER clause with the Windowing function ROW_NUMBER can be used to create an id in the CTE. Since we are relying on the physical storage of the records (not a good idea, please add keys to the tables) we can ORDER BY (SELECT NULL) which means just use the order in will be read in.

SQLFiddle to test

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

As OP has not mention any RDBMS

I am taking the liberty for taking SQL SERVER 2008 as his RDBMS. If OP wants the following Query can be converted to any other RDBMS easily.

select A.[Order],
    ROW_NUMBER() OVER(ORDER BY A.[Pick up]) rn1,
    A.[Pick up]
    into A1
FROM A
;
select B.[Order],
    ROW_NUMBER() OVER(ORDER BY B.[Drop off]) rn2,
    B.[Drop off]
    into B1
FROM B
;
Select A1.[Order],
    A1.[Pick up],
    B1.[Drop off]
FROM A1
INNER JOIN B1 on A1.rn1=B1.rn2

SQL FIDDLE to Test

Upvotes: 1

Related Questions