Avoid duplicate

[keenburn]

i have 2 tables Wall and Area.

Wall entries looks like this.

id SurveyID Description WallLength

11 001 ext12004 8.85
12 001 ext12004 8.85
13 001 ext12004 7.85
14 001 ext21999 9.75
15 001 ext21999 8.75

Area entries looks like this

id surveyID Description floor joistheight
131 001 ext12004 ground 0
132 001 ext12004 first .25
133 001 ext21999 ground 0
134 001 ext21999 first .27

I want my query to output all the records in wall table with the corresponding floor and joist height from table Area but without duplicates.
the result should be as given below

surveyID Description floor WallLength joistHeight
001 ext12004 ground 8.85 0
001 ext12004 ground 8.85 0
001 ext12004 ground 7.85 0
001 ext21999 ground 9.75 0
001 ext21999 ground 8.75 0


surveyID Description floor WallLength joistHeight
001 ext12004 first 8.85 .25
001 ext12004 first 8.85 .25
001 ext12004 first 7.85 .25
001 ext21999 first 9.75 .27
001 ext21999 first 8.75 .27

i have written a query

select w.surveyID, w.Description, a.floor, w.wallLength, a.joistHeight from wall w, area a where a.survey =001
and w.surveyid = 001 and a.floor = 'ground'

but this gives me 10 records instead of 5. and when the same query is run with a.floor = 'first' it gives me 5 which is the required result.
any pointers on how to get this result is highly appreciated.

[rmiao]

Try this:

select w.surveyID, w.Description, a.floor, w.wallLength, a.joistHeight from wall w, area a where a.Description = w.Description and a.floor = 'ground'

[keenburn]

but i need to match the survey id as well. it should be the same surveyid as the one currently being processed.

[rmiao]

Can add that in join condition like:

select w.surveyID, w.Description, a.floor, w.wallLength, a.joistHeight from wall w, area a where a.surveyID = w.surveyID and a.Description = w.Description and a.floor = 'ground'

[keenburn]

thanks. yeah the query is working fine. it was w.description = a.description that was missing.