how to write a subquery into mysql 4.0 ?

fabiuz

Hi,

I need to write something like this:

SELECT books.idbook, books.title 
FROM books
WHERE EXISTS (SELECT * FROM out_of_stock WHERE out_of_stock.city =  'london' 
AND out_of_stock.idbook = books.idbook)

I have one table with all records (eg. books titles) and one table with some of this books (may be the ones not in stock) and the city where they are not available.

So what I want to do is to get a list of them from the first table (books) if and only if they are available in the city I am searching into the query.

to better explain, with the following query I am able to see all books not available in the selected city:

SELECT distinct books.idbook, books.title
FROM books, out_of_stock
WHERE out_of_stock.idbook = books.idbook AND 
out_of_stock.city LIKE 'london'

what I need is exactly the opposite: all books except the ones not available.

Is it possible?

thanks

Pieman86

Umm, well, I'm assuming that any book that's not explicitly listed as unavailable in a city is available. In which case, something like:

SELECT idbook, title FROM books
WHERE idbook NOT IN
(SELECT idbook FROM out_of_stock WHERE city = 'london');

should work.

The subquery fetches all the IDs of books not available in London. You then find the IDs and titles of all the books that are not in the list.

goldlikesnow

Won't work in MySQL 4.0, no subselects available yet!

SELECT distinct books.idbook, books.title
FROM out_of _stock
LEFT JOIN books ON out_of_stock.id = books.id
WHERE out_of_stock.id = books.id
AND out_of_stock.city LIKE 'london'

Pieman86

Oh well, that's a shame!

But I thought he was asking for a query that finds all available books in a city, whereas that finds all unavailable ones?

If subselects aren't possible, I'd suggest just splitting it into two separate queries (easily done via PHP or whatever). I can't think of any neat way of doing this using JOINs, but I could just be missing something obvious. 🙂

MarkR

You can probably do it using an outer join, then having a WHERE clause looking for a NULL on the foreign key column of the other table (which will only be NULL if the outer join didn't match a row in the other table).

This is really the Right way of doing it.

Mark

Pieman86

That almost works, but the trouble is you need to outer join the books table with only part of the out_of_stock table. Doing a full join on the tables and then searching for NULLs won't let you narrow your results down by city. I can't think of any way of selecting that subset of the out_of_stock table in one query using JOINs.

It's probably possible though...

fabiuz

thanks for the replies, unfortunally I am still unable to get results with one query only :-/

Pieman, may I ask you how you will do that with queries ?

I mean, I could get all records from the books table, I can get all records from second table in the specified city... but then I have to subtract first result from second ones...

Add 'n' Where conditions to a 3th query with many LIKE NOT is not a good solution.

I have arrays fileds to compare one by one then ...but it will result a long loop with about 10.000 records into main table ... is this the only solution?

Thanks

Sxooter

to find all the things in one table that aren't in another, use a left join with a where null clause... like so:

select yada from table1 t1 left join table2 t2 on (t1.id=t2.t1id) where t2.t1id IS NULL

fabiuz

thanks Sxooter, that sonds good, but unfortunatly it seems not to work: i am still gettin only the few rows available on table 2 or nothing at all when I add the city where clause

SELECT books.id, books.title
FROM books LEFT JOIN not_availables ON books.id = not_availables.id 
WHERE not_availables.id IS NULL AND not_availables.city = 'london'

Pieman86

I had another think, and this should work.

SELECT books.idbook, books.title
FROM books LEFT OUTER JOIN out_of_stock
ON out_of_stock.city = 'london'
AND books.idbook = out_of_stock.idbook
WHERE out_of_stock.idbook IS NULL;

fabiuz

wow!

thanks Pieman86, that is working!
Now I have to study and understand exactly what you have told to do so that that I could be able to write it by myself next time 🙂

thanks again!