RE: FN-FORUM: MySQL Query Problems

date posted 31st January 2006 12:28

I agree ben

A join gives the opposite but you could run over the resulting array in php
or whatever and eliminate results, im a big believer in pushing as much work
to the parsing engine if the operation can be done that way as opposed to
using the DB,

ash

-----Original Message-----
From: [EMAIL REMOVED] [EMAIL REMOVED] On Behalf Of Ben Moxon
Sent: 31 January 2006 12:30
To: FN-FORUM / [EMAIL REMOVED]
Subject: Re: FN-FORUM: MySQL Query Problems


A join would be the right operation if you want to select everything in
Table1 with matching records from Table2 - so for example, if table1 has
an Id called table1_id and table 2 references it with a field called
t1_id (why do they not have the same name? to make it easier to follow
in this example...) you could join like this:

SELECT ta.*, tb.* FROM Table1 ta INNER JOIN Table2 tb ON
ta.table1_id=tb.t1_id

However, what you want is everything from table 2 that has no equivalent
in table 2, for which a join is not such an obvious choice.

If you're on mysql 3.23 you may even have to create a temporary table,
which would look a bit like this:

/* Create a copy of table 1 with an extra field to mark out if it is in
table 2 */
CREATE TEMPORARY TABLE temp TYPE=HEAP SELECT table1_id AS Id, 0 AS
inTab2 FROM Table1;
/* Update the temporary table to mark whether there is a crossover
with table2 - we can update on a join */
UPDATE temp tm INNER JOIN Table2 tb ON tm.id= tb.t1_id SET inTab2=1;
/* Delete all records where there is a crossover */
DELETE FROM temp WHERE inTab2=1;
/*Finally select the subset of Table1 that is still in the temporary
table.*/
SELECT ta.* FROM Table1 ta INNER JOIN temp tm ON ta.table1_id=tm.Id;

In php those queries have to be run separately.

It's convoluted but I don't recall many other ways to get "real"
database functionality in early versions of MySQL. Much easier if you
have a recent version, mind.

-ben


D D Glendinning wrote:

>Morning All!
>
>A few of you have helped me offlist, but its still not working, so
>thought I would post it, so what the rest of you think:
>
>Basically I have a table storing clients, and a table storing
>documents which have a 1 to many relationship with clients.
>
>I am trying to select "all clients from clients table who do
>not have a relationship with a given document" so I am using this
>query:
>
>$Query = "SELECT * FROM ABSclients WHERE clientID != '(SELECT clientID
>FROM ABSdocs_clients WHERE imageID='".$imageID"')' ORDER BY name";
>
>
>Which doesn't work. I was suggested that I use this (Thanks Ben!):
>
>SELECT * FROM ABSclients WHERE clientID NOT IN (SELECT clientID FROM
>ABSdocs_clients WHERE imageID=$imageID) ORDER BY name
>
>But not sure if its compatibility issues or something, but that
>doesn't work either.
>
>Is it a Join I am after? HELP!!!
>
>
>D
>
>
>


--
Freelancers, contractors earn more with Prosperity4
Call 0870 870 4414 or visit www.prosperity4.com
and benefit from Inland Revenue approved expenses today.

To advertise here: http://www.freelancers.net/advertising.html