[Phpwm] mysql in() lists

[Phil Beynon]

> > Is there a more efficient way of making in() list sql other than
> > concatenating them together?
> >
>
> Without sample data I'm not sure I've got this quite right, but you can
> do this in a single SQL query, even in MySQL 4.0.
>
> You're looking for user_names from Type_B_Image_Tracking that match
> '%DUMMY_IMAGE.jpg' but do not already appear in the imageasset table,
> correct?

Yes, thats correct

> If so, here's the sample datasets I used: (apologies for the
> ASCII art, this should be viewed in a fixed-width font)
>
> mysql> SELECT * FROM Type_B_Image_Tracking;
> +-------------------+
> | user_name         |
> +-------------------+
> | 1_DUMMY_IMAGE.jpg |
> | 2_DUMMY_IMAGE.jpg |
> | 3_DUMMY_IMAGE.jpg |
> +-------------------+
> 3 rows in set (0.00 sec)
>
> mysql> SELECT * FROM imageasset;
> +-------------------+
> | user_name         |
> +-------------------+
> | 1_DUMMY_IMAGE.jpg |
> | 2_DUMMY_IMAGE.jpg |
> | 4_DUMMY_IMAGE.jpg |
> +-------------------+
> 3 rows in set (0.00 sec)
>
> This query returns just 3_DUMMY_IMAGE.jpg:
>
> mysql> SELECT Type_B_Image_Tracking.user_name
>     -> FROM Type_B_Image_Tracking
>     -> LEFT JOIN imageasset ON Type_B_Image_Tracking.user_name =
> imageasset.user_name
>     -> WHERE Type_B_Image_Tracking.user_name LIKE '%DUMMY_IMAGE.jpg'
>     -> AND imageasset.user_name IS NULL;
> +-------------------+
> | user_name         |
> +-------------------+
> | 3_DUMMY_IMAGE.jpg |
> +-------------------+
> 1 row in set (0.00 sec)
>
> The LEFT JOIN and NULL trick is useful to find rows in one table that
> don't exist in another without using subselects. By switching to SELECT
> * and removing the "IS NULL" part, we can see more clearly how the query
> works:
>
> mysql> SELECT *
>     -> FROM Type_B_Image_Tracking
>     -> LEFT JOIN imageasset ON Type_B_Image_Tracking.user_name =
> imageasset.user_name
>     -> WHERE Type_B_Image_Tracking.user_name LIKE '%DUMMY_IMAGE.jpg';
> +-------------------+-------------------+
> | user_name         | user_name         |
> +-------------------+-------------------+
> | 1_DUMMY_IMAGE.jpg | 1_DUMMY_IMAGE.jpg |
> | 2_DUMMY_IMAGE.jpg | 2_DUMMY_IMAGE.jpg |
> | 3_DUMMY_IMAGE.jpg | NULL              |
> +-------------------+-------------------+
> 3 rows in set (0.00 sec)
>
> The query takes all rows from the Type_B_Image_Tracking that match the
> given filename and tries to find a matching row in imageasset. If there
> is no match, NULL is used instead.
>
> In cases like this, it's often more readable and more efficient to let
> the database do all the work in one go, rather than generating dynamic
> queries from data that already exists in the database.
>
> Cheers,
>
> Dave
>

Thanks for that Dave, I've used Joins in the past, quite frequently in fact,
but it never occured to me to use it for doing this. Don't think I knew
about the "IS NULL" bit though, but now I've seen it in action I can see how
it's useful, and how it actually would have made things I've done in the
past easier :-)

Thanks again,

Phil