Emulating subqueries with temp tables in MySQL 3.23.55

[Emanuele D'Arrigo]

Hi everybody! I hope you can gimme a hand with this:

I just found out that the server I'm writing some code
for only has MySQL 3.23.55 which unfortunately doesn't
support subqueries (and dumb me I spent so much time
trying to make them work thinking it was a syntax problem).

So, I gotta find another way around.

I think temporary tables are the way to go, but I
can't find enough examples to put beside the manual
to full understand them. Any pointer to some resources
about this?

Specifically what I have are two tables:
- The first holds information about the flights in a timetable:
flight number, departure airport and arrival airport. Each
row is unique here.
- The second is basically a list of flights actually flown,
each flight identified by the flight number but not necessarily
unique or even present as a flight could have been flown
0, 1 or N times.

What I'd like is a query resulting in: flight number,
departure airport, arrival airport and "number of
time the flight has been flown".

The following query appearently worked:

SELECT t.flight, t.dpt, t.arr, count(*) as nb
FROM timetable t, flownlist f
WHERE t.flight=f.flightnumber
GROUP BY f.flight
ORDER BY nb

Unfortunately, I realized that flights never flown
but present in the timetable were not available in
the query's result as they are not present in the
flownlist.

How could I deal with this?

Thanks in advance.

Manu

[Emanuele D'Arrigo]

Excuse me folks,

is my question:

A) impossible to answer
B) impossible to understand
C) already answered somewherelse
D) too boring to deserve an answer
E) Other...

Manu

[Emanuele D'Arrigo]

Excuse me folks,

is my question:

A) impossible to answer
B) impossible to understand
C) already answered somewherelse
D) too boring to deserve an answer
E) Other...

Manu

[tunahan]

a little help..

Look at part8 of
http://www.keithjbrown.co.uk/ for temporary tables.
i think you should use left outer join instead of where in your query.

SELECT t.flight, t.dpt, t.arr, count(*) as nb
FROM timetable t, flownlist f
WHERE t.flight=f.flightnumber
GROUP BY f.flight
ORDER BY nb

try sth like

SELECT t.flight, t.dpt, t.arr, count(*) as nb
FROM timetable t
Left outer join flownlist f
on t.flight = f.flightnumber
group by f.flight
order by nb

[tunahan]

a little help..

Look at part8 of
http://www.keithjbrown.co.uk/ for temporary tables.
i think you should use left outer join instead of where in your query.

SELECT t.flight, t.dpt, t.arr, count(*) as nb
FROM timetable t, flownlist f
WHERE t.flight=f.flightnumber
GROUP BY f.flight
ORDER BY nb

try sth like

SELECT t.flight, t.dpt, t.arr, count(*) as nb
FROM timetable t
Left outer join flownlist f
on t.flight = f.flightnumber
group by f.flight
order by nb