mysql query help needed

[rob_ot]

Hi all. I've been lurking for a while, but i'm stuck, so though I'd ask you kind folks for help.

two tables:

ltable
p_id ¦ q_id

btable
p_id ¦ q_id ¦ user_id

I'm trying to select the q_id from all records in ltable where p_id in both tabels = n, that are not in btable and records from btable must be a certain user_id.

This is what I came up with, it doen't work. :/

SELECT l.q_id FROM ltable l, btable b WHERE l.p_id = 98 AND b.p_id = 98 AND b.user_id = 101 AND b.q_id!= l.q_id

I suspect I need a crazy inner join for this?

[JerryOdom]

maybe...

SELECT l.q_id FROM ltable as l, btable as b WHERE l.p_id = b.p_id AND l.p_id = 98 AND b.p_id = 98 AND b.user_id = 101 AND b.q_id!= l.q_id

oh sorry I noticed there was no "as" for the table renames

[edited by: JerryOdom at 11:08 pm (utc) on Feb. 8, 2007]

[rob_ot]

Hey Jerry, thanks for the try, but I've always aliased table names without the AS with no problem. With the AS, I get the same result as without.

I did add a distinct

SELECT DISTINCT l.q_id

and now it's a little closer. It doesn't seem to compare the two tables though. I have 10 records in btable and 12 in ltable.

The query should return only 2, with the distinct it returns twelve. without 121.

[coopster]

Welcome to WebmasterWorld, rob_ot.

Did you get anywhere with this? If not, can you offer a small sample of data from both tables and what you expect to see output by your query from those tables?