hi,

i want to ask a simple question,

i have a sql server relational tables named "tbl_Contact" (w/c has a field of ID & ContactName) and "tbl_reviews" (w/c has ID,ContactCode & Reviews).

the tables relationship is one-to-many, now my question is, how can i display a single record per 'ContactCode' from 'tbl_review'??

thanks..

Hi,

I think your problems is with your table structure. What i would is create tree tables like this:

1. tbl_Contact (ID, ContactName)
2. tbl_ContactReviews (ID, IDContact, IDReviews)
3. tbl_Reviews (Id, Review)

This way you will be able to retrieve the correct information. Let me know if it helps or i didn't understand your questions correctly.

Thanks,

hi, thanks for your reply, in tbl_reviews i only want to display a single record for review per IDcontact.

for example,

i have two records in tbl_review which has unique ID, same IDcontact (w/c is from tbl_contacts) and Review.

now, i just want to get/display one review for that same IDcontact.

any suggestions, comments?

Ok,

so basically you want to do this: 

SELECT DISTINCT(IDContact), Review
FROM tbl_review

yep, but if i include the field 'review' from the select statement, redundant IDcontact showsup again, is there any way, subquries,etc.. to omit the same IDcontacts??

i really dont want to change my table structure, maybe we can get the result by doing subqueries...

create table tbl_review
(
 tblReviewID int identity(1,1),
 IDContact int,
 Review varchar(10)
)
insert into tbl_review values (1,'a')
insert into tbl_review values (1,'b')
insert into tbl_review values (2,'c')
insert into tbl_review values (2,'d')

SELECT DISTINCT IDContact,Review
FROM tbl_review t where tblReviewID =
 (select max(tblReviewID) from tbl_review where IDContact = t.IDContact )

output:

 1   b

 2  d

wow, thanks.