0

I have two tables: Event and Story -- the former has info about an event and the latter has the story text of those events.

I can get all the story_id of the events that involve Brazil from Event like so:

SELECT @story_id := `story_id` FROM events
    WHERE actor_id='Brazil';

Then I want to query Story using the story_id I got. How would I do that? Can I nest them somehow?

UPDATE: I ended up storing story_id in a temporary table instead of a variable. (New to MySQL, I have no idea how variable works here...)

CREATE TEMPORARY TABLE IF NOT EXISTS temp_table AS (SELECT story_id FROM events
    WHERE actor_id='Brazil');

Then

SELECT * FROM stories, temp_table
    WHERE stories.StoryID = temp_table.story_id;

I'd appreciate any comment on this solution!

Heisenberg
  • 8,386
  • 12
  • 53
  • 102

1 Answers1

2

You could do this, with a JOIN:

SELECT @story_id := e.`story_id`, s.* FROM events e
INNER JOIN stories s ON s.StoryId = @story_id
    WHERE e.actor_id='Brazil';

So the reason for the s's and the e's are to identify what tables you're selecting from. You could just do FROM events and INNER JOIN stories, however, that's just the syntax I use to avoid typing out long table names. You could just do this:

SELECT @story_id := events.`story_id`, stories.* FROM events
INNER JOIN stories ON stories.StoryId = @story_id
    WHERE events.actor_id='Brazil';

However, as you probably notice, it's longer and not as simple. Though it is easier to understand.

hichris123
  • 10,145
  • 15
  • 56
  • 70
  • Could you explain a bit about the `e.` and `s.` syntax? – Heisenberg Jan 18 '14 at 03:01
  • @Anh I edited my answer to answer that, basically it's just to avoid typing out long table names. – hichris123 Jan 18 '14 at 03:04
  • Thanks for the answer! So it seems that MySQL variable can indeed store a vector of value. Why could I not do `SELECT * FROM stories WHERE StoryID = @story_id`? – Heisenberg Jan 18 '14 at 03:09
  • @Anh I'm not quite sure about that; interesting question. [This question](http://stackoverflow.com/questions/5273942/mysql-inner-join-vs-where) has some answers, but is kind of out of date. – hichris123 Jan 18 '14 at 03:16