Nested where clause in MySQL

Question

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!

hichris123 · Accepted Answer · 2014-01-18 03:04:07Z

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.

edited Jan 18, 2014 at 3:04

answered Jan 18, 2014 at 2:54

hichris123

10.3k15 gold badges58 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Heisenberg Over a year ago

Could you explain a bit about the e. and s. syntax?

hichris123 Over a year ago

@Anh I edited my answer to answer that, basically it's just to avoid typing out long table names.

Heisenberg Over a year ago

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?

hichris123 Over a year ago

@Anh I'm not quite sure about that; interesting question. This question has some answers, but is kind of out of date.

Collectives™ on Stack Overflow

Nested where clause in MySQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related