CODEWITHSUNDEEP
sqlinner-join
coderatlarge
coderatlarge

Reputation: 649

SQL where clause getting ignored

I have 2 tables:

pf_master
temp_table

pf_master layout is

username
pf_name
pf_id

and the data in pf_master is

username  pf_name  pf_id
-------------------------
xyz       General  3
xyz       Overall  0
abc       General  3

temp table layout is

pf_id
inv_val
curr_val
xirr
loginid
timestamp

and the values in temp table are

pf_id   inv_val   curr_val    xirr    loginid    timestamp
--------------------------------------------------------------------
0       100       200         10%     xyz        2019-04-06 12:21:28
3       100       300         11%     xyz        2019-04-06 12:21:40

I want to fetch all records of 'abc' user along with the pf_name. But 'abc' does not have any records in temp_table so ideally this query should return a NULL. But it is returning the value of 'xyz' user.

My SQL is:

SELECT 
    pf_master.pf_name, 
    temp_table.inv_val, temp_table.curr_val, temp_table.xirr 
FROM 
    temp_table 
INNER JOIN 
    pf_master ON pf_master.pf_id = temp_table.pf_id 
WHERE 
    pf_master.username = 'abc' 
ORDER BY 
    pf_master.pf_id ASC

Where am I going wrong?

Upvotes: 0

Views: 68

Answers (2)

lapwingg
lapwingg

Reputation: 112

First, fetch all records from pf_master where username = 'abc' 

SELECT * FROM pf_master WHERE username = 'abc'

Then change INNER_JOIN clause in your question, instead of entire table pf_master you should use only result from fetching

Something like this:

 INNER JOIN
 (SELECT * FROM pf_master WHERE username = 'abc') AS tmp ON tmp.pf_id = tmp_table.pf_id

Upvotes: 1

Jim Castro
Jim Castro

Reputation: 894

You need to include the username as well as the pf_id in your join as pf_id by itself isn't enough to identify a unique user. 

 SELECT 
        pf_master.pf_name, 
        temp_table.inv_val, 
        temp_table.curr_val,
        temp_table.xirr 
    FROM temp_table t
    JOIN pf_master  p 
      ON p.pf_id = t.pf_id 
     AND p.username = t.loginid
    WHERE 
        p.username = 'abc' 
    ORDER BY 
        p.pf_id ASC

Upvotes: 1

Related Questions