1,105,546 Community Members

mysql join not working correctly

Member Avatar
mbhanley
Junior Poster in Training
52 posts since Nov 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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";
LastMitch
Deleted Member
 
0
 

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.

Member Avatar
Reverend Jim
Noli mentula
5,464 posts since Aug 2010
Reputation Points: 770 [?]
Q&As Helped to Solve: 654 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
0
 

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

Member Avatar
rch1231
Veteran Poster
1,187 posts since Sep 2009
Reputation Points: 131 [?]
Q&As Helped to Solve: 194 [?]
Skill Endorsements: 13 [?]
 
0
 

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";
You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: