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
– grifos, Commented Mar 26, 2012 at 0:32
See this answer for some other ideas to implement what you're trying to do — Phil
– Phil, Commented Mar 26, 2012 at 0:37

Mosty Mostacho · Accepted Answer · 2012-03-26 00:32:42Z

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, 2012 at 0:32

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

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

6 Comments

Vidarious Over a year ago

What is the significance of "el.data", "el", "ef", "el.id"? Are they like labels/objects of the table names?

Mosty Mostacho Over a year ago

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)

Phil Over a year ago

@MostyMostacho FYI, data doesn't appear to be part of the event_list columns (id, name and date)

Mosty Mostacho Over a year ago

@Phil: In the question that field is named data and also date:)

Phil Over a year ago

@MostyMostacho I think when the OP said "event_list data", they meant all columns in that row, not specifically a data column

|

Alexander Korovin · Accepted Answer · 2020-11-26 16:23:41Z

2

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.

edited Nov 26, 2020 at 16:23

answered Oct 24, 2020 at 15:48

Alexander Korovin

1,4851 gold badge13 silver badges22 bronze badges

Collectives™ on Stack Overflow

mySQL SELECT using a foreign key in another table

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related