Solved

Not unique table/alias error in mysql

Posted on 2011-03-06
12
599 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How do uses indexes to maximize MySQL Searches 14 78
MySQL Sub-Select Query Returning Duplicate Result 7 63
Currency in SQL? 2 53
Mysql how to execute a commands file 5 41
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

751 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