Solved

Not unique table/alias error in mysql

Posted on 2011-03-06
12
606 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 250 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

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 250 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

630 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