CODEWITHSUNDEEP
sqloracle-database
Developer404
Developer404

Reputation: 5972

Join Query which should return one row

I need to write a query which will retrieve me the fullname (available in fullname) of the ownerid and creatorid. Since both are referring the same table, it is retrieving me two rwo. But I need fullname for both ownerid and creatorid fullname in one row. How to do this? owner id and creator id is the reference name in userdetail table.

create table page(page_key,page_number,ownerid,creatorid)

create table userdetail(id,fullname,phonenumber)

Upvotes: 1

Views: 45

Answers (1)

John Woo
John Woo

Reputation: 263803

you need to join table userdetail twice since you want to get the fullname of two ids in you table page

SELECT  a.*, 
        b.fullName as OwnerName,
        c.Fullname as CreatorName
FROM    page a
        INNER JOIN userdetail b
            ON a.ownerid = b.id
        INNER JOIN userdetail c
            ON a.creatorID = c.id

One more thing, if ownerid and creatorID can be null, use LEFT JOIN instead of INNER JOIN.

Upvotes: 1

Related Questions