NOT IN subquery not working 1

[jemminger]

hi all,

this query keeps throwing a syntax error
"#1064 - 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 bcj_bookmark_id FROM bcj_bookmark_category_join_tbl
)"

SELECT * FROM bmt_bookmark_tbl
WHERE bmt_bookmark_id not in (
  SELECT bcj_bookmark_id FROM bcj_bookmark_category_join_tbl
)

each individual query executes properly by itself. i've written a million similar queries in mssql and oracle...what's going on here?

=========================================================
-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }

 

[cLFlaVA]

jemm-

subqueries aren't supported until 4.1. Do you have 4.1? If not, you'll have to create a JOIN statement...

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[cLFlaVA]

I think this might do what you want:

SELECT * FROM bmt_bookmark_tbl
WHERE bmt_bookmark_id not in (
  SELECT bcj_bookmark_id FROM bcj_bookmark_category_join_tbl
)


SELECT a.*
  FROM bcj_bookmark_category_join_tbl b LEFT JOIN bmt_bookmark_tbl a
    ON b.bcj_bookmark_id = [red]a.bookmark_id_col[/red]
 WHERE [red]a.bookmark_id_col[/red] IS NULL

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[jemminger]

thanks - i'll use a join.


=========================================================
-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }

 

[r937]

cLFlaVA, i think you have your tables backwards in the left join



rudy

http://r937.com/

SQL Consulting

 

[cLFlaVA]

I'm an idiot - the first part of the code I put there was just for my reference. I forgot to remove it from the textarea.

Let me know if the join works.

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[cLFlaVA]

r937, are you sure?

I thought the results from the join would be:

b a
---------------------
val1 val1
val2 val2
val3 null
val4 null
val5 val5

but, i respect your expertise...I am most likely wrong.

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[cLFlaVA]

ahh, i left out NOT in the is null part-

I tested this on my local, it seemed to return what jemm wanted:

SELECT a.*
  FROM test1 b LEFT JOIN test2 a
    ON b.c1 = a.c1
 WHERE a.c1 is not null

Do you see anything wrong with that?

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[r937]

yes, there's something wrong with your test1/test2 syntax -- if you say B left outer join A where A.x is not null, you are turning it from a left outer join to an inner

if you go back and look at the original problem, you will see that it is the bmt_bookmark_tbl rows with no matching bcj_bookmark_category_join_tbl rows that should be returned

if bmt_bookmark_t is A and bcj_bookmark_category_join_tbl is B, then we needt A left outer join B where B.x is null

your solution was the other way around, you had B left outer join A where A.x is not null

not the same thing at all

i think the answer is this --

select bmt_bookmark_tbl.* 
  from bmt_bookmark_tbl
left outer
  join bcj_bookmark_category_join_tbl
    on bmt_bookmark_id
     = bcj_bookmark_id 
 where bcj_bookmark_id is null

rudy

http://r937.com/

SQL Consulting

 

[cLFlaVA]

thanks rudy.

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[jemminger]

rudy's example is just how i ended up writing it. thanks for including your example for posterity



=========================================================
-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }