Inner Join 3 tables

Asked

Viewed 321 times

3

I have the following appointment.

$cmd = "SELECT ofertas.id, ofertas.titulo, ofertas.descricao,  ofertas.valor, ofertas.user_of, ofertas.categ, ofertas.local, ofertas.fav, favoritos.id_oferta

FROM ofertas
INNER JOIN favoritos
ON ofertas.id=favoritos.id_oferta
ORDER BY favoritos.id_user='$login_session'";

In the bookmarks table I have id, id_user, id_offer(whether it is a simple offer or offer_pro)

The way this is, I can do what I want for the table offers. Only I have one more table called offers_pro which also has the same fields as the offers.

I wanted something like that:

    $cmd = "SELECT ofertas.id, ofertas.titulo, ofertas.descricao,   ofertas.valor, ofertas.user_of, ofertas.categ, ofertas.local, ofertas.fav, favoritos.id_oferta, ofertas_pro.id, ofertas_pro.titulo, ofertas_pro.descricao,   ofertas_pro.valor, ofertas_pro.user_of, ofertas_pro.categ, ofertas_pro.local, ofertas_pro.fav

FROM ofertas (e ofertas_pro)
INNER JOIN favoritos
ON ofertas.id=favoritos.id_oferta (e ofertas_pro.id=favoritos.id_oferta)
ORDER BY favoritos.id_user='$login_session'";

It seemed?

It’s like this at the moment: 

$cmd = "SELECT o.id, o.titulo, o.descricao, o.valor, o.user_of, o.categ, o.local, o.fav, f.id_oferta
                    FROM ofertas AS o, favoritos AS f
                    WHERE o.id = f.id_oferta
                    ORDER BY f.id_user='$login_session'";



    $produtos = mysql_query($cmd);
    $total = mysql_num_rows($produtos);

//exibe os produtos 
        echo "<table style= width:auto>";

        echo "<tr>";
        echo "<th>ID</th>";
        echo "<th>Empresa</th>";
        echo "<th>Categoria</th>";
        echo "<th>Serviço</th>";
        echo "<th>Descrição</th>";
        echo "<th>Pagamento</th>";
        echo "<th>Distrito</th>";
        echo "<th>Ações</th>";
        echo "<th>Avaliar</th>";
        echo "<th>Total</th>";
        echo "</tr>";
    while ($produto = mysql_fetch_array($produtos)) {


        echo "<tr>";
        echo "<td>".$produto['id_oferta']."</td>";
        echo "<td>autor:".$produto['user_of'] . "</td>";
        echo "<td>".$produto['categ'] . "</td>";
        echo "<td>".$produto['titulo'] . "</td>";
        echo "<td>".$produto['descricao'] . "</td>";
        echo "<td>".$produto['valor'] . "</td>";
        echo "<td>".$produto['local'] . "</td>";
        echo "<td><a href=aceita.php?id=".$produto['id'].">Aceitar</a></td>";
        echo "<td><a  href=fav.php?id=".$produto['id']."><img src='img/fav.png' height='24' width='24'></a></td>";
        echo "<td>".$produto['fav'] . "</td>";










        echo "</tr>";

    }
        echo "</table>";

?>
php sql
  • 1

    before ORDER BY.. add another INNER JOIN offers_pro...

  • You can do as many JOIN as you need

  • I can’t seem to... I have 3 tables Offers, Offers_pro and Favorites I want to select the fields of offers, and pro offers and go in the favorite table search for the id of offers and offers_pro

  • Edit your question and specify what you want to get from the three tables.

  • About the gave to understand, had understood from the first.

  • Put the tables script and the result Voce wants

Show 1 more comment

2 answers

2


"SELECT ofertas.id, ofertas.titulo, ofertas.descricao,  ofertas.valor, ofertas.user_of, ofertas.categ, ofertas.local, ofertas.fav, favoritos.id_oferta

FROM ofertas
INNER JOIN favoritos
ON ofertas.id=favoritos.id_oferta
ORDER BY favoritos.id_user='$login_session'";

In the bookmarks table I have id, id_user, id_offer(whether it is a simple offer or offer_pro)

then..

SELECT 
favoritos.id,

ofertas.titulo,
ofertas.descricao,  
ofertas.valor, 
ofertas.user_of, 
ofertas.categ, 
ofertas.local, 
ofertas.fav, 
favoritos.id_oferta

ofertas_pro.titulo, 
ofertas_pro.descricao,  
ofertas_pro.valor, 
ofertas_pro.user_of, 
ofertas_pro.categ, 
ofertas_pro.local, 
ofertas_pro.fav, 

FROM favoritos 
LEFT JOIN ofertas ON (ofertas.id=favoritos.id_oferta) 
LEFT JOIN ofertas_pro ON (ofertas_pro.id=favoritos.id_oferta)

If this represents +/- what you need, I suggest you restructure your tables, after all offers_pro is an offer, but certainly with some difference, in the table offers_pro Oce should put the id_offer and what is different! this would be the query

SELECT 
favoritos.id,

ofertas.titulo,
ofertas.descricao,  
ofertas.valor, 
ofertas.user_of, 
ofertas.categ, 
ofertas.local, 
ofertas.fav, 

favoritos.id_oferta

ofertas_pro.informacao1_pro
ofertas_pro.informacao1_pro

FROM ofertas 
INNER JOIN ofertas_pro ON (ofertas.id=ofertas_pro.id_oferta) 
INNER JOIN favoritos ON (ofertas.id=favoritos.id_oferta) 
ORDER BY favoritos.id_user='$login_session'

0

Do something like this:

SELECT o.id, o.titulo, p.outroCampo, f.qualquerCampo
FROM ofertas AS o, ofertas_pro AS p, favoritos AS f
WHERE o.id = f.id_oferta
ORDER BY f.id_user;

It is possible to use INNER JOIN and ON also several times, but particularly I prefer this mode (I don’t know if it is more efficient or not).

Browser other questions tagged php sql

You are not signed in. Login or sign up in order to post.