Page 1 of 2

Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 6:00 pm
by jbh
Hey,

I've really gotten the hang of Join Queries and I love using them. For some odd reason, though, I cannot wrap my brain around a simple
request. The goal is to list question titles that were not created by a user (member_id='2') nor answered by the same user.

Tables:

questions:

ID | title | user_id
1 ques #1 1

results: (the table where we track which members answered which questions)

ID | question_id | user_id
1 1 2

I removed pointless fields for the 'results table'

Well, long story short, if a member has user_id = '3', clearly he did not answer a question nor ask one.
That is why '3' doesn't exist under the user_id fields for any of those tables.

The question is, and I have tried for hours and my brain refuses to see things logically (it's okay to laugh at me),
how do I pull the question TITLE from table 'questions' that has a user_id <> member_id AND
from questions that are NOT answered (in the results table) from the same member_id

I just cannot get it to work.

Thank you for your time. I hope this was clear. If not, I do apologize and I can try again.

Re: Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 6:06 pm
by Eran
From what I understand it should be something like

Code: Select all

SELECT * FROM questions 
INNER JOIN results ON results.question_id=questions.ID
WHERE questions.user_id != 3
AND results.user_id != 3

Re: Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 6:16 pm
by jbh
First of all, thank you. Second of all, I might be asking for the wrong thing.
Technically, it did work perfectly. But I notice one flaw in my logic:

If user_id 3 doesn't answer a question yet, there is no record at all, to pull from
So out of 3 questions (created and answered by user 1 and 2) he only sees 2 of 3 questions.

The 2 questions he sees are based on questions that were logged by the results table.

So my dilema is how would user member 3 view all 3 questions with such a join? Maybe
I am asking for something not possible or requiring a better database design?

Re: Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 6:32 pm
by Eran
it's perfectly possible, and your design seems correct (at least from the information you gave). just change the inner join to a left join and move the where condition to the on clause:

Code: Select all

 
SELECT * FROM questions
LEFT JOIN results ON results.question_id=questions.ID AND results.user_id != 3
WHERE questions.user_id != 3
I would assume that you need to pull some data from the results table, otherwise there's no need to join to that at all in this case.

Re: Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 7:32 pm
by jbh
Oddly enough, even that won't work. Well, it works for 2 users but not for one.

One user, in this case user_id='2' answered a question but has not asked one. However, since
our query is looking for questions in the questions table where user_id<>'2', it shows all questions
where user_id=1. Even though that question was answered, it shows up anyway.

Welcome to my mysql hell! ;)

Thank you for the input. I just feel like I am going to slap myself when this is somehow figured out.

Re: Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 7:41 pm
by Eran
I'm finding it hard to understand what you want to achieve with this query. This query returns all the questions not asked by a particular user, and also the answers for those questions for which the same user did not answer. What do you want to receive? from your last post it seems you don't want any questions that were answered at all, regardless of user? please try to be more specific

Re: Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 7:46 pm
by jbh
I see. As mentioned (or attempted to explain, my apologies)

the goal is to find questions that were *both* not asked by user_id=2 (for example) and also not answered.

So, what combo of questions did this user not create as well as not answer.

Re: Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 7:49 pm
by Eran
Not answered at all? or by that particular user?

Re: Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 7:51 pm
by jbh
Not answered by that user. Sorry. See, the point of the function is to let a new member ask or answer a question (2 options presented to them)
If they ever answer a question it's important to not let it appear again when they try to answer another one.
Since it's also key that they don't want to answer their own question, my query is intended
to eliminate a question asked by the user and answered by the user.

The only focus is the user, not overall users. I apologize if this was not clear. I realize it's not a standard request.

Re: Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 8:02 pm
by Eran
I think this will get you the results you want (if I got you this time)

Code: Select all

SELECT * FROM questions
LEFT JOIN results ON results.question_id=questions.ID
WHERE questions.user_id != 3
AND (results.user_id IS NULL
OR results.user_id != 3)

Re: Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 8:14 pm
by jbh
1.) Thank you
2.) (on edit, read below for those following this thread)

Sadly, something went wrong. (Not your fault, I think I am asking for the impossible and I feel terrible)

Re: Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 8:23 pm
by jbh
Spoke too soon. This is odd.

I let user_id = 3 answer a question. User id 1 also answered it (question #1)
When I used the query it displayed all 3 questions including the one user_id=3 answered.

Re: Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 8:33 pm
by Eran
You're right, I didn't consider multiple users answering the same question. This should do it

Code: Select all

SELECT * FROM questions
WHERE questions.user_id != 3
AND id NOT IN(SELECT id FROM results WHERE user_id=3)
I tried to avoid using a dependent subquery, but I guess there's no helping it

Re: Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 8:53 pm
by jbh
I think this works. ..

Re: Cannot Create The Proper Join Query Between 2 Tables

Posted: Mon Aug 03, 2009 9:08 pm
by jbh
Got it:

Code: Select all

SELECT *
FROM questions
WHERE questions.user_id !=3
AND questions.ID NOT
IN (
 
SELECT question_id
FROM results
WHERE user_id =3
)
I want to save up money. PM me your paypal so when I do
I can send you a thank you gift.