CODEWITHSUNDEEP
sqloracle11g
user1696885
user1696885

Reputation: 45

Join Two Select Queries having where clause

SELECT JIVE_USER_ID,
       EMAIL_ID, 
       USER_ID 
FROM VIK_USER WHERE EMAIL_ID='[email protected]';

Result is

JIVE_USER_ID    EMAIL_ID              USER_ID
11222            [email protected]     User

Another query is

SELECT  VIK_USER_PROFILE.FIRST_NAME,
        VIK_USER_PROFILE.LAST_NAME,
        VIK_USER.EMAIL_ID 
FROM VIK_USER,VIK_USER_PROFILE 
WHERE VIK_USER_PROFILE.USER_ID=VIK_USER.USER_ID;

Result is

FIRST_NAME    LAST_NAME     EMAIL_ID
Ray            User        [email protected]

I want to get the result using a single block statement.How can I join these two select statements into one.

Thank You.

Upvotes: 3

Views: 1121

Answers (2)

Harshini
Harshini

Reputation: 499

The answer above joins the tables and filters based on where or and clause. This may be inefficient for big tables. Instead filter the table for particular id and then join.

Upvotes: 0

Jensen Ching
Jensen Ching

Reputation: 3164

select VIK_USER.JIVE_USER_ID,
       VIK_USER.EMAIL_ID, 
       VIK_USER.USER_ID,
       VIK_USER_PROFILE.FIRST_NAME,
       VIK_USER_PROFILE.LAST_NAME,
       VIK_USER.EMAIL_ID
from VIK_USER, VIK_USER_PROFILE
where VIK_USER_PROFILE.USER_ID=VIK_USER.USER_ID
and VIK_USER.EMAIL_ID='[email protected]'

To elaborate, VIK_USER and VIK_USER_PROFILE are in an INNER JOIN (the comma is just a shortcut).

The join condition is VIK_USER_PROFILE.USER_ID=VIK_USER.USER_ID. JOINs have join conditions, usually on columns which share the same values across two tables.

Alternatively, the exact same query can be written as such:

select *
from VIK_USER
inner join VIK_USER_PROFILE on VIK_USER_PROFILE.USER_ID=VIK_USER.USER_ID
where VIK_USER.EMAIL_ID='[email protected]';

Upvotes: 3

Related Questions