SEO

#1 (**permalink**) 02-29-2008, 08:08 AM

This is probably straight forward, but let's see:

Say I have a Family which includes an address field table and a related
FamilyMembers table which includes an age field.

I need to output a report on each Family where there is at least one
family member with an age over 16.

Any help gratefully received!

#2 (**permalink**) 02-29-2008, 08:08 AM

Can you specify the details of the stuctures of both the tables?

Regards
Debian

*** Sent via Developersdex http://www.developersdex.com ***

#3 (**permalink**) 02-29-2008, 08:08 AM

You need to join those two tables. Then you can filter them on condition(s)
you need. This would be one way of doing it, please specifiy structure and
expected result if this isnt good enough:

select ColumnList --- list of columns you need returned
from
Family f
inner join FamilyMembers fm on f.PK_Family = fm.PK_Family
where
fm.Age > 16

--- PK_Family is Primary Key on Family table

MC

"RichMUK" <support@insurance.uk.com> wrote in message
news:1119522354.673863.165840@g49g2000cwa.googlegr oups.com...
> This is probably straight forward, but let's see:
>
> Say I have a Family which includes an address field table and a related
> FamilyMembers table which includes an age field.
>
> I need to output a report on each Family where there is at least one
> family member with an age over 16.
>
> Any help gratefully received!
>

#4 (**permalink**) 02-29-2008, 08:08 AM

Thanks debian & MC.

MCs query works fine but I need to report just one record per family.
In this example the tables are:

Family.FamilyID [id]
Family.FamilyName
Family.Address

and

FamilyMembers.FamilyMemberID [id]
FamilyMembers.FamilyID
FamilyMembers.FirstName
FamilyMembers.Age

SELECT f.FamilyID, f.FamilyName, f.Address
FROM dbo.Family f INNER JOIN
dbo.FamilyMembers fm ON f.FamilyID = fm.FamilyID
WHERE (fm.Age > 16)

MC, the query you suggest repeats the same Family. record by the number
of FamilyMembers in the query.
I hope this makes sense.

Rich

#5 (**permalink**) 02-29-2008, 08:09 AM

RichMUK (support@insurance.uk.com) writes:
> MCs query works fine but I need to report just one record per family.
> In this example the tables are:
>
> Family.FamilyID [id]
> Family.FamilyName
> Family.Address
>
> and
>
> FamilyMembers.FamilyMemberID [id]
> FamilyMembers.FamilyID
> FamilyMembers.FirstName
> FamilyMembers.Age
>
> SELECT f.FamilyID, f.FamilyName, f.Address
> FROM dbo.Family f INNER JOIN
> dbo.FamilyMembers fm ON f.FamilyID = fm.FamilyID
> WHERE (fm.Age > 16)
>
> MC, the query you suggest repeats the same Family. record by the number
> of FamilyMembers in the query.

SELECT f.FamilyID, f.FamilyName, f.Address
FROM dbo.Family f
WHERE EXISTS (SELECT *
FROM dbo.FamilyMembers fm
WHERE f.FamilyID = fm.FamilyID
AND fm.Age > 16)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Beginners Question