SQL problem

[cotton31]

I have a query which contains a couple of subqueries looking at the same field (different WHERE clauses). For some results the sub queries return two sets of differing data, so I have two rows for that result.

I only want it to display the one of the rows - the row with a certain value for one of the subqueries. How can I get it to only show one row, based on what is returned by the subquery?

 

[cotton31]

Bit of background: the database has a 'Houses' table, a 'Person' table and a 'Person Attributes' table. Each house can hold 1 or more persons and each person can have 0 or more attributes, with each attribute having its own sub attribute.

SELECT DISTINCT H.HouseID
,(SELECT PA.SubAtt FROM PersonAttributes PA WHERE Att = 'Language') AS Language
,(SELECT PA.SubAtt FROM PersonAttributes PA WHERE Att = 'Religion') AS Religion

FROM House H INNER JOIN
Person P ON H.PersonID = P.PersonID INNER JOIN
PersonAttributes PA ON P.PersonID = PA.PersonID

WHERE
1 = 1

Most of the time, if there are more than 1 persons in the house, they will both have the same Language/Religion, and the DISTINCT operator gets rid of these.

However, with more than one person in the house with different religons/languages, it pulls these through as seperate rows. I only want it to pull through one row per house.

How can I do this?

I know this is a bit lengthy! Cheers for taking a look.

 

[cotton31]

To the OP, try this - think it should do the job

SELECT DISTINCT h.HouseID,
       paLang.SubAtt "Language",
       paRel.SubAtt "Religion"     
FROM House h
JOIN Person p
  ON h.PersonID = p.PersonID
LEFT JOIN PersonAttributes paLang
  ON paLang.PersonID = p.PersonID
 AND paLang.Att = 'Language'
LEFT JOIN PersonAttributes paRel
  ON paRel.PersonID = p.PersonID
 AND paRel.Att = 'Religion'

I'll give that a try in a bit - just out of interest, why does the LEFT JOIN make a difference opposed to INNER JOIN?

ah yes, you're right.. though really there should be an attributes type table and link it to attributes.

I know, that would make things much simpler! Unfortunately, I'm reporting on a piece of software at work which I can't amend, so I have to work with what I have.

Cheers for the help guys, I'll post back with the results.