MariusJ MariusJ - 2 years ago 67
SQL Question

Join 3 tables in SELECT query in MySQL

I have done some insertions into SQL tables, see the code.

Based on that code, I want the values for column 'tittel' from table 'video', but 'video' elements needs to have a given value from the table 'etikett' (I need to search for a matching element in ALL columns). As you can see, I need to go via table 'videoetikett' using the column 'videoid', which is the common column, and I'm not sure how to write the SELECT for this.

Example: I want to search all columns in 'etikett' for the 'spania' value. When it finds it, I need to use the 'etikettid', which is 1, on that row to get the 'videoid', which is 1, in 'videoetikett', which finally outputs the 'tittel' of 'video', 'Ferie 2016'.

I hope I managed to explain this in a good way.

Any help would be highly appreciated!

insert into video (videoid, starttid, sluttid, tittel, filplassering, navn)
values (1, '2016-01-11 16:00:00', '2016-01-11 17:00:00', 'Ferie 2016', '/bilder', 'Roy Jakobsen');

insert into etikett (etikettid, beskrivelse, posisjon)
values (1, 'ferie', 'spania');

insert into videoetikett (etikettid, videoid)
values (1, 1);

Answer Source

Do it in steps,


select e.etikettid from etikett as e where posisjon = 'spania';


select e.etikettid 
  from etikett as e, videoetikett as ve 
 where posisjon = 'spania' 
   and e.etikettid = ve.etikettid;


select v.tittel, e.etikettid 
  from etikett as e, videoetikett as ve, video as v
 where posisjon = 'spania' 
   and e.etikettid = ve.etikettid
   and v.videoid = ve.videoid;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download