Home / Questions / 328249
Accepted
EMCode
Asked: 2020-02-11 11:41:40 +0800 CST 2020-02-11 11:41:40 +0800 CST

SQL query to compare IDs in three tables

  • 772

I have a small test database with three tables .

Basically it is teacher data:

  • The field_title table brings the names of the teachers
  • The field_body table brings a description of your work experience
  • The field_images table brings in images like college degrees, eg.

They all have a common identifier field which is pages_id .

These are the tables:

field_title

pages_id      data
------------------------------
1060          Juan Carlos
1062          Ana Maria
1063          Jose Eduardo
1064          Juan Camilo
1068          Mario Andres

field_body

pages_id      data
------------------------------
1060          So, Jonathan, how was the pull-out? You act like y...
1062          Seitan art party dolore farm-to-table, slow-carb n...
1063          Yard maroon hulk cable wench tack furl bilge rat c...
1064          Está la cosa muy malar jarl aute incididunt. Tempo...
1068          No! Hoc non credant? Gus habet cameras ubique plac...

field_images

pages_id      data
------------------------------
1060          prfilepicture.png
1062          cierreimg.png
1062          enero.png
1063          boletin.png
1064          fondoblanco.png
1068          hv-44.png
1068          image.png
1068          profile.png
1068          otros.png

What I want to do is bring with a query this data per record: the name of the teacher, his biography and his image(s).

This is what I tried, so that it would bring the first result for example.

SELECT * FROM field_title, field_body, field_images
WHERE pages_id = '1060';

Then I realized that I can use INNER JOIN on multiple tables, by doing this:

SELECT * FROM field_title, field_body, field_images
JOIN data ON ALL TABLES
WHERE pages_id = '1060';

But I don't quite understand how it works.

The expected result would be something like this, in the first case:

Juan Carlos
So, Jonathan, how was the pull-out? You act like y...
prfilepicture.png

Thanks for your time and help!

html

1 Answers

  1. Best Answer

    UPDATED:

    If we have a date field in field_images and we want to keep the first (oldest) one, we could adapt the SQL to:

    SELECT ft.pages_id, ft.data as autor, fb.data as body, fi.data as imagen
FROM 
field_title ft JOIN field_body fb on ft.pages_id = fb.pages_id
JOIN field_images fi on fi.pages_id = ft.pages_id 
where ft.pages_id = 1068 
and fi.fecha_insertado = (select min(fi2.fecha_insertado) from field_images fi2 where fi2.pages_id = ft.pages_id );

    ORIGINAL ANSWER:

    I would do 2 "typical" inner joins between the 3 tables, one chained with the other:

    SELECT ft.pages_id, ft.data as autor, fb.data as body, fi.data as imagen
FROM 
field_title ft JOIN field_body fb on ft.pages_id = fb.pages_id
JOIN field_images fi on fi.pages_id = ft.pages_id 
where ft.pages_id = 1068;

    The "problem" is that the author and the body are going to be repeated for each image on the same page, but the grouping in that case would be done (if necessary) in the part of the main program (outside the SQL).

    The output of the query would be something like:

    pages_id    autor   body    imagen  
1068    Mario Andrés    No! Hoc non credant? Gus habet cameras ubique plac...   otros.png   
1068    Mario Andrés    No! Hoc non credant? Gus habet cameras ubique plac...   profile.png 
1068    Mario Andrés    No! Hoc non credant? Gus habet cameras ubique plac...   image.png   
1068    Mario Andrés    No! Hoc non credant? Gus habet cameras ubique plac...   hv-44.png
    • 3