Query Producing Slightly Unwanted Results

Hi guys,

I have a query that pulls member photos. Default Y, not Default N. The problem is when the user has not set a default and they are all N… its pulling all the N photos for that user instead of just one. Somehow I’ve broken the query and I’m not sure how.

$result568=mysql_query("SELECT DISTINCT

     u.name, u.age, u.category, u.info1, u.info12, u.lastlogin, u.lastactivity

     ,COALESCE(u2i.url,u2i2.url) url

  FROM

     usertable u

  LEFT OUTER

  JOIN

     imagetable u2i

    ON

     u.name = u2i.name

   AND

     u2i.default = 'Y'

  LEFT OUTER

  JOIN

     imagetable u2i2

    ON

     u.name = u2i2.name

  WHERE

  	(u2i.priv != 1 && u2i.status = 'active' OR u2i2.priv !=1 && u2i2.status = 'active') && u.name IN (" . $picar . ") ORDER BY field(u.name, $picar) LIMIT 30;");

Anyone?

Try this:
You currently have:


$result568=mysql_query("SELECT DISTINCT

     u.name, u.age, u.category, u.info1, u.info12, u.lastlogin, u.lastactivity

     ,COALESCE(u2i.url,u2i2.url) url

  FROM
..... and so on

Change it slightly to:


$sql = "SELECT DISTINCT

     u.name, u.age, u.category, u.info1, u.info12, u.lastlogin, u.lastactivity

     ,COALESCE(u2i.url,u2i2.url) url

  FROM
..... and so on

// and then do:
echo $sql ;
$result568=mysql_query($sql);

Echoing out the sql statement may throw up a PHP error you have made elsewhere.

IF the statement does look fine, the yours is ostensibly an SQL question, hit the little orange flag under your avatar on this question and ask a moderator to move it to the SQL forum, you’ll likely get a better response than here.