0

I have 2 tables that I join using an ID. I want all the data from my main table to show and match if that ID is in table #2 to show a few more columns in my output. That currently works with 

select table1.id, table1.name, table1.phone, table1.address, 
table2.loyalcustomer, table2.loyaltynumb, table2.loyaltysince from table1
left join table2
ON table1.id = table2.table1id

What I'm trying to do is the same thing, but add a WHERE clause to table2.loyalcustomer != 'Yes'. When I do that, it doesn't return all the data from my main table (table1), but instead only shows what matches between table1 and table2. Also, table2 does not have all the info, only what was inserted into the table.

select table1.id, table1.name, table1.phone, table1.address, 
table2.loyalcustomer, table2.loyaltynumb, table2.loyaltysince from table1 
left join table2
ON table1.id = table2.table1id
WHERE table2.loyalcustomer != 'Yes'

Been reading about different joins but what i've been reading is that my where statement may be contradicting my join and I'm not sure how to resolve that.

SQL DB: Postgres

Nigel Ren
  • 56,122
  • 11
  • 43
  • 55
yusof
  • 143
  • 2
  • 14
  • Hi. Learn what left join returns: inner join rows plus unmatched left table rows extended by nulls. A where that needs a right table column to be not null after a left join, which you have, removes any rows extended by nulls, ie leaves only inner join rows, ie "turns outer join into inner join". Always know what inner join you want as part of a left join. Also this is an easily-found faq. Always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Also you're unclear, please read & act on [mcve] – philipxy Jun 14 '18 at 19:36
  • Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy Sep 29 '20 at 21:10

2 Answers2

6

The problem is on your WHERE clause. Be carefull with LEFT JOINS !

When you do a LEFT JOIN on a TABLE, this table wont filter the results as if it was an INNER JOIN. This is because you accept your LEFT JOIN TABLE to return entire NULL rows.

However, you are using a COLUMN from your "LEFT JOINED TABLE" in your WHERE clause when you say... "table2.loyalcustomer != 'Yes'" . This clause works when table2.loyalcustomer is not not null but it DOESN'T work if table2.loyalcustomer is NULL.

So here it goes the right way to do it :

select table1.id, ... from table1 left join table2 ON table1.id = table2.table1id and table2.loyalcustomer != 'Yes'

Here it goes an alternative way to do it...

select table1.id, ... from table1 left join table2 ON table1.id = table2.table1id WHERE ISNULL(table2.loyalcustomer, '') != 'Yes'

To resume : NULL != 'Yes' doesn't work. You need something different from null to evaluate your expression.

David
  • 143
  • 8
  • got a question about the ISNULL(table2.loyalcustomer, '') != 'Yes'. You have the contents in the brackets with a comma and quotes. Is that correct? if I put the double quotes, it will break my variable. I also removed , " – yusof Jun 14 '18 at 16:29
  • and got this error Warning: pg_query(): Query failed: ERROR: function isnull(text) does not exist LINE 7: WHERE ISNULL(table2.loyalcustomer) != 'Yes' ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. – yusof Jun 14 '18 at 16:30
  • Also changed it from possible double quotes to 2 single and I got this error message: function isnull(text, unknown) does not exist LINE 7: WHERE ISNULL(table2.loyalcustomer, '') != 'Yes' – yusof Jun 14 '18 at 16:33
  • Sorry, I forgot to say that we are not using the same database. But the idea is the same. I'm using SQLServer. You might have an equivalent ISNULL function in POSTGRE. Otherwise use my first suggestion. – David Jun 14 '18 at 16:47
  • First suggestion we tried earlier and did not work. Second one, I believe ISNULL works. https://www.tutorialspoint.com/postgresql/postgresql_null_values.htm – yusof Jun 14 '18 at 16:49
  • 1
    Also, you could do something like "WHERE table2.loyalcustomer is null OR table2.loyalcustomer != 'Yes' – David Jun 14 '18 at 16:51
0

Try this one man

SELECT table1.id, table1.name, table1.phone, table1.address, 
table2.loyalcustomer, table2.loyaltynumb, table2.loyaltysince FROM users 
LEFT JOIN table2
ON table1.id = table2.table1id
HAVING table2.loyalcustomer != 'Yes'
Lucarnosky
  • 514
  • 4
  • 18
  • I got this error in my php code: table1.id must appear in the GROUP BY clause or be used in an aggregate function. I'm not doing any grouping in my code. – yusof Jun 14 '18 at 16:01
  • That's right, before the HAVING part add this GROUP BY table1.id But be carefull. If in your table2 there is more then 1 match with table 1 it won't display cause is grouped – Lucarnosky Jun 14 '18 at 16:01
  • The GROUP BY error now moved to the second column which is table1.name – yusof Jun 14 '18 at 16:03
  • table2 should not have any duplicates. – yusof Jun 14 '18 at 16:05
  • Warning: pg_query(): Query failed: ERROR: column "table1.name" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select tabl1.id, table1.n... ^ in index.php on line 99 Warning: pg_fetch_array() expects parameter 1 to be resource, boolean given in index.php on line 100 – yusof Jun 14 '18 at 16:07
  • This was the same error that appeared for the ID when I mentioned the grouping error. It just shifted. – yusof Jun 14 '18 at 16:08
  • I'm sorry, but i think i've finished the ideas. The only thing you can try is to add a second field in your group by so you have something like GROUP BY table1.id,table1.name . But again, if you have duplicate name in table1 you will only see just the first record – Lucarnosky Jun 14 '18 at 16:20
  • No worries. It shifted to the column right after that. I appreciate you attempting to help!!! – yusof Jun 14 '18 at 16:22