solved it...
i used 'distinct' in main and subquery and somehow it affects the whole
query results...
---
Badai Aqrandista
Cheepy (?)
From: "Badai Aqrandista" <[EMAIL PROTECTED]>
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/[EMAIL PROTECTED]
_________________________________________________________________
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/[EMAIL PROTECTED]
