MySQL ODBC Connector - subquery accross different database

Author

subquery accross different database

Badai Aqrandista

2005-09-19, 3:24 am

Hi all,

I have two three tables, spread accross two databases:

hotel_1 database:
table room (room_id, room_name)
table package (package_id, package_name)

master database:
table search_cache (date, room_id, package_id, hotel_id)

I'd like to search for any combination of room/package ids that don't exist
in the search_cache for any given date and hotel_id, so I made this simple
query:

SELECT distinct room_id, package_id
FROM hotel_1.room, hotel_1.package
WHERE ROW(room_id, package_id) IN (
SELECT distinct sc.room_id, sc.package_id
FROM master.search_cache sc
WHERE sc.hotel_id = 1 AND date = '2005-09-20'
);

But it returns an empty set.

However, when I do the main and sub queries independently, I got these
results:

main query:
room_id | package_id
--------------------------------
1 | 1
1 | 2
1 | 3
2 | 1
2 | 2
2 | 3

sub query:
room_id | package_id
---------------------------------
1 | 1
1 | 2
2 | 1
2 | 2

So, the combined query should return table similar as the subquery right?

Could anyone shed a light on this problem? Is it because the subquery uses
other database's table?

Thanks...

---
Badai Aqrandista
Cheepy (?)

____________________
____________________
____________________
_____
Sell your car for $9 on carpoint.com.au
http://www.carpoint.com.au/sellyourcar

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Badai Aqrandista

2005-09-19, 3:24 am

solved it...

i used 'distinct' in main and subquery and somehow it affects the whole
query results...

---
Badai Aqrandista
Cheepy (?)

>From: "Badai Aqrandista" < badaiaqrandista@hotm
ail.com>
>To: mysql@lists.mysql.com
>Subject: subquery accross different database
>Date: Mon, 19 Sep 2005 15:14:06 +1000
>
>Hi all,
>
>I have two three tables, spread accross two databases:
>
>hotel_1 database:
>table room (room_id, room_name)
>table package (package_id, package_name)
>
>master database:
>table search_cache (date, room_id, package_id, hotel_id)
>
>I'd like to search for any combination of room/package ids that don't exist
>in the search_cache for any given date and hotel_id, so I made this simple
>query:
>
>SELECT distinct room_id, package_id
>FROM hotel_1.room, hotel_1.package
>WHERE ROW(room_id, package_id) IN (
> SELECT distinct sc.room_id, sc.package_id
> FROM master.search_cache sc
> WHERE sc.hotel_id = 1 AND date = '2005-09-20'
> );
>
>But it returns an empty set.
>
>However, when I do the main and sub queries independently, I got these
>results:
>
>main query:
>room_id | package_id
>--------------------------------
>1 | 1
>1 | 2
>1 | 3
>2 | 1
>2 | 2
>2 | 3
>
>sub query:
>room_id | package_id
>---------------------------------
>1 | 1
>1 | 2
>2 | 1
>2 | 2
>
>So, the combined query should return table similar as the subquery right?
>
>Could anyone shed a light on this problem? Is it because the subquery uses
>other database's table?
>
>Thanks...
>
>---
>Badai Aqrandista
>Cheepy (?)
>
> ____________________
____________________
____________________
_____
>Sell your car for $9 on carpoint.com.au
>http://www.carpoint.com.au/sellyourcar
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>http://lists.mysql.com/mysql? unsub...> a@hotmail.com
>

____________________
____________________
____________________
_____
REALESTATE: biggest buy/rent/share listings
http://ninemsn.realestate.com.au

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links