SQL problem

cotton31

cotton31

cotton31

Associate
Joined
25 Feb 2007
Posts
905
Location
Midlands
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?
 
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.

Code: 
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.
 
Last edited:
Why the where 1 = 1?

Anyway..
Code: 
SELECT DISTINCT
    H.HouseID,
    PA.SubAtt AS Language,
    PA.SubAtt AS Religion
  FROM House H
  INNER JOIN Person P ON H.PersonID = P.PersonID
    INNER JOIN PersonAttributes PA ON P.PersonID = PA.PersonID
 
Dj_Jestar said:
Why the where 1 = 1?

Anyway..
Code: 
SELECT DISTINCT
    H.HouseID,
    PA.SubAtt AS Language,
    PA.SubAtt AS Religion
  FROM House H
  INNER JOIN Person P ON H.PersonID = P.PersonID
    INNER JOIN PersonAttributes PA ON P.PersonID = PA.PersonID
Click to expand...

Don't think that would work as you're not specifying the attribute types

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

Code: 
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'
 
Haircut said:
To the OP, try this - think it should do the job

Code: 
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'
Click to expand...

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?

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

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.
 
Left join will return a row even if there is no record in PersonAttributes table - but it will be null.

If we just had a standard inner join and there were no records in PersonAttributes of either of the types you're searching for then the query would return no rows.
As you said there could be 0 or more attributes for each person we need the left join to ensure the query returns properly.
 
You must log in or register to reply here.
Back
Top Bottom