mysql join not working correctly

[mbhanley]

Hi, I am having an issue, any suggestions will be great.
I am trying to fetch records from 3 tables on conditions to retrieve the correct data.
So far it brings up 2 of the same results as there are two image file names with the same product id's in the table row.
I want to limit that row to just show 1 but if I do that it removes all other records from being displayed.

$sql = "SELECT p.p_id, p.p_active, p.p_sku, p.p_slug, p.p_title, p.p_desc, p.p_price, p.p_qty, p.p_instock, i.p_img_id, i.p_img,i.p_img_thumb, i.p_id, c.c_id, c.c_title, c.c_slug 
FROM products AS p 
INNER JOIN categories AS c ON p.p_catid = c.c_id 
INNER JOIN product_img AS i ON  p.p_id=i.p_id 
WHERE p.p_catid='$c_id' AND p.p_active='1' ORDER BY p.p_id DESC";

I am trying to fetch records from 3 tables on conditions to retrieve the correct data.

@mbhanley

Can you post your table structure?

I think it would be easier to follow with your query.

[Reverend Jim]

And perhaps some data with what you expect to see as a result of the JOIN.

[rch1231]

Hello,

Try this for your statement:

    $sql = "SELECT Distinct p.p_id, p.p_active, p.p_sku, p.p_slug,
    p.p_title, p.p_desc, p.p_price, p.p_qty, p.p_instock, i.p_img_id,
    i.p_img,i.p_img_thumb, i.p_id, c.c_id, c.c_title, c.c_slug
    FROM products AS p
    INNER JOIN categories AS c ON p.p_catid = c.c_id
    INNER JOIN product_img AS i ON p.p_id=i.p_id
    WHERE p.p_catid='$c_id' AND p.p_active='1' ORDER BY p.p_id DESC";