CODEWITHSUNDEEP
sql
Omar
Omar

Reputation: 27

SQL- How to select data from two different table?

I am working to select data from 2 different table but I can't figured out. If I use INNER JOIN it show noting. Any help are welcome and Thanks.

My First table:

CREATE TABLE P_N( 
PN_ID int NOT NULL,
PN VARCHAR (1000),
primary key (PN_ID)
);

My second Table:

CREATE TABLE NAME (
NAME_ID VARCHAR(60) PRIMARY key,
NAME  VARCHAR (40)
);

My select code :

SELECT DISTINCT NAME.NAME_ID,  PN.PN_ID
FROM NAME
FULL JOIN P_N 
ON PN.PN =NAME.NAME_ID;

If I use left or full Join this is the result:

NAME_ID      PN_ID
nm0006300    NULL
nm0006400    NULL
nm0006500    NULL
nm0006600    NULL
nm0006700    NULL

AND if I use right join:

NAME_ID      PN_ID
null        921691
null        921692
null        921693
null        921694

This is what I want the result to looks like For example:

NAME_ID        PN_ID
nm0006300      921691
nm0006400      921692
nm0006500      921693
nm0006600      921694

Upvotes: 1

Views: 44

Answers (2)

Abdelrahman Gamal
Abdelrahman Gamal

Reputation: 510

try this 

select DISTINCT NAME.NAME_ID,  PN.PN_ID
from NAME,P_N as PN
where PN.PN =NAME.NAME_ID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You don't seem to have a JOIN key. You can add one with ROW_NUMBER():

SELECT n.NAME_ID, PN.PN_ID
FROM (SELECT n.*, ROW_NUMBER() OVER (ORDER BY NAME_ID) as seqnum
      FROM NAME n
     ) n JOIN
     (SELECT pn.*, ROW_NUMBER() OVER (ORDER BY PN) as seqnum
      FROM P_N pn
     ) pn
     ON PN.seqnum = n.seqnum;

Upvotes: 1

Related Questions