Page 1 of 1

getting values by joining tables

Posted: Tue Aug 21, 2007 1:36 am
by shivam0101
Hello,


I have ta table,

id (auto inc), memberid, partnerid, questions


if i want to know questions which are attempted by both, what query should i have to write

for example,

id (auto inc), memberid, partnerid, questions

1 98 99 1
2 98 99 2
3 98 99 1

i should get 1 (since, both attempted question No. 1)





Thanks

Posted: Tue Aug 21, 2007 4:09 am
by iknownothing
Your question is not very well thought out, or written, it also might help to look at some basic tutorials, but heres a starter...

Code: Select all

SELECT * FROM table_name WHERE question = '1'

Posted: Tue Aug 21, 2007 8:43 am
by shivam0101
SELECT * FROM table_name WHERE question = '1'

I want to retrive all the questions where both member and partner have taken.

your query gives the reverse result

Posted: Tue Aug 21, 2007 9:04 am
by iknownothing
Your post is confusing the hell out of me, I kind of get what your trying to say, but my brain is folding in on itself

Could you perhaps redo your table:

Code: Select all

1 98 99 1 
2 98 99 2 
3 98 99 1
to have a bit more uniqueness to it, and then explain which one has what etc, so I can understand. It just seems as though the same numbers are repeating themselves at the moment.

Posted: Tue Aug 21, 2007 10:03 am
by Zoxive

Code: Select all

Select * from `table` Group By `memberid`,`partnerid`
That will return every different match of memberid, and partnerid.

Its still kind of hard to under stand what you want.

Posted: Tue Aug 21, 2007 12:02 pm
by feyd
I think DISTINCT would work more in line with what shivam0101 is asking for than GROUP BY.

Posted: Tue Aug 21, 2007 3:07 pm
by VladSun
I agree.
In fact, shivam0101 haven't pointed what SQL engine he is going to use. Thus, using GROUP BY, as Zoxive suggested, would cause SQL error in PGSQL for example.

Posted: Tue Aug 21, 2007 10:17 pm
by califdon
I'm reading something different in shivam's question. And note that his subject was about "joining tables". I think he is trying to do something in one table that requires relational tables. I'm guessing that what he really wants is more like this:

tblMembers:
MemberID
MemberName
Question
.... etc.

tblPartners:
PartnerID
PartnerName
Question
.... etc.

Then he could do a Join between the two tables based on having the same value in the Question fields.

But since he hasn't explained what the relationship may be between "members" and "partners", or what these "Questions" are, it is very difficult to know what he is really trying to do. I also wonder whether the Questions field is going to contain just a single number or if he's trying to jam non-atomic values into it.

Shivam, we really do want to help you, but you need to explain what you're doing more precisely.

Posted: Wed Aug 22, 2007 12:26 am
by shivam0101
I am extremely sorry for giving wrong table structure.

id (auto inc), memberid, questions ->table structure

1 96 1

2 96 2

3 96 3

4 97 2


there will be two types of members - male, female

here, 96 is male and 97 is female.

i want all the questions attempted by both (member 96 and member 97)

Posted: Thu Aug 23, 2007 2:15 am
by shivam0101
I wanted the questions which are attempted by both.

This i solved by using the below query,

Code: Select all

select * from table_name WHERE member_id =96 AND member_id =97

Now, i want questions which was not attempted by 96 but attempted by 97

I tried,

Code: Select all

select * from table_name WHERE member_id !=96 AND member_id =97
This giving me all the questions attempted by 97

i tried intersection, later came to know mysql does not support intersection

Posted: Thu Aug 23, 2007 2:27 am
by shivam0101
solved it,

SELECT `question_id` FROM question_table WHERE member_id=97 AND `question_id` NOT IN (SELECT question_id FROM question_table WHERE member_id=96)