Roger, Thank you for the explanation. I don't have Mysql 4.1 here, so I will see if I can install this new version. But I will try the self join suggestion to solve this thing.
Thank you very much for the quick response! Jasper > -----Original Message----- > From: Roger Baklund [mailto:[EMAIL PROTECTED] > Sent: woensdag 13 augustus 2003 13:40 > To: [EMAIL PROTECTED] > Cc: Jasper Bijl > Subject: Re: sub queries in mysql? > > > * 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]
