in reply to Newbie can't see the forest
The first step to getting a problem fixed is in stating the problem in ways that others can understand unambiguously. One diagnostic of how stuck you are is in the lack of clarity in your problem statement. I've read over your post several times, but to make sense of it I have to fill in everal gaps with assumptions and guesses. That's a warning flag. I say this to give you honest feedback, not to be mean.
If you repost the problem, please provide complete SQL DDL for both tables, along with notes of what the keys and foreign keys are.
I suspect that you're going to need a query like
This will give you a table with one row per item.it_ID, ordered by it_ID (which I assume is linearly ascending, and thus keeps it_Name in a sensible order). Thanks to the LEFT JOIN, the UserID column will be non-NULL if and only if the user has a history for the corresponding item.SELECT items.it_ID, items.it_Name, user_item_history.UserID FROM items LEFT JOIN user_item_history ON items.it_ID = user_item_history.it_ID WHERE user_item_history.UserID = ? ORDER BY items.it_ID
Getting the results of the query into Perl data structures is left as an exercise.
|
|---|