Getting similar rows (same title, different description)

[HaLo2FrEeEk]

I'm still working on my project with the challenges, and wanted to try something. Every day new daly challenges are put out. I track them and save them to a database. Sometimes the challenges are reused, in those cases I simply reference the saved challenge in the database, instead of putting in a new row. Other times, however, the challenge title is the same as one previously used, but the requirements or rewards are different. For example:

A Solid Outing - Earn at least 15 kills in a multiplayer Matchmaking game. (reward: 3000)
A Solid Outing - Earn at least 15 kills in a multiplayer Matchmaking game. (reward: 1500)
A Solid Outing - Earn at least 12 kills in a multiplayer Matchmaking game. (reward: 1300)

All three have the same title, the first two have the same requirements but a different reward, the last one has different requirements. I'd like to group these together, so that if today's challenge is the first one (reward 3000), I can show any information about the other two as well. Is there a simple, one-off query for this, or would I be better off simply running a query like this:

SELECT * FROM table WHERE title = "A Solid Outing"

And using PHP to go from there.

[Rabbit]

I'm not sure what you're looking for. Given an existing dataset, and given an input, what result are you looking for?

[HaLo2FrEeEk]

I store the challenges so that people can post tips on the best way to complete them. The tips are stored in a separate table. A tip for a specific challenge will contain that challenge's ID in the row.

Let's say today's challenge is one of the ones I used in my example above, we'll go with the first one, which has an ID of 33. I'll want all tips for challenge ID 33, but since the other two (IDs 69 and 136) are similar to the first one, I'd also like to get any tips for those challenges as well, so I basically need to get the chalenge IDs from the challenge table for a specified title, then get any tips in the tips table for those IDs.

I think LEFT JOIN is gonna work for me here, I just have to figure out the query.

[Rabbit]

In your case, then yes, you will need to pull the different IDs by the name of the challenge. However, since for any one challenge, the thresholds to complete them can vary, the data should be separated. I would probably store the thresholds in the daily challenges table if they vary day by day. That way, you only have one challenge ID instead of three for the same challenge.

[HaLo2FrEeEk]

I was thinking, perhaps have a table with just the titles, then another table with the requirements. Rows in both tables would have an ID and could be linked together, so for example I'd have one row in the titles table called "A Solid Outing", then I'd have 3 rows in the description table for each of the requirements / rewards, each of them would have a field linking it to the title row. Let's say today's challenge is the one worth 3000, I'd use the title from the titles table. That might work.

I did get a working query with LEFT JOIN though, but it's at home and I don't remember it.

[Rabbit]

If you're trying to join to the tips table, you don't really need an outer join. Since you only want tips whose ID match the ones you're pulling, an inner join should work.

[HaLo2FrEeEk]

I don't remember if it's an outer join or just a regular left join, I think it goes something like this:

SELECT * FROM challenges C LEFT JOIN tips T ON C.id = T.challenge_id WHERE title = 'some title';

I could have it wrong though. I'm not using it currently, but I copied it into my PHP and commented it out, so i'd have it if I needed it. Basically the query gets. All of the tips for all of the challenges that have a specific title.