Solved

Not unique table/alias error in mysql

Posted on 2011-03-06
12
596 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 40

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 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