Complex Query?

[xProteuSx]

I am not a MySQL guru: I only know some basics.  I am hoping that someone can point me in the right direction, and I can learn something a little more complex.  Here is my scenario:

 

I have 2 tables. 

 

Table 1:

 

user_id

user_fname

user_lname

user_handle

user_email

 

Table 2:

 

img_id_fk

img_thumb

 

Now, img_id_fk is a foreign key, that is equivalent to user_id.  Therefore, if user_id = 15 in table 1, then table 2 will have an row with img_id_fk = 15 which holds a filename for an image associated with this user.  Not everyone has an actual value for img_thumb.  That is, that field may be blank.

 

What I would like to do is select five random user_email's from table 1 and their associated values for the img_thumb field from table 2 but img_thumb cannot be blank.

 

I don't know if I am explaining this clearly.  Please let me know if I have to clarify anything else ...

 

Thank you in advance.

[AyKay47]

select t1.user_email, t2.img_thumb from table_1 as t1 left join table_2 as t2 on t1.user_id = t2.img_id_fk where t2.img_thumb is not null

 

Typically you want to name fields so they make sense with what they hold and with relationships, a user id is not an image id..

[The Little Guy]

You don't have to check for NULL if you do join instead of a left join, the is not null would also be in the having, not the where. Also you want to order by rand and limit it to 5 results.

 

select t1.*, t2.img_thumb from table1 t1 joint table2 t2 on (t1.user_id = t2.img_id_fk) order by rand() limit 5;

[AyKay47]

I forgot the order by rand(),

 

the is not null would also be in the having, not the where.

 

the having clause is used in conjunction with the group by clause, you can use IS NULL and IS NOT NULL in the where clause.

[The Little Guy]

But you need to check if they are null after you join.

[AyKay47]

But you need to check if they are null after you join.

right, your suggestion of using an inner join instead is the correct solution.

[The Little Guy]

This is how yours should look:

 

select t1.user_email, t2.img_thumb from table_1 as t1 left join table_2 as t2 on t1.user_id = t2.img_id_fk having t2.img_thumb is not null order by rand() limit 5

 

Yours does NOT check NULLs after the result set is built, if a member doesn't have a row in table 2 you will get a NULL in your result set when using a left join and no HAVING.

 

My method will automatically ignore the NULLs, so I don't need to check for a null during a search and after the search.

[AyKay47]

This is how yours should look:

 

select t1.user_email, t2.img_thumb from table_1 as t1 left join table_2 as t2 on t1.user_id = t2.img_id_fk having t2.img_thumb is not null order by rand() limit 5

 

Yours does NOT check NULLs after the result set is built, if a member doesn't have a row in table 2 you will get a NULL in your result set when using a left join and no HAVING.

 

My method will automatically ignore the NULLs, so I don't need to check for a null during a search and after the search.

 

Again, the HAVING clause is used in conjunction with a group by clause, it does not replace the where clause, quite simply, the query you just wrote is wrong.

 

I'm not here to argue, OP this is the query you should use for this, provided by Little Guy

 

select t1.*, t2.img_thumb from table1 t1 inner join table2 t2 on (t1.user_id = t2.img_id_fk) order by rand() limit 5;

[xProteuSx]

Thanks very much guys.  In the end, I did not even have to use the JOIN function of MySQL.  I simply combined a WHERE and IS NOT NULL with RAND() and LIMIT.  I'm going to have to look into this join stuff, though, because it seems like a very popular function.  Either way, as suspected, I got the bonus of learning some new MySQL:  I was not familiar with IS NOT NULL until now.  Again, thank you.

[AyKay47]

Thanks very much guys.  In the end, I did not even have to use the JOIN function of MySQL.  I simply combined a WHERE and IS NOT NULL with RAND() and LIMIT.  I'm going to have to look into this join stuff, though, because it seems like a very popular function.

 

There are two ways to grab values from multiple tables; a subquery or a join.

Can you post the final solution query please.