SQL Query simple question

Question

Just wondering if I'm missing something here:

$sql = "SELECT DISTINCT usr.id, 
CONCAT(usr.username, '".PHP_EOL."' ) as username 
FROM #__ads AS a 
RIGHT JOIN #__users 
WHERE gid= '19' AS usr  
    ON usr.id = a.userid 
ORDER BY usr.username";

It works when I remove the WHERE gid = '19' part, is it in the wrong place? The gid column only exists in the __users table.

Thank you!

Edit: got it, just needed to put the Where clause right before the ORDER BY.

regality · Accepted Answer · 2011-09-26 22:59:46Z

1

The where clause must come after any joins:

$sql = "SELECT DISTINCT usr.id
             , CONCAT(usr.username, '".PHP_EOL."' ) as username
        FROM #__ads AS a
        RIGHT JOIN #__users AS usr
        ON usr.id = a.userid 
        WHERE gid= '19'
        ORDER BY usr.username";

answered Sep 26, 2011 at 22:59

regality

6,5846 gold badges31 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Smith · Accepted Answer · 2011-09-26 22:58:37Z

0

Assuming you want just the row(s) from #__users where usr.gid= '19'

SELECT DISTINCT usr.id, 
                CONCAT(usr.username, '".PHP_EOL."' ) as username 
FROM #__ads AS a 
RIGHT JOIN #__users as usr  ON usr.id = a.userid  
WHERE usr.gid= '19' 
ORDER BY usr.username

The alternative

SELECT DISTINCT usr.id, 
                CONCAT(usr.username, '".PHP_EOL."' ) as username 
FROM #__ads AS a 
RIGHT JOIN #__users as usr  ON usr.id = a.userid  
                            AND usr.gid= '19' 
ORDER BY usr.username

Would give you all rows from #__users but only join on those matching usr.gid= '19'

answered Sep 26, 2011 at 22:58

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Collectives™ on Stack Overflow

SQL Query simple question

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related