Relation between 3 tables mysql

Question

Relation between 3 tables mysql

Navigation

#1 by (3 votes)

2

I'm trying to make a relationship between three tables. I have the tbl_eventos table that will be a table where you will save the event and its date. The tbl_servicos_extra table will save extra services that can be added to the event. And finally the tbl_servicos_extra_eventos table, this table will make the relationship between the events and the extra services (many-to-many relationship).

What happens now is that by associating an extra service to the event, I have to go get all the extra services that are not being used on that day of the event. That is, I have the date of the event, so I have to make a query for a <select> that has all the extra services that can be added but can not be extra services that are already being used on the same day.

-tbl_eventos-
id_evento
data

-tbl_servicos_extra-
id_servico_extra
designacao

-tbl_servicos_extra_eventos-
id
id_evento
id_servico_extra

I'm using mysql and php. To display the results I have the following:

        <select name="id_servico_extra">
        <?php
            $sql = mysql_query("SELECT * FROM tbl_servicos_extra");
            while($row = mysql_fetch_array($sql))
            {
        ?>
                <option value="<?php echo $row['id_servico_extra'];?>"><?php echo $row['designacao'];?></option>
        <?php
            }
        ?>
        </select>

This is missing the filtering so that extra services that are being used on the same day by another event will not appear.

php mysql sql html

asked by anonymous 24.07.2014 / 12:47

1 answer

Convert JSON to UTF-8 on Android Doubt in the complexity analysis of an algorithm

score 3 · Accepted Answer

select *
  from tbl_servicos_extra se
 where not exists (select 1
                     from tbl_servicos_extra_eventos see
                     join tbl_eventos ev
                       on ev.id_evento = see.id_evento
                    where see.id_servico_extra = se.id_servico_extra
                      and ev.data = :param_data)

Where you have : param_data you replace with the date you want to filter.