Displaying The Same Record Twice

Asked

Viewed 779 times

0

This is my code that makes a list of tables 

<?php 
    $consulta = mysql_query("SELECT * FROM mesas INNER JOIN pedidos WHERE pedidos.mesa = mesas.id and conta = 0");
    if (mysql_num_rows($consulta)==true) {
       while($lnmesas = mysql_fetch_array($consulta)){ 
?>

I launch a request into the database and it inserts it with the name, size, value, produced = 0, account = 0, and the table number ai type I did that Ner Join there because I just want it to display the table containing a record in the database that the table column is = to the table number in the table tables...

But there for example I launch two orders with table number 20 then he shows me twice table 20 if I place 4 orders at table 15 he shows me four times at table 15, and I wanted him to show only one of each.

I created the SQL Fiddle to improve the explanation of the problem.

php mysql

  • Tried using JOIN or LEFT JOIN?

  • There is also an error, you need to put AND tables.account = 0

  • use the clause GROUP BY

  • Use distinct.....

  • Can you give me an example of this DISTINCT based on my code?

  • @Sanction how I can use GROUP BY to solve my problem?

  • 1

    In this case he will even rethink, as a table may have more than one request. In your example, if table 15 has four orders, it’s showing every order on this table, isn’t it correct? I don’t know if that’s what you want, but here is an example using GROUP BY.

  • worked exactly the way I needed, thank you very much..

Show 3 more comments

1 answer

4

Expensive,

The querys below will return only the tables that exist in the requested table. Because the requested table contains more than one table it is necessary to use the distinct to prevent the return of duplicate lines.

select id
  from mesas
 where id in ( select distinct mesa
                 from pedidos
                where conta = 0 );

OR

select distinct a.id
  from mesas a,
       pedidos b
 where a.id = b.mesa
   and b.conta = 0;

Hug.

Browser other questions tagged php mysql

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