Help!!! Urgent SQL Query Question

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)

Help!!! Urgent SQL Query Question

Author

Topic

pinecrest515
Starting Member

11 Posts

Posted - 2010-12-06 : 14:02:23

Dear Forumers,

I need help on a SQL statment:

Suppose I have the following table:

First_Name Last_Name Age_Group Vaccinated
John Smith Adult Yes
Joe Smith Child No
Mary Smith Child No
Sam Walton Adult Yes
Thomas Walton Child Yes
John Walton Child Yes
Casey James Adult Yes
Mary Scott Adult No
Andrew Johnson Adult Yes
Bryon Johnson Child Yes

I am trying to query for a list of all Adults whose children (who have same last name) have been Vaccinated.

Ideally, the query should output the following:
First_Name Last_Name Age_Group Vaccinated
Sam Walton Adult Yes
Andrew Johnson Adult Yes

How should I best construct this query?

Thanks in advance for your reply!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-06 : 14:35:45

What have you come up with so far?

Jim

Everyday I learn something that somebody else already knew

pinecrest515
Starting Member

11 Posts

Posted - 2010-12-06 : 14:39:01

Not much... It's been awhile since I worked on SQL

I am looking for something along these lines...

Select Distinct * From Table
Where Age_Group = ‘Adult’ AND has one or more Child with same ‘Last_Name’ AND each child is vaccinated

pinecrest515
Starting Member

11 Posts

Posted - 2010-12-06 : 15:03:27

This is what I have so far...I am getting an error in Microsoft Access saying there is a syntax error in the FROM clause...can anyone shed some light?

Select Distinct * From t1.Last_Name, t2.Last_Name, t1.Age_Group, t1.Vaccinated
FROM
(table1 t1
JOIN
table1 t2)
ON
t1.Last_Name = t2.Last_Name
Where
t2.Vaccinated = ‘Yes’

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-06 : 15:25:52

This should work for SQL. but if you are using Access, then you mught need to use a forum that is kore specific to Access:

SELECT DISTINCT
    A.*
FROM 
    MyTable AS A
INNER JOIN
    MYTable AS B
    ON A.Last_Name = B.Last_Name
    AND A.Age_Group = 'Adult'
    AND B.Age_Group = 'Child'
WHERE
    B.Vaccinated = 'Yes'

pinecrest515
Starting Member

11 Posts

Posted - 2010-12-06 : 15:47:51

Thanks Lamprey!

I tried the above, but got an error from Access saying JOIN expression not supported.

SELECT DISTINCT
A.*
FROM
[Table1] AS [A]
INNER JOIN
[Table1] AS [B]
ON [A].[Last_Name] = [B].[Last_Name]
AND [A].[Age_Group] = 'Adult'
AND [B].[Age_Group] = 'Child'
WHERE
[B].[Vaccinated] = 'Yes'

pinecrest515
Starting Member

11 Posts

Posted - 2010-12-06 : 15:52:17

Got it! Thanks!!

SELECT DISTINCT
A.*
FROM
[Table1] AS [A]
INNER JOIN
[Table1] AS [B]
ON [A].[Last_Name] = [B].[Last_Name]
WHERE
[A].[Age_Group] = 'Adult'
AND [B].[Age_Group] = 'Child'
AND
[B].[Vaccinated] = 'Yes'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources