Mysql SELECT query return double values

Question

I'm having problems with a SELECT query, which instead of returning me a single value, gives me too many. The query below:

            SELECT costi.id, costo, descrizione, categoria
            FROM costi 
            INNER JOIN categorie 
            WHERE costi.id = 140

The query fishes data from a DB with two tables (costi and categorie) db structure

The problem I'm having is that: If, for example, I run the query on PHP my admin (by entering instead of $ _POST ['id'], the ID of a cost like 140) instead of just going back one row with the associated data, It comes back two rows (one for each category I've created). query result

They are identical rows, with the same values, except for the category field that is different. I need back just one row with the correct data.

You're not specifying any condition for the join. In other words, you're missing an `on` statement following the join. — Mitya, Aug 10 '17 at 09:31
Please specify some conditions to join. Otherwise it will populate like now only — Pranav MS, Aug 10 '17 at 09:39
If you don't specifying a ON statement your INNER JOIN will work as a CROSS JOIN instead makes sense that you get duplicated records. No body explained why it happens so i will explain it. — Raymond Nijland, Aug 10 '17 at 09:55

score 0 · Answer 1 · answered Aug 10 '17 at 09:32

Looking to your query result ..

You could use a group_concat for get the result in one row

   SELECT  costo, descrizione, group_concat(categoria)
        FROM costi 
        INNER JOIN categorie 
        WHERE costi.id = 140
 group by   costo, descrizione

score 0 · Accepted Answer · answered Aug 10 '17 at 09:33

0

Looks like it should be:

SELECT costi.id, costo, descrizione, categoria
FROM costi 
INNER JOIN categorie
    ON costi.id = categorie.id --note the join condition
WHERE costi.id = 140

answered Aug 10 '17 at 09:33

SierraOscar

17,507
6
40
68

score 0 · Answer 3 · answered Aug 10 '17 at 09:33

You need to JOIN the 2 tables using the foreign key in the JOIN statement

SELECT costi.id, costo, descrizione, categoria
FROM costi 
    INNER JOIN categorie ON categorie.id = costi.idcatagoria
WHERE costi.id = 140

Now the query knows which rows in the 2 tables are related to each other

score 0 · Answer 4 · answered Aug 10 '17 at 09:34

0

There can be 2 approaches:

Use the following query avoiding INNER JOIN:

    SELECT h.id, h.costo, h.descrizione, i.categoria
    FROM costi h, categorie i 
    WHERE h.id = 140 and h.idcategoria=i.id

You can use GROUP BY with INNER JOIN

answered Aug 10 '17 at 09:34

Chinmay jain

979
1
9
20

Why did you remove the JOIN in preference for an AND? 2 steps back surley – RiggsFolly Aug 10 '17 at 09:36
They are basically the same thing. Please refer to this: https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins – Chinmay jain Aug 10 '17 at 09:38
But why change the OP's code for what you believe is no benefit – RiggsFolly Aug 10 '17 at 09:40
Because of the personal preferences. I was just suggesting a working alternative. Using inner join or where is completely his choice. – Chinmay jain Aug 10 '17 at 09:45

score 0 · Answer 5 · answered Aug 10 '17 at 09:43

0

use the following query avoiding INNER JOIN:

 SELECT h.id, h.costo, h.descrizione, i.categoria
    FROM costi h, categorie i 
    WHERE h.id = 140 and h.idcategoria=i.id

answered Aug 10 '17 at 09:43

SAH

31
5

old way to join..this method could be deprecated in the future – Raymond Nijland Aug 10 '17 at 09:53

score -1 · Answer 6 · answered Aug 10 '17 at 09:43

-1

specifying any condition for the join. like table.id on ...

specify condition then you will get limited results

answered Aug 10 '17 at 09:43

Moiz Shafqat Husain

219
2
14

Mysql SELECT query return double values

6 Answers6