You are getting two rows not because there is one in parcels and one in building, but because one of the tables has two rows and the other has one that matches according to the WHERE clause and the join criteria.
To see the "source data", do separate selects on the two tables: select * from parcels where DXF = '150-3-6' select * from building where DXF = '150-3-6' and ADDRESS LIKE '%21369 Vails%' Looking at the resulting data will help you determine why you are getting two result rows. If the source data is correct/as you want it to be, the keyword DISTINCT will suppress duplicate rows in the output. I usuallu try to avoid using it unless strictly required, since its use tends to hide an undesired Cartesian Products in a badly written query (usually from incomplete or improper join criteria). After reviewing your source data, try running your query with SELECT DISTINCT ... (rest of query unchanged). It should return 1 row in the sample you show, but if you had 2 DIFFERENT addresses, it would return both rows. (DISTINCT works across the entire select list (unless used in an aggregate) - this tends to be a source of some confusion among people who have limited SQL experience). HTH, Tore. ----- Original Message ----- From: "Diver8" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, February 24, 2003 4:36 PM Subject: RE: Unknown table in field list > Jon and Tore, thanks so much for your kind help. I > greatly appreciate it. > > I still have one bit of confusion about this whole > thing. I'm reading through the manual as I type this > so maybe I'll find the answer. If someone can help > clarify, I'd appreciate that as well. > > Running this query: > > mysql> SELECT parcels.DXF as 'record', > building.ADDRESS as 'results1', building.ADDRESS as > 'results2' from parcels INNER JOIN building on > parcels.DXF = building.DXF where building.ADDRESS LIKE > '%21369 Vails%' order by building.ADDRESS > desc; > > Returns these results: > > +---------+---------------------+---------------------+ > | record | results1 | results2 > | > +---------+---------------------+---------------------+ > | 150-3-6 | 21369 VAILS MILL RD | 21369 VAILS MILL RD > | > | 150-3-6 | 21369 VAILS MILL RD | 21369 VAILS MILL RD > | > +---------+---------------------+---------------------+ > 2 rows in set (2.08 sec) > > Okay. Basically, what's happening is that I'm ending > up with a duplicate result. I think I understand why > that's happening - I'm searching two tables & it's > returning the results from each table that match the > 'DXF' entry. > > My problem is that I need the query to be "smart" > enough to figure out if the result is a true duplicate > & if so, to discard that second result. > > To further complicate the issue... there will be > instances where duplicate entries *are* to be > expected. For instance, one parcel of land may have > two addressed buildings on it (for instance a duplex > or apartment building). If that's the case, the DXF > entry would be the same for both addresses, and I > would expect to get two results from the query. In > the case of the query cited at the top of this > message, that happens to be one parcel of land with > one addressed structure on it. Therefore, I would > only want to get one result back. My point in > mentioning this is that I don't think a simple "LIMIT > 1" would work here. I know it won't because I've > tried it. > > Maybe this is going to be impossible to do? The > parcels table has a unique key - 'DXF'. The buildings > table does not. There may be duplicate 'DXF' entries > in that table... the only common link between the two > tables, though, is the 'DXF' entry. > > I'm stumped. Any suggestions? > > Thanks. > > --- Jon Wagoner <[EMAIL PROTECTED]> wrote: > > Change the query to: > > > > SELECT parcels.DXF as 'record', > > building.ADDRESS as 'results1', parcels.relname as > > 'results2' from parcels INNER JOIN building > > on parcels.DXF = building.DXF where > > parcels.relname LIKE '%jones%' order by > > parcels.relname desc; > > > > __________________________________________________ > Do you Yahoo!? > Yahoo! Tax Center - forms, calculators, tips, more > http://taxes.yahoo.com/ > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
