problem with querying from 2 databases

[jeff5656]

I trying to join two tables in 2 separate databases, mainbill and demo. 

 

$query2 = "SELECT * FROM mainbill.notes inner join mainbill.notes ON mainbill.notes.id = demo.notes_list.bill_id";

When I run this query I get:

 

Not unique table/alias: notes.

 

Now I do not have a notes table in the demo database so I can't figure out what the problem is.  Both databases are on the same server.

[AyKay47]

mainbill.notes is a table reference to the table notes...that's what you are telling it, looks like you are joining the same table...which makes no sense

[jeff5656]

I'm trying to join the table notes (in the mainbill database) with the table notes_list (in the demo database).

 

the join is via id (in the notes table) with bill_id (in the notes_id table).

[AyKay47]

$query2 = "SELECT * FROM mainbill.notes inner join demo.notes_list ON mainbill.notes.id = demo.notes_list.bill_id";

[jeff5656]

Thanks - now I get a new error:

SELECT command denied to user 'user_nameofdb'@'localhost'  for table 'notes'

 

So my question now is, how do I connect to 2 databases in order to run this query?    I think that's the problem...

[AyKay47]

well to put it plainly, you can't however by indicating the database in your select query, MYSQL allows another database to be called...so that's not the issue here..

 

1. Are you sure that you can execute query's with the given connection information?

 

2. Which database are you using in your mysql_select_db?

 

[jeff5656]

here's the connect code right before that query:

$dbhost = "localhost";
$dbname = "xxxx_mainbill";
$dbuser = "xxxxx_yyyy";
$dbpass = "xxxxx";

mysql_connect ( $dbhost, $dbuser, $dbpass)or die("Could not connect: ".mysql_error());
mysql_select_db($dbname) or die(mysql_error());

Importantly, the dbuser and dbpassword is the same for the other database (demo)

[AyKay47]

okay, try this

 

$query2 = "SELECT * FROM notes inner join demo.notes_list ON notes.id = demo.notes_list.bill_id";

[jeff5656]

I get the same arror except it says

SELECT command denied to user 'user_nameofdb'@'localhost'  for table 'notes_list'

 

 

Since the username and pw are the same for both databases, I wonder why this doesn't work...

[fenway]

Then your GRANTs are incorrect.

[jeff5656]

"Then your GRANTs are incorrect."

 

I can connect to both databases individually (just tried it) - but when I try to connect to both simultaneously with the above query I get the error.

[jeff5656]

Ok I'm sorry I think I just wasted all of your time.  I was not naming the databases correctrly.  for example the database is called

myusername_demo  NOT

demo

 

I a really sorry but at least I still needed the correct JOIN command which you guys did help me with so all is not lost!

Thanks again...

[AyKay47]

you know what, I thought that to myself when you posted xxxx_mainbill as your db name, I dismissed it as you trying to through us off!! 

 

Anyway, glad this is sorted