CODEWITHSUNDEEP
sqloracle-databasejoinunique
javaCoder
javaCoder

Reputation: 37

select unique fields on join

CREATE TABLE Face.Person 
   ("LAST_NAME" VARCHAR2(50 BYTE), 
    "TICKET_NUMBER" VARCHAR2(50 BYTE));

insert into Person(last_name,ticket_number) values('johnson','100');
insert into Person(last_name,ticket_number) values('smith','200');

CREATE TABLE Face.Orders 
   ("REGION" VARCHAR2(50 BYTE), 
    "STATE" VARCHAR2(50 BYTE), 
    "A_CODE" VARCHAR2(5 BYTE),
    "STORE_NUM" VARCHAR2(50 BYTE), 
    "TICKET_NUMBER" VARCHAR2(50 BYTE),
    "SEQ" NUMBER(12));

insert into Orders(ticket_number, REGION, STATE, STORE_NUM, A_CODE, SEQ ) values('100', 'S', 'F1', '111', 'Mia', 1);
insert into Orders(ticket_number, REGION, STATE, STORE_NUM, A_CODE, SEQ ) values('100', 'S', 'F1', '222', 'FLL', 2);
insert into Orders(ticket_number, REGION, STATE, STORE_NUM, A_CODE, SEQ ) values('100', 'S', 'F1', '333', 'PBI', 3);
insert into Orders(ticket_number, REGION, STATE, STORE_NUM, A_CODE, SEQ ) values('100', 'S', 'F1', '444', 'BOC', 4);


SELECT A.LAST_NAME, A.TICKET_NUMBER, B.REGION, B.STATE 
FROM PERSON A
  JOIN Orders B ON A.TICKET_NUMBER 
    = (SELECT * FROM Orders WHERE A.TICKET_NUMBER= B.TICKET_NUMBER 
  AND ROWNUM = 1);

Join both tables on ticket Number. Person is a one to many relationship with Orders. Multiple orders exist but just need one Orders record(out of 4) that matches with ticket number from both tables.The reason being is the fields required from the Orders table remain the same for all records with same ticket Number.

Expected result:

Johnson 100 S Fl

Johnson and 100 from person table and S and FL, which remain the same for all records per unique ticket number, are from Orders table

Oracle 12c

Upvotes: 1

Views: 61

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

It's not clear on what basis you are selecting the records from Orders. One option is to simply chose MIN() of region & state with a group by and then join it with Person

WITH b AS (
     SELECT ticket_number,
            MIN(region) AS region,
            MIN(state) AS state
     FROM orders
     GROUP BY ticket_number
) SELECT a.last_name,
         a.ticket_number,
         b.region,
         b.state
  FROM person a
  JOIN b ON a.ticket_number = b.ticket_number;

Demo

Upvotes: 1

Related Questions