mySQL SELECT using a foreign key in another table

Question

I'm having a hard time finding this answer so I'm hoping you guy can help me out.

I have two tables:

event_list with columns 'id', 'name', 'date' and event_default with one column 'default_id' which is a foreign key event_list('id')

The idea is that only one event can be default at a time, hence why event_default can only have one value (the id of the event in event_list).

I need a SELECT statement that obtains the event_list data of the event that is currently default. But I want to do this in one SELECT statement which I think I can do rather then two. I am using the PDO framework if that helps.

Success! Thank you. I ended up using:

$this->connection->query('SELECT * FROM event_list el JOIN event_default ef ON el.id = ef.default_id');

How do you select your default event in the event_default table? Does this table contain only one row? — grifos, Mar 26 '12 at 00:32
See [this answer](http://stackoverflow.com/a/4956023/283366) for some other ideas to implement what you're trying to do — Phil, Mar 26 '12 at 00:37

score 4 · Accepted Answer · answered Mar 26 '12 at 00:32

4

If I don't get the requirements wrong, this would do it:

select el.data from event_list el
join event_default ef on el.id = ef.default_id

I assume the table event_default only contains one row in which you specify the default event. So, after joining with the event_list table you'll get only one row (if id is the PK of event_list).

answered Mar 26 '12 at 00:32

Mosty Mostacho

42,742
16
96
123

What is the significance of "el.data", "el", "ef", "el.id"? Are they like labels/objects of the table names? – Vidarious Mar 26 '12 at 00:35
1

They are aliases. Instead of writing `event_list.id` I aliased that table so that I can reference that field as `el.id` (it's an L not a 1) – Mosty Mostacho Mar 26 '12 at 00:37
@MostyMostacho FYI, `data` doesn't appear to be part of the `event_list` columns (`id`, `name` and `date`) – Phil Mar 26 '12 at 00:38
@Phil: In the question that field is named `data` and also `date`:) – Mosty Mostacho Mar 26 '12 at 00:40
@MostyMostacho I think when the OP said *"event_list data"*, they meant all columns in that row, not specifically a `data` column – Phil Mar 26 '12 at 00:41
Oh, good point. Then just replacing `el.data` with `el.*` will do the trick, if that is the case. – Mosty Mostacho Mar 26 '12 at 00:44

Alexander Korovin · Answer 2 · 2020-11-26T16:23:41.460

Another solution is to use the parameter WHERE instead of JOIN (LEFT JOIN, RIGHT JOIN, etc.):

SELECT el.data 
    FROM event_list el, event_default ef 
    WHERE el.id = ef.default_id;

if there are more than two tables:

SELECT t1.field1, t2.field6, t2.field7
    FROM table1 t1, table2 t2, table3 t3 
    WHERE t1.field2 = t2.field3 AND t1.field4=t3.field5;

where there are three tables: table1 with fields/columns field1, field2, field4; table2 with fields field3, field6; table3 with fields field5, field7. If it is necessary one can use the same table names in FROM section and select any fields.

But JOIN is a litle bit faster and is recommended.

mySQL SELECT using a foreign key in another table

2 Answers2