Getting data from two tables only under certain conditions

[alhena]

I have a database with two tables. Table1 has my users and their data, table2 has only some of these users, and some other data.

 

I want to select my data from both tables, but it's supposed to check table2 ONLY if the user is actually entered in both tables, otherwise keep checking table1. Can I do this with a single query? Or do I have to select from table1 first, then separately check whether the userIDs I just pulled from table1 happen to exist in table2, as well? Basically, I want to tell MySQL to "look for the following users in table1 and table2, but please check table2 ONLY if there actually is an entry for that user in table2".

 

I've tried all sorts of combinations with CASE, IF(), IFNULL(), NULLIF(), but neither is working as intended. I had hoped this would work:

 

SELECT table1.userid FROM table1, table2 WHERE table1.foo='$var' AND table1.bar='1' AND (CASE WHEN table1.userid=table2.userid THEN table2.activated='0' END)

 

But it doesn't. It only gets the data from table2, and ignores any that only appears in table1.

 

Any ideas?

 

I have MySQL version 5.1.49

[Barand]

Does this give the result you want

 

SELECT table1.userid, table2.activated
FROM table1
LEFT JOIN table2 
    ON table1.userid = table2.userid 
    AND table2.activated = 0;

[alhena]

Unfortunately, it does not work, no. The original WHERE needs to go in, or it simply gets me all entries from table1. I'm unfamiliar with LEFT JOIN, do I put the WHERE after it? I just tried it, and seems to completely ignore the JOIN part, meaning it gets me the same results from table1 as when I leave the join out.

 

SELECT table1.userid, table2.activated
FROM table1
LEFT JOIN table2 
    ON table1.userid = table2.userid 
    AND table2.activated = 0
WHERE table1.foo='$var' AND table1.bar='1'

[alhena]

No further ideas?

[Barand]

The query you just posted will get all rows from table 1 where foo and bar meet your WHERE conditions. If there is a matching row in table 2 it will also show the activated value (0) from that table 2 row. If there is no such table 2 row the activated column will contain NULL.

 

What do you want ?

[alhena]

Okay, I'll try a different way of explaining what I want... IF there is an entry in table2, and table2.activated happens to be 1 (not 0), it's not supposed to show that entry in the results at all. That's the problem I'm having, because right now it does. I want to get the data from table1 that fits the WHERE conditions, but it's supposed to exclude an entry if it also shows up in table2 and has the table2.activated field set to 1. Please let me know if I'm not being clear enough, as it's difficult to explain.

[Barand]

try

SELECT table1.userid, table2.activated
FROM table1
LEFT JOIN table2 
    ON table1.userid = table2.userid 
    AND table2.activated = 1
WHERE table1.foo='$var' AND table1.bar='1' AND table2.activated IS NULL

[alhena]

No, it's not working. It was giving me some really weird results that took me a while to figure out. First, table2.activated is supposed to be 0, not 1, but that's something I can easily fix.

 

However, due to the JOIN part, it's now giving me NULL for those entries that do not appear in table2, as well as those that appear, but have table2.activated set to 1. The latter is causing the problem, because it makes entries appear in the results that shouldn't be appearing. So, in order for this to work, I need a way to make "table2.activated=1" entries not show up as NULL.

 

Does this make sense?

 

Here is the corrected (but not working) query:

 

SELECT table1.userid, table2.activated
FROM table1
LEFT JOIN table2 
    ON table1.userid = table2.userid 
    AND table2.activated = '0'
WHERE table1.foo='$var' AND table1.bar='1' AND table2.activated IS NULL

[alhena]

Nevermind! I had the wrong thought process. It works perfectly the way you posted it.

 

Thanks for your help!