Modifying a JOIN to include another table

Databases
1

Hi guys. I have this code that pulls user info and using COALESCE it pulled the default image from the user’s photos. It works great but I now want to modify it to include another table named “unlocked” where u.user_id = unlocked.id

I’m not sure how to do this as the join is already very complicated for my experience level. My attempts fail :slight_smile:

SELECT
     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') group by u.name DESC LIMIT 10
2

in your first post you said you wanted where u.user_id = unlocked.id, but your subquery uses name?

it appears that the GROUP BY clause is intended to return only one image for each user, which would be a random one, so i’ve pushed that down into a separate subquery

also, if you want to use LIMIT to do paging (i.e. x rows per page), you pretty much have to use an ORDER BY clause for reliable results

i tried making a few other changes, please let me know if you get the same error message

SELECT u.name
     , u.age
     , u.category
     , u.info1
     , u.info12
     , u.lastlogin
     , u.lastactivity      
     , COALESCE(u2i.url,u2i2.url) url   
  FROM usertable u
INNER
  JOIN ( SELECT DISTINCT name
           FROM Unlocked 
          WHERE id = $newcomid ) AS d
    ON d.name = u.name 
LEFT OUTER
  JOIN imagetable u2i
    ON u2i.name = u.name 
   AND u2i.default = 'Y'
LEFT OUTER
  JOIN ( SELECT name
              , MIN(url) AS min_url
           FROM imagetable 
         GROUP
             BY name ) AS u2m
    ON u2m.name = u.name  
LEFT OUTER
  JOIN imagetable u2i2
    ON u2i2.name = u.name 
   AND u2i1.url = u2m.min_url
 WHERE (
       u2i.priv <> 1 AND u2i.status = 'active' 
    OR u2i2.priv <> 1 AND u2i2.status = 'active'
       )
3

I have.

The query runs fine, the subquery also runs fine. Put them both together, however, and “too many connections” and the site shuts down.

4

try testing your query outside of php, please

5

For some reason I am unable to accomplish what I want with a subquery. In my mind, this should work:

$resultSilentFriends=mysql_query("SELECT
     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(SELECT name FROM Unlocked WHERE id = $newcomid) GROUP BY u.name LIMIT " . (($n - 1) * $npp) . "," . $npp . ";");

BUT it crashes mysql/too many connections. Any idea why?

Thanks guys.

6

Its hard to give you the exact code without looking at all of the tables schema.

Also you say that “u.user_id = unlocked.id” , you have to make sure that “id” in unlocked table actually contains the user_id …

7

I don’t know, but you might get better help in the MySQL forum. This isn’t really a PHP issue.

8

You’re right :slight_smile: I’m used to having php questions.