Select from two tables with one query?

I have two (relevant) tables in my database:

"movies"
movie_id
moviename

"aka"
movie_id
aka

I'd like to be able to select * from movies,aka where moviename="Blah" or aka="Blah"; however, this needs to be done in one query only. I found a query that would do what I want with a union join, however, when doing a select count with this sort of query, it returns two rows with the count... a count from movies, and a count from aka. I need that count to be just one row.

Is this possible? Any ideas on how to do it?

Thanks!

Let me try to find out what you want:

movies:
movie_id moviename
1, First Movie
2, Second Movie

aka
movie_id aka
1, First Movie AKA
2, Second Movie AKA

If you want to get the following
movie_id moviename aka
1, First Movie, First Movie AKA
2, Second Movie, Seond Movie AKA
...

then you have to equi join, e.g.

SELECT m.movie_id, m.moviename, a.aka FROM movie m, aka a WHERE m.movie_id = a.movie_id AND (m.moviename like '%First%' or a.aka like '%First%');

would result in
1, First Movie, First Movie AKA

If you want to count rows you can do this by

SELECT count(*) FROM movie m, aka a WHERE m.movie_id = a.movie_id AND (m.moviename like '%First%' or a.aka like '%First%');

If you want to get a result like this
1, First Movie
1, First Movie AKA
...

you'd have to use UNION, e.g.

SELECT movie_id, moviename FROM movie WHERE moviename like '%First%'
UNION
SELECT movie_id, aka FROM aka WHERE aka like '%First%';

If you want to count records that this statement returns, you could use


if your DB supports subqueries. Otherwise you could create a view and use it for counting.

Hope it helps

Ahh... I wasn't aware of subqueries, never thought to look for those... that makes everything work now, thanks!