getting values by joining tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

getting values by joining tables

Post 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
User avatar
iknownothing
Forum Contributor
Posts: 337
Joined: Sun Dec 17, 2006 11:53 pm
Location: Sunshine Coast, Australia

Post 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'
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

Post 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
User avatar
iknownothing
Forum Contributor
Posts: 337
Joined: Sun Dec 17, 2006 11:53 pm
Location: Sunshine Coast, Australia

Post 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.
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I think DISTINCT would work more in line with what shivam0101 is asking for than GROUP BY.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

Post 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)
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

Post 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
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

Post 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)
Post Reply