basic query for returning names and addresses

[Guest_imported]

Hi, I'm new to SQL and have a problem/query.

I want to return data from 2 tables, tblPerson and tblPersonAddress. A person can have many addresses.

I want to return all the addresses for each person (with id per_id), but I only want the persons name to be returned once.

Currently with the query below I get all addresses, but the names are returned with each address. How do I filter this?

Here's my current query:

SELECT

p.per_id, p.per_last_name,p.per_first_name, pa.add_id, pa.add_text, pa.add_county, pa.add_country

FROM tblPerson p, tblPersonAddress pa

WHERE p.per_id = pa.per_id

GROUP BY p.per_last_name,p.per_first_name,pa.add_id, pa.add_text, pa.add_county, pa.add_country, p.per_id


TIA,
Luke

 

[carp]

I don't think you can do what you want with standard SQL. What RDBMS are you using?

 

[Guest_imported]

I'm using SQL Server 7.0 but I also need the query to run in MS Access for testing purposes.

 

[krkX]

You cant do that with a query, only way to do so is to use 'Crystal Reports', with crystal you can set various options as you wish that the data should be displayed..

Good luck

krkX

 

[buttersoft]

Have you tried SELECT DISTINCT?

Steve Butterworth

 

[THoey]

Using SELECT DISTINCT on the name and the address will still return the name twice. Since Luke just wants the name once, I have to agree with Carp that this probably can't be done without using PL\SQL, cursors or coding... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[MikeJones]

I know this isn't gonna be of direct use to Luke H, but in SQL Plus you could use the break command to format the report in this way. I know he is using SQL Server and also needs it to work on Access so obviously SQL Plus commands aren't gonna be of much use, but maybe SQL Server / Access have some similar ones that someone knows about??

 

[MalcolmW]

The first half gets the just one address with a name, and the second half gets the remaining addresses without a name.
This uses standard SQL92, so it should be fairly independent of platform.
I think your best way to do this is with your reporting tool (as suggested above), not SQL though.
SELECT p.per_id
, p.per_last_name
, p.per_first_name
, pa.add_id
, pa.add_text
, pa.add_county
, pa.add_country
FROM tblPerson p INNER JOIN tblPersonAddress pa ON p.per_id = pa.per_id
WHERE pa.add_id IN (SELECT MIN(pa2.add_id)
FROM tblPersonAddress AS pa2
WHERE pa.per_id = pa2.per_id)
UNION
SELECT p.per_id
, '' AS per_last_name
, '' AS per_first_name
, pa.add_id
, pa.add_text
, pa.add_county
, pa.add_country
FROM tblPerson p INNER JOIN tblPersonAddress pa ON p.per_id = pa.per_id
WHERE pa.per_id NOT IN (SELECT MIN(pa2.add_id)
FROM tblPersonAddress AS pa2
WHERE pa.per_id = pa2.per_id)
ORDER BY 1, 4, 2 DESC, 3 DESC Malcolm