Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Not unique table/alias error in mysql

Posted on 2011-03-06
12
Medium Priority
?
616 Views
Last Modified: 2012-05-11
Hi, I need to find all category names that are bind to a product my tables looks like this:

product_category                   product_tbl                  category_tbl
-------------------------             ---------------------       -------------------------
c_id             p_id                         P_id         blah           c_id         category
  1                254152                 254152      24               1            electronics
  1                548414                 548414      15               2            books  
  2                548414

I have the product id = $p_id  and  try to get all category names from mysql
using  the folowing code.  I get:  Not unique table/alias   mysql error

code:
$result = mysql_query("select category_tbl.* from product_tbl inner join product_category on product_category.c_id = category_tbl.c_id inner join product_category on product_category.p_id = product_tbl.p_id where product_category.p_id = $product_id")or die("    "  . mysql_error());

does someone knows how to correct this pls
0
Comment
Question by:ikon32
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 7

Expert Comment

by:MrNed
ID: 35053390
Are you sure that's correct - where's the category_tbl join? I see product_tbl and product_category twice.
0
 

Author Comment

by:ikon32
ID: 35053581
not sure if is correct, is not working...  can you correct my code pls, very confiused...
0
 
LVL 7

Accepted Solution

by:
MrNed earned 1000 total points
ID: 35053673
My guess:

select category_tbl.*
from product_tbl inner join category_tbl
  on product_tbl.c_id = category_tbl.c_id
inner join product_category
  on product_category.p_id = product_tbl.p_id
where product_category.p_id = $product_id
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:ikon32
ID: 35053739
returns: Unknown column 'product_tbl.c_id' in 'on clause'
0
 
LVL 7

Expert Comment

by:MrNed
ID: 35053800
OK, try this:

select category_tbl.*
from product_tbl inner join category_tbl
  on product_tbl.p_id = category_tbl.c_id
inner join product_category
  on product_category.p_id = product_tbl.p_id
where product_category.p_id = $product_id

I'm driving blind here with no schema definitions.
0
 

Author Comment

by:ikon32
ID: 35053922
my tables...

mysql_query("CREATE TABLE IF NOT EXISTS product_tbl (
            p_id BIGINT(20) NOT NULL,
            p_name varchar(50) NOT NULL,
            p_color varchar(50) NOT NULL,
            p_price int(4) NOT NULL,
            p_caption varchar(255) NOT NULL,
            p_supplier varchar(50) NOT NULL,
            pic_link varchar(100) NOT NULL,
            PRIMARY KEY(p_id),
            UNIQUE KEY (p_id))");



mysql_query("CREATE TABLE IF NOT EXISTS category_tbl (c_id int(6) NOT NULL auto_increment,
category varchar(50) NOT NULL,
PRIMARY KEY(c_id), UNIQUE KEY (category))");
mysql_query("SET NAMES 'utf8'");


mysql_query("CREATE TABLE IF NOT EXISTS  product_category (
            c_id int(8) NOT NULL,
            p_id BIGINT(20) NOT NULL,
            PRIMARY KEY (c_id, p_id)
            )ENGINE=InnoDB") or die("    "  . mysql_error());


0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 total points
ID: 35053976
try this.
$result = mysql_query("SELECT c.* 
  FROM product_tbl AS p 
       INNER JOIN product_category AS pc 
         ON p.p_id = pc.p_id 
       INNER JOIN category_tbl AS c 
         ON pc.c_id = c.c_id 
 WHERE pc.p_id = $product_id")or die("    "  . mysql_error());

Open in new window

0
 

Author Comment

by:ikon32
ID: 35054277
$temp_rows = mysql_fetch_array($result);

// the
print_r($temp_rows);

returns: Array ( [0] => 2 [c_id] => 2 [1] => electronics [category] => electronics  )

in my loco example I have asign 2 categories that sould have been returned.
how can it return only the names of all categories that are asign to the $p_id without the c_id?
0
 
LVL 7

Expert Comment

by:MrNed
ID: 35054451
Change the select columns from * to the column you want (i assume category):

select category_tbl.category
from product_tbl inner join category_tbl
  on product_tbl.p_id = category_tbl.c_id
inner join product_category
  on product_category.p_id = product_tbl.p_id
where product_category.p_id = $product_id
0
 

Author Comment

by:ikon32
ID: 35054694
returns no value.
0
 

Author Comment

by:ikon32
ID: 35056987
the one step approch maybe faster but more complicated and confusing, so I also tried a 2
step.
First find all the (category id's) that are maped with the (product id) and then run a loop to find the names of the categories based on the (category id's) that have been return.

In this case it works and returns all names, but what is the weight that will be put on the server... can you please check and advice?
my solution:

$resu = mysql_query("select c_id from product_category where p_id ='$product_id'")or die("    "  . mysql_error());

    while($rows= mysql_fetch_row($resu)){
    $val_temp = mysql_query("select category  from category_tbl where c_id ='$rows[0]'")or die("    "  . mysql_error());
    $temp_rows = mysql_fetch_row($val_temp);
    echo "$temp_rows[0]";
}
0
 

Author Closing Comment

by:ikon32
ID: 35058055
You guys are great, thanks for your comments,with few modifications I'll get it right.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question