* Jasper Bijl > Is there a way to do something like subqueries in one query?
Yes, JOIN can be used in many cases where you would think you need sub-queries. A JOIN is also generally faster, according to: <URL: http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html > > I have a customer table (Klant) with contacts (KlantKontakt) and a > address (Adres) table. > The address table keeps a record of each different address for a > customer including old addresses (to maintain history). ok... and there is a date column or similar in the Adres table, to keep track of which address is the last, I suppose. Let's call it 'FromDate'. You could add a column in Adres: state enum('active','inactive') ...and update the previous active address and set it to 'inactive' when you insert a new one, but that would be redundant, because the latest always is the active, right? > If I want to retrieve a list of customers with their newest address, I > have to do a max() to retreive the last address. You should take a look at this: <URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html > The "MAX-CONCAT trick" can maybe solve your prolem? > I can do this in PHP in a seperate query but I like to do it in one > query on the mysql prompt. > > Below is my (wrong) query: > > SELECT > Klant.klantcode, Klant.naam, > KlantKontakt.persooncode, KlantKontakt.Achternaam, > Adres.straat, Adres.postcode, Adres.plaats > > FROM Klant, KlantKontakt, Adres > > WHERE SoortKlant = 'Prospect' > AND Klant.klantcode = KlantKontakt.klantcode > AND Adres.klantcode = Klant.klantcode > > ORDER BY Klant.Naam > > > Is there any way to do this in MySQL in one query? It's hard to see what's wrong with the query above... except, of course, it does not select the latest address. :) There is another approach (in addition to subqueries if you use 4.1, or temporary tables, or the MAX-CONCAT trick). You could try using a self join. The date column in the Adres table is called 'FromDate', at least in my mind. :) The same query as above, but with an additional join on the address: SELECT Klant.klantcode, Klant.naam, KlantKontakt.persooncode, KlantKontakt.Achternaam, Adres.straat, Adres.postcode, Adres.plaats FROM Klant, KlantKontakt, Adres LEFT JOIN Adres AS A2 ON A2.klantcode = Klant.klantcode AND A2.FromDate > Adres.FromDate WHERE SoortKlant = 'Prospect' AND Klant.klantcode = KlantKontakt.klantcode AND Adres.klantcode = Klant.klantcode AND A2.klantcode IS NULL ORDER BY Klant.Naam We join any later address, if it's found we do _not_ want the row in our result. This is achieved by testing if A2.klantcode IS NULL. Warning: For each customer, the server will do a lookup on all later addresses for each address... this will be very slow when you have very many addresses for each customer. In your case, I would guess you rarly have more than 10-15 address rows per customer, so you should be safe. If my assumptions are wrong, and you have, say, 1000 address rows per customer, the self join approach will probably be too slow. The server would have to read 500.000 Adres rows for each customer (the first Adres would join to the 999 later, the second Adres would join to the 998 later, and so on). -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
