471,108 Members | 1,279 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

Beginners Question

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!

Jul 23 '05 #1
4 967
Can you specify the details of the stuctures of both the tables?

Regards
Debian

*** Sent via Developersdex http://www.developersdex.com ***
Jul 23 '05 #2
MC
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" <su*****@insurance.uk.com> wrote in message
news:11**********************@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!

Jul 23 '05 #3
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

Jul 23 '05 #4
RichMUK (su*****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by blah | last post: by
4 posts views Thread by Eggnog | last post: by
6 posts views Thread by William Foster | last post: by
18 posts views Thread by John Salerno | last post: by
2 posts views Thread by =?Utf-8?B?UmFrZXNo?= | last post: by
4 posts views Thread by aman firoz | last post: by
reply views Thread by Dual_b00t | last post: by
19 posts views Thread by yltkhuu | last post: by
6 posts views Thread by Lars | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.