Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2007-05-20 : 02:36:15
|
| I have the following sqlSELECT members.*, members_schoolS.Is_Primary, schools.schoolFROM schools RIGHT JOIN (members_schoolS RIGHT JOIN members ON members_schoolS.member_ID = members.id) ON schools.id = members_schoolS.school_ID;now the issue is that this returns multiple records for members if they have multiple records in the schools table -- How can I get it to return only one record per member? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-05-20 : 03:28:26
|
quote:
now the issue is that this returns multiple records for members if they have multiple records in the schools table -- How can I get it to return only one record per member?
Well, you have to decide which record do you want ?
KH |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2007-05-20 : 03:36:29
|
| anyway to get it to show the member and field for primary school (this can only be one) and a field for secondary field(showing all secondary schools -- usually more then one)otherwise how cna i get it to show me at least the first entry but not return both? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-05-20 : 03:48:15
|
What is first entry ? Record are not stored in table in any particular manner. As such there isn't a first row and last row. You decide the sequence of records to return using the ORDER BY clause.You are not new to this forum, you know the drill. Post your table DDL, sample data and the expected result.
KH |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2007-05-20 : 04:01:26
|
| membersfirstnamelastnamemembersschoolsmembersidschoolidisprimaryi guess if i can't show all the entries in the school fields can I just return one member ignoring what's in the schools field? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-05-20 : 04:06:21
|
You can show all the columns of the school table if you want. But as for one member record, there maybe more than one corresponding records in the school table. What you need to do is define which record in the school table to show. Post the school table structure with sample record for each of the table and the result that you want.
KH |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2007-05-20 : 04:09:05
|
| ok I just went through it again -- i realize that if i do a left outer join it will return the first school but now my issue is how can I search where schoolid=4 or schoolid=5 or schoolid=10 searching through all the records in the members_schools(I want the result to actually return the member but look through all the members records in the school table) |
|
|
|
|
|
|
|
|
sql join help