Select recs having at least 1 rec in other table

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 2000 Forums

Transact-SQL (2000)

Select recs having at least 1 rec in other table

Author

Topic

Apollois
Starting Member

49 Posts

Posted - 2004-02-02 : 15:53:42

How do I select all persons who have at least one record in another table?

For example:

Table Contacts
============
Contact_PK int
LName varcar(50)

Table Events
==============
Event_PK int
Contact_FK int
Event_Type int

I need to select all Contacts who have at least one Event with an Event_Type = 5

I only want one record per Contact, even if the Contact has several records in the Event table.

TIA.

Best Regards,
Jim

brooks
Starting Member

3 Posts

Posted - 2004-02-02 : 16:25:33

Try This:


SELECT C.*
FROM CContacts AS C
INNER JOIN 
   (SELECT Contact_FK, Event_Type 
    FROM CEvents
    GROUP BY Contact_FK, Event_Type
    HAVING Event_Type = 5) AS E ON (C.Contact_PK = E.Contact_FK)

brooks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-02 : 16:38:32

Here is another method:


SELECT c.* 
FROM contacts c
WHERE c.contact_pk IN (SELECT contact_fk FROM events WHERE event_type = 5)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-02-02 : 20:27:58

And another:

SELECT * FROM Contacts C
WHERE EXISTS(SELECT * FROM Events WHERE Contact_FK=C.Contact_PK AND Event_Type=5)

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-02-03 : 09:05:16

Or:

SELECT	c.* 
FROM	Contacts c JOIN Events e
	 ON  c.Contact_FK = e.Contact_FK
	 AND e.Event_Type = 5

Apollois
Starting Member

49 Posts

Posted - 2004-02-03 : 17:10:22

Thanks for all the great suggestions guys!

I'm evaluating each of them to determine which will work best for my situation. When I'm done I'll post my results.

Thanks again! You're a great group and a great resource.

Best Regards,
Jim

Apollois
Starting Member

49 Posts

Posted - 2004-02-05 : 20:55:33

I want to thank everyone again for your suggestions. They were all helpful in stimlating my thoughts, and in helping me formuate my final approach.

As you may have guessed, my actual problem is somewhat more complex than the example I asked help for. I didn't want to bough you guys down in the details right away.

I have shown below my final approach. My original question related to the block labeled INNER CORE SELECTION. I certainly welcome your critique on it.

Here is the real problem:

How to obtain a selection of Participants who participated in the Challenge for the Ref Year (2003) AND were in a given PU that year, but ALSO participated in a Challenge in the comparision year (2002). IOW I need to compare the performance from one year to the next. Obviously, I only want people who were in the program both years.

So here's a query (prep for a SP) that builds this selection, and then uses it to collect some statistics:

================================================


DECLARE  @iRefYear      integer,
         @iCompareYear  integer,
         @iStatsYear    integer,
         @sPerfUnit     varchar(20)

SET @iRefYear     = 2003
SET @iCompareYear = 2002
SET @iStatsYear   = 2003
SET @sPerfUnit    = 'DWD'

SELECT @sPerfUnit AS PU, @iStatsYear AS StatsYear,  
      Count(*) AS Num_Par, p.Sex, 
      (Round(DATEDIFF(year, p.DOB, ad.Assess_Date), -1, 1)) 
      AS Age_range
   FROM Assessment_Data ad
   INNER JOIN Participants p ON p.Participant_PK = ad.Participant_FK
   INNER JOIN Register r ON r.Register_PK = ad.Register_FK
   INNER JOIN Challenges c ON c.Chal_PK = r.Chal_FK
      AND c.Chal_Year = @iStatsYear
   WHERE p.Participant_PK IN
         (
         ---THIS IS INNER CORE SELECTION NEEDED FOR A NUMBER OF SPs ---
         SELECT r.Participant_FK
            FROM Register r
            INNER JOIN Challenges c ON c.Chal_PK = r.Chal_FK
               AND c.Chal_Year = @iCompareYear
            WHERE r.Participant_FK IN
                  (  
                  SELECT r.Participant_FK
                     FROM Register r
                     INNER JOIN Challenges c 
                        ON c.Chal_PK = r.Chal_FK
                        AND c.Chal_Year = @iRefYear
                     INNER JOIN Performance_Units pu 
                        ON pu.Perf_Unit_PK = r.Perf_Unit_FK
                     WHERE pu.Performance_Unit = @sPerfUnit
                  )
         ---   END OF INNER CORE ---
         )
   GROUP BY Sex, (Round(DATEDIFF(year, DOB, ad.Assess_Date), -1, 1))
   ORDER BY Sex Desc, Age_Range

==============================================================

If you want/need me to post the schema for the above, I will.

The question I have now is: How can I reuse the INNER CORE selection in a number of SPs? I have a lot of statistics to calc, and they will each require a separate SP.

Any suggestions on how to reuse the INNER CORE SELECTION other than copy and paste?

Thanks again.

If you're still reading this point, the following description of my tables may be helpful:

1. Participants
- people who may or may not participate in the company's Health Program in any given year.
2. Challenges
- a collection of Health Programs issued each year
3. Register
- Records when a Participant registers to participate in a given Challenge (Health Program)
4. Assessment_Data
- Health & Fitness data collected on the Participant at a particular point in the Program.

Best Regards,
Jim

Apollois
Starting Member

49 Posts

Posted - 2004-02-05 : 21:20:42

Here are the relevant tables if anyone is interested:


CREATE TABLE [Participants] (
	[Participant_PK] [int] IDENTITY (1, 1) NOT NULL ,
	[FName] [varchar] (24) NOT NULL ,
	[MI] [varchar] (1) NULL ,
	[LName] [varchar] (30) NOT NULL ,
	[DOB] [smalldatetime] NULL ,
	[Sex] [varchar] (1) NULL ,
	CONSTRAINT [idxParticipants_ParticipantPK] PRIMARY KEY  NONCLUSTERED 
	(
		[Participant_PK]
	)
)

CREATE TABLE [Challenges] (
	[Chal_PK] [int] IDENTITY (1, 1) NOT NULL ,
	[Chal_Year] [int] NOT NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[Chal_PK]
	)
)


CREATE TABLE [Register] (
	[Register_PK] [int] IDENTITY (1, 1) NOT NULL ,
	[Chal_FK] [int] NOT NULL ,
	[Participant_FK] [int] NOT NULL ,
	[Perf_Unit_FK] [int] NOT NULL ,
	(
		[Register_PK]
	)
)


CREATE TABLE [Assessment_Data] (
	[AD_PK] [int] IDENTITY (1, 1) NOT NULL ,
	[Register_FK] [int] NULL ,
	[Assess_FK] [int] NOT NULL CONSTRAINT [DF__Temporary__Asses__52593CB8] DEFAULT (0),
	[Assess_Date] [smalldatetime] NULL ,
	[Height] [real] NULL CONSTRAINT [DF__Temporary__Heigh__571DF1D5] DEFAULT (0),
	[Weight] [real] NULL CONSTRAINT [DF__Temporary__Weigh__5812160E] DEFAULT (0),
	[BP_Systolic] [real] NULL CONSTRAINT [DF__Temporary__BP_Sy__59063A47] DEFAULT (0),
	[BP_Diastolic] [real] NULL CONSTRAINT [DF__Temporary__BP_Di__59FA5E80] DEFAULT (0),
	[Body_Fat_Percent] [real] NULL CONSTRAINT [DF__Temporary__Body___5AEE82B9] DEFAULT (0),
	CONSTRAINT [idxAssessmentData_ADPK] PRIMARY KEY  NONCLUSTERED 
	(
		[AD_PK]
	)
)

CREATE TABLE [Performance_Units] (
	[Perf_Unit_PK] [int] IDENTITY (1, 1) NOT NULL ,
	[Performance_Unit] [varchar] (20) NOT NULL ,
	CONSTRAINT [PK_Performance_Units] PRIMARY KEY  NONCLUSTERED 
	(
		[Perf_Unit_PK]
	)
)

Best Regards,
Jim

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-02-06 : 08:51:05

If you post some sample data with INSERT statements and an example of desired output you might get a faster response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources