RE: FN-FORUM: MySQL Query Problems

date posted 31st January 2006 12:06

Oops

Using two queries should give a noticeable

Should read

Using two queries SHOULDNT give a noticeable

ash

-----Original Message-----
From: [EMAIL REMOVED] [EMAIL REMOVED] On Behalf Of ashley
etchell
Sent: 31 January 2006 12:24
To: FN-FORUM / [EMAIL REMOVED]
Subject: RE: FN-FORUM: MySQL Query Problems



Option 1

You could try using a LEFT JOIN

SELECT * FROM ABSclients LEFT JOIN ABSdocs_clients USING(clientID)

Its maybe overkill to do it this way but it should give you something to
work with until you find a better fix

This should produce a list of all documents with client data too, people
with null docs data are the ones you want

Option 2

This is similar to bens suggestion

But split it into two queries
Presuming your using PHP here..
$client_ID_array = query('SELECT clientID FROM ABSdocs_clients WHERE
imageID='.$imageID);

// fetch it into an array
$sorted_client_IDs = implode(',',$client_id_array);

// get your clients with no relationship
SELECT * FROM ABSclients WHERE clientID NOT IN ($sorted_client_IDs)


Using two queries should give a noticeable speed impact unless you have more
than 1,000,000 millions rows in the db or something like that.

Hope it helps
Ash





-----Original Message-----
From: [EMAIL REMOVED] [EMAIL REMOVED] On Behalf Of Andy Creed
Sent: 31 January 2006 11:52
To: FN-FORUM / [EMAIL REMOVED]
Subject: RE: FN-FORUM: MySQL Query Problems


What is the actual value of $Query? You can then always paste that into
something like phpmyadmin and test it.

What version of Mysql are you using?

Andy

-----Original Message-----
From: [EMAIL REMOVED] [EMAIL REMOVED] On Behalf Of D D
Glendinning
Sent: 31 January 2006 11:43
To: FN-FORUM / [EMAIL REMOVED]
Subject: FN-FORUM: MySQL Query Problems


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


--
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


--
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