Retrieve specific row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retrieve specific row

Author

Topic

paulafernandes
Starting Member

10 Posts

Posted - 2008-01-23 : 11:44:22

Hello everyone!
I'm new here, and I have a problem, I will try to explain it...

I'm working with SQL Server Express 2005.
I have two tables:
Table1 - Person
Id_Person (int)
Name (nvarchar(200))
Age (int)

Table2 - Foto
Id_Foto (int)
Id_Person (int)
Foto (nvarchar(100))

The scenario is that every person may have more than one foto.
I need a query where I have EVERY person's data and only ONE foto, in the case that there's more than one foto, or the foto field should be null if there is no foto.

I have done all kind of attempts, by I can't seem to get an answeer!
I've tried this:

SELECT     a.Id_Person, b.Foto
FROM         Person a
			LEFT OUTER JOIN
			(
				SELECT DISTINCT Id_Person, Foto FROM Foto
			) b
			ON a.Person = b.Person
GROUP BY a.Person, b.Foto

But I keep getting all the rows with the fotos of one person.

Maybe it's not possible to do, so I'm asking the experts!

Thank you in advance!
Paula

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-23 : 11:52:20

[code]SELECT a.Id_Person, b.Foto
FROM Person a
LEFT OUTER JOIN
(
SELECT Id_Person, max(Foto) as Foto FROM Foto
Group by Id_Person
) b
ON a.ID_Person = b.ID_Person[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

paulafernandes
Starting Member

10 Posts

Posted - 2008-01-23 : 13:19:10

Harsh Athalye,

Thank you, thank you, thank you!!!!
That's perfect!

Paula

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources