SQL newbie query issue

Question

I have these two tables:

Table1

Itemid      Username            Rate        Opinion     
0944947     pooh_sweety_70      3.2         Negative
0468569     pooh_sweety_70                  Positive
0411008     liamvdheuvel        2.1         Negative
0468569     liamvdheuvel        3.4         Negative
1375666     liamvdheuvel        1.5         Negative
0903747     liamvdheuvel        5.5         Negative
0412142     rzajac              4.8         Negative
2267998     rzajac              9.2         Positive
1074638     rzajac              7.4         Positive
1182345     rzajac              6.1         Negative
0468569     rzajac              3.3         Negative
1856010     michaelsmith01      9.9         Positive
1486217     michaelsmith01                  Positive
1300854     michaelsmith01      2.4         Negative
2267998     pooh_sweety_70      8.4         Positive
2911666     aswilliams40        8.7         Positive
1074638     aswilliams40        9.4         Positive
0468569     aswilliams40        8.2         Positive

Table2

Username            Password
aswilliams40        pantsftw
liamvdheuvel        qwascoolzuiop
michaelsmith01      ilovejessica
rzajac              toocoolforscool
pooh_sweety_70      legendarymythic

And I'd like to find the users whose passwords contain the word 'cool' somewhere on it, and that user must also always have left a Negative opinion on an item if they rated said item. Unrated items dont matter.

I tried putting a query together, but it doesnt really work :/

SELECT Username FROM Table1, Table2
    WHERE Table1.Username = Table2.Username AND
    Table2.Password LIKE '%cool%' AND
    Table1.Rate IS NOT NULL AND
    Table1.Opinion = 'Negative';

So basically I want the only output to be 'liamvdheuvel'

Answer 1

SELECT Table2.Username 
FROM   Table1, 
       Table2 
WHERE  Table1.Username = Table2.Username 
       AND Table2.Password LIKE '%cool%' 
       AND Table1.Rate IS NOT NULL 
       AND Table1.Opinion = 'Negative'; 

The problem was that you didn't specify what table you wanted to get the 'Username'.

If you don't want get repeated usernames, add the DISTINCT:

SELECT DISTINCT Table2.Username 
FROM   Table1, 
       Table2 
WHERE  Table1.Username = Table2.Username 
       AND Table2.Password LIKE '%cool%' 
       AND Table1.Rate IS NOT NULL 
       AND Table1.Opinion = 'Negative'; 

If you don't want users that have negative AND positive opinions, try this:

SELECT DISTINCT Table2.Username 
FROM   Table1, 
       Table2 
WHERE  Table1.Username = Table2.Username 
       AND Table2.Password LIKE '%cool%' 
       AND Table1.Rate IS NOT NULL 
       AND Table1.Opinion = 'Negative' 
       AND Table2.Username NOT IN (SELECT Table1.Username 
                                   FROM   Table1 
                                   WHERE  Table1.Opinion = 'Positive'); 

Answer 2

SELECT DISTINCT Table1.Username 
FROM Table1
LEFT JOIN Table2
ON table1.Username=table2.Username
Where
Table2.Password LIKE '%cool%' AND
Table1.Rate IS NOT NULL AND
Table1.Opinion = 'Negative';

For optimization you need to remove username from table1(give this table a proper name and add userID to table2(name it properly as well)).

Table2
------

UserId    **Username**         **Password**
1         aswilliams40        pantsftw
2         liamvdheuvel        qwascoolzuiop
3         michaelsmith01      ilovejessica
4         rzajac              toocoolforscool
5         pooh_sweety_70      legendarymythic

Table1

**Itemid**      **UserID**           **Rate**        **Opinion**     
  0944947        5                         3.2           Negative
  0468569        5                                      Positive
  0411008        2                        2.1           Negative