[RESOLVED] what is wrong with this query

[cornercuttinjoe]

i guess my thought process is wrong somewhere, but i cant seem to figure it out.

I have these two tables

pic_lpic (pic_lpic_num mediumint auto increment..., pic varchar(7), lpic varchar(7));
voice_circuit_has_pic_lpic (circuit_id varchar(20), pic_lpic_num mediumint);

i want to see everything in table pic_lpic in which the pic_lpic_num is not included in a query of of the voice_circuit_has_pic_lpic table with a given circuit_id.

basically, here is the query i have tried.

$query = "SELECT pic_lpic_num, pic, lpic FROM pic_lpic WHERE pic_lpic_num != some (SELECT pic_lpic_num FROM voice_circuit_has_pic_lpic WHERE circuit_id = '$circuit_id')";

you can see, i just want everything from the pic_lpic table where the pic_lpic_num doesnt equal those pic_lpic_num's in the inner query.

where am i going wrong with this?

it is just returning everything in the pic_lpic table. it doesnt filter anything out.

i know its a stupid mistake and ive just been staring at my computer for too long.

[pohopo]

I think this is what you want...

SELECT v.pic_lpic_num, p.pic, p.lpic
FROM pic_lpic p, voice_circuit_has_pic_lpic v
WHERE p.pic_lpic_num *= v.pic_lpic_num
AND v.pic_lpic_num IS NOT NULL

[cornercuttinjoe]

hmm, its still not working, but i see where you were going with it. i completely forgot about using the whole x.col_name notation.

what i tried is

SELECT p.pic_lpic_num, p.pic, p.lpic FROM pic_lpic p,
voice_circuit_has_pic_lpic v WHERE p.pic_lpic_num != v.pic_lpic_num AND
v.circuit_id = 'mycircuitid' AND v.pic_lpic_num IS NOT NULL

This should return the empty set, because i have it set up so that the voice_circuit_has_pic_lpic table has a circuit_id 'mycircuitid' that is associated with every pic_lpic_num.

do you see where im going wrong still? to me it looks like this should work.

thanks for trying to help me. you pointed me in the right direction, but its still not working like i want it.

i really appreciate it.

thanks for any help

[cornercuttinjoe]

found my own solution. had to break out the database textbook from college.

the query that solved my problem is ...

SELECT DISTINCT pic_lpic_num, pic, lpic FROM pic_lpic WHERE pic_lpic_num NOT IN (SELECT pic_lpic_num FROM voice_circuit_has_pic_lpic WHERE circuit_id = 'mycircuitid');

thanks for looking and trying to help.