select rows from 3 tables

[Deus_m1k3]

Hi:

I need to select the rows that aren't in a specific table but with a reference to a another table the tables are:

TABLE1
clv PK
names

TABLE2
clvp PK
clv FK (from TABLE1)

TABLE3
clva PK
clvp FK (from TABLE2)
dat

I know 4 sure the dat field on TABLE3 so I need 2 select the "names" fields that aren't related with TABLE2... e.g.

TABLE1
clv names
1 X
2 XX
3 XXX

TABLE2
clvp clv
1 2
2 1
3 2
4 2
5 1

TABLE3
clva clvp dat
1 1 AT-001
2 3 AT-002
3 4 AT-001
4 2 AT-003
5 5 AT-001

I need that the query result shows:

IF dat="AT-001" THEN

names
XXX


READ YA
Deus_m1k3

[Xnuiem]

I think it is possible using a subselect where not in (query) type deal. I will mess with it some later today and see if I can get you a working query.

[Deus_m1k3]

thanx 4 the help...

READ YA

[Xnuiem]

select 
	table1.names
from
	table1		
where
	table1.clv not in (
		select
			table2.clv
		from
			table2,
			table3
		where
			table2.clvp = table3.clvp
			and table3.dat = 'AT-001'
	)
;

Here ya go. It is just a sub select. Now this will not work in MySQL less than 4.1.x

[Deus_m1k3]

Sorry that didn't work, "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select table2.clv from table2, table3 where..."

READ YA

[Deus_m1k3]

My server is a 3.23.58 version

READ YA

[Xnuiem]

It will not work in anything less than 4.1.0 .

3.x is extremely out of date, you need to upgrade to at least 4.1.0 if not 5.x

Without upgrading, there is no way to write a query to do what you are asking. You will have to use multiple queries or other logic to filter the results.

[Deus_m1k3]

I was just hoppin' that the upgrade could be avoided 'cos I don't have directly the control of the server but thanx 4 the tips & the big help