Querying multiple tables... Headache

leatherback

Hi All,

Back after a few months. Again working on some stuff, but I am just too stupid to figure this one out.. Any help is appreciated.

OK.. This is what I try to do, using PhP and MySQL:
Three tables:
Clients: ID, PCode, ...
Activities: ID, ....
ClientAct: ID, ClientNr, ActNr

where Clients.ID = ClientAct.ClientNr and Activities.ID = ClientAct.ActNr.

I would now like to find all combinations of a specific Clients.PCode and Activities.ID that exist, using ClientAct as a linking table.

Using a query like:
select * from Clients activities ClientAct
where ( (Clients.PCode = $code)
and (ClientAct.ClientNr = ClientAct.ActNr)
and (Activities.ID = 1))

gives me 10 times the same result.

Any idea on a better way to do this, or any errors which may cause the result described above?
Of course I can break it up in several seperate queries where the results of each query are the basis of the next query. But as you add tables, this become impossible to work with.

Tha,

Christian_B_

Hi,

your're comparing ClientNr with ActNr (ClientAct.ClientNr = ClientAct.ActNr). That's like comparing apples with pears.
I think it must be

select * from Clients,ClientAct
where ( (Clients.PCode = $code)
and (ClientAct.ClientNr = Clients.ID)
and (ClientAct.ActNr = 1))

Hope it works.

leatherback

Oops..

Sorry about that.
Yeah. Well, I have build my oage in dutch, and though people would prefer some fieldnames which make sense in english. So I translated it..
I do have the right fieldnames. But.. still. I get 10 repetitions of the same result. I could use "distinct"in the command, but with larger databases I run into time-out problems. Somehow there is a loop in the querying, which I do not understand...

Any other ideas?

cyclops

you might want to use 'LIKE'

example :

select * from Clients,ClientAct 
where Clients.PCode LIKE $code
and ClientAct.ClientNr LIKE Clients.ID
and ClientAct.ActNr LIKE '1'

leatherback

🙂

That worked.
But eh.. Why!?
What is the difference??
(I know: I will RTFM )

But.. Thanks!