none
Help with a query

    Question

  • It's been a while since I have had to write any queries more complex than your typical select/insert/update queries and I am having trouble getting this one going. I do have experience using joins; however, that is not enough at this point.

    I have 3 tables participating in a many to many relationship.

    Table 1: Juveniles has a pk called "id"

    Table 2: GuardianAssociations has a pk called "id" and two fks called "JuvenileId" and "GuardianId"

    Table 3: GuardiansSource has a pk called "id";

    I basically want to select any siblings of a juvenile object (table 1) by use of the pk from table 1.

    so far I have 

    CREATE PROCEDURE [dbo].[FetchJuvenileSiblingsByJuvenileId]
    	@id bigint
    AS
    	SELECT Juveniles.id, Juveniles.ParentOrg, Juveniles.ParentUser, Juveniles.FirstName, Juveniles.MiddleName, Juveniles.LastName, Juveniles.[Address], Juveniles.Age, Juveniles.Birthdate, Juveniles.City, Juveniles.EyeColor, Juveniles.Gender, Juveniles.HairColor
    	, Juveniles.Height, Juveniles.MainPhone, Juveniles.Race, Juveniles.[State], Juveniles.[Weight], Juveniles.Zip, Juveniles.School FROM Juveniles
    	INNER JOIN GuardianAssociations ON Juveniles.id = GuardianAssociations.JuvenileId
    RETURN 0

    I need to select all the GuardianIds associated with the juvenile's pk, place them in a list (only if they are father or mother which is referenced by a column in the table called 'Type') then select all Juvenile Ids in the joined table that are associated with either mother or father's pk value and return all the columns which I have above from the juvenile table that belong to the juvenile Ids associated with the mother and father ids. How could I go about this?


    • Edited by MARV102 Tuesday, October 15, 2013 7:35 PM grammar
    Tuesday, October 15, 2013 7:34 PM

Answers

  • Try this..

    Declare @Id int
    set @Id=1
    
    ;With DistSiblings
    as
    (
    SELECT 
    	Distinct sib_ga.JuvenileId as ID
    FROM 
    	GuardianAssociations juv_ga 
    	INNER JOIN GuardiansSource gs ON gs.id=juv_ga.GuardianId	and gs.type  in('Mother','Father')
    	INNER JOIN GuardianAssociations sib_ga ON sib_ga.GuardianId=gs.id 	
    WHERE
    	juv_ga.JuvenileId = @Id and sib_ga.JuvenileId<>@Id
    	)
    select * from Juveniles 
    join DistSiblings on Juveniles.Id=DistSiblings.Id
    	

    If you comment out the highlighted and section you will get the input juvenile as well


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer


    • Edited by Vinay Valeti Wednesday, October 16, 2013 1:16 AM
    • Marked as answer by MARV102 Wednesday, October 16, 2013 7:00 PM
    Wednesday, October 16, 2013 1:16 AM

All replies

  • Please post complete DDL of your Tables that involve with this query (probably four tables) and provide DML to populate with some sample data and the expected output result.


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Tuesday, October 15, 2013 8:00 PM
  • In order to forumate the query to need to DDL of the 3 tables and some data for these tables..Without data and table schema it is hard to design the query.


    Thanks, hsbal

    Tuesday, October 15, 2013 8:15 PM
  • How does GuardiansSource enter the picture?

    personally, I prefer to split up 3 table joins into seperate chunks, which also helps in debugging. In this case , something like select GA.id, GA.juvenile.id,GA.guardianid,GA.type from GuardianAssociations GA where TYPE $ "MF"  inner join on Juveniles.id = GuardianAssociations.JuvenileId into holdingTable

    and then using holdingTable to finish up your select'ions.


    TechNet

    Tuesday, October 15, 2013 8:32 PM
  • Here is the dll guys.

    USE [Juvenile_Information_System]
    GO
    /****** Object:  Table [dbo].[GuardianAssociations]    Script Date: 10/15/2013 3:35:58 PM ******/
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[GuardianAssociations](
    	[id] [bigint] IDENTITY(1,1) NOT NULL,
    	[JuvenileId] [bigint] NOT NULL,
    	[GuardianId] [bigint] NOT NULL,
     CONSTRAINT [PrimaryKey_b1b6f89e-3cf0-49a3-8d95-5a85621b69f2] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    )
    
    GO
    /****** Object:  Table [dbo].[GuardiansSource]    Script Date: 10/15/2013 3:35:59 PM ******/
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[GuardiansSource](
    	[Type] [nvarchar](50) NOT NULL,
    	[FirstName] [nvarchar](100) NOT NULL,
    	[id] [bigint] IDENTITY(1,1) NOT NULL,
    	[LastName] [nvarchar](50) NULL,
    	[PrimaryPhone] [nvarchar](50) NULL,
    	[SecondaryPhone] [nvarchar](50) NULL,
    	[City] [nvarchar](50) NULL,
    	[State] [nvarchar](2) NULL,
    	[Address] [nvarchar](50) NULL,
     CONSTRAINT [PrimaryKey_ac017243-416c-46ca-8b08-effd89b5b6e9] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    )
    
    GO
    /****** Object:  Table [dbo].[Juveniles]    Script Date: 10/15/2013 3:36:00 PM ******/
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Juveniles](
    	[id] [bigint] IDENTITY(1,1) NOT NULL,
    	[ParentOrg] [int] NOT NULL,
    	[ParentUser] [int] NOT NULL,
    	[FirstName] [nvarchar](100) NULL,
    	[MiddleName] [nvarchar](100) NULL,
    	[LastName] [nvarchar](100) NULL,
    	[GangId] [int] NULL,
    	[Address] [nvarchar](200) NULL,
    	[Age] [int] NULL,
    	[Birthdate] [datetime] NULL,
    	[City] [nvarchar](200) NULL,
    	[EyeColor] [nvarchar](50) NULL,
    	[Gender] [nvarchar](10) NULL,
    	[HairColor] [nvarchar](50) NULL,
    	[Height] [nvarchar](10) NULL,
    	[MainPhone] [bigint] NULL,
    	[Race] [nvarchar](50) NULL,
    	[State] [nvarchar](2) NULL,
    	[Weight] [int] NULL,
    	[Zip] [int] NULL,
    	[School] [nvarchar](200) NULL,
     CONSTRAINT [PrimaryKey_51d9c022-9c37-4bc8-b029-7df44cd6875c] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    )
    
    GO
    SET IDENTITY_INSERT [dbo].[GuardianAssociations] ON 
    
    INSERT [dbo].[GuardianAssociations] ([id], [JuvenileId], [GuardianId]) VALUES (10005, 1, 3)
    INSERT [dbo].[GuardianAssociations] ([id], [JuvenileId], [GuardianId]) VALUES (10006, 1, 10006)
    SET IDENTITY_INSERT [dbo].[GuardianAssociations] OFF
    SET IDENTITY_INSERT [dbo].[GuardiansSource] ON 
    
    INSERT [dbo].[GuardiansSource] ([Type], [FirstName], [id], [LastName], [PrimaryPhone], [SecondaryPhone], [City], [State], [Address]) VALUES (N'Mother', N'Pamela', 3, N'Anderson', N'632-329-2930', N'324-242-4242', N'Winettka', N'IL', N'3512 W. Roosevelt Rd.')
    INSERT [dbo].[GuardiansSource] ([Type], [FirstName], [id], [LastName], [PrimaryPhone], [SecondaryPhone], [City], [State], [Address]) VALUES (N'Father', N'Jacob', 10006, N'Marly', NULL, NULL, NULL, NULL, NULL)
    SET IDENTITY_INSERT [dbo].[GuardiansSource] OFF
    SET IDENTITY_INSERT [dbo].[Juveniles] ON 
    
    INSERT [dbo].[Juveniles] ([id], [ParentOrg], [ParentUser], [FirstName], [MiddleName], [LastName], [GangId], [Address], [Age], [Birthdate], [City], [EyeColor], [Gender], [HairColor], [Height], [MainPhone], [Race], [State], [Weight], [Zip], [School]) VALUES (1, 1, 1, N'John', N'Allen', N'Doe', NULL, N'2738 E. Renalt Ave.', 17, CAST(0x000087EC00000000 AS DateTime), N'Chicago', N'Black', N'Male', N'Black', N'e9', 7731293029, N'White', N'IL', 167, 14245, NULL)
    INSERT [dbo].[Juveniles] ([id], [ParentOrg], [ParentUser], [FirstName], [MiddleName], [LastName], [GangId], [Address], [Age], [Birthdate], [City], [EyeColor], [Gender], [HairColor], [Height], [MainPhone], [Race], [State], [Weight], [Zip], [School]) VALUES (2, 1, 1, N'Brandon', N'Jamie', N'Swanson', NULL, N'5232 E. Eader St.', 15, CAST(0x0000882A0119CF3E AS DateTime), N'Chicago', N'Black', N'Male', N'Black', N'f0', 7731234242, N'White', N'IL', 176, 45323, NULL)
    INSERT [dbo].[Juveniles] ([id], [ParentOrg], [ParentUser], [FirstName], [MiddleName], [LastName], [GangId], [Address], [Age], [Birthdate], [City], [EyeColor], [Gender], [HairColor], [Height], [MainPhone], [Race], [State], [Weight], [Zip], [School]) VALUES (3, 1, 1, N'Allen', N'Wane', N'Watson', NULL, N'2453 E. Dundee Rd.', 17, CAST(0x0000882A011A9A49 AS DateTime), N'Chicago', N'Black', N'Male', N'Black', N'f2', 2424242424, N'White', N'IL', 174, 15225, NULL)
    INSERT [dbo].[Juveniles] ([id], [ParentOrg], [ParentUser], [FirstName], [MiddleName], [LastName], [GangId], [Address], [Age], [Birthdate], [City], [EyeColor], [Gender], [HairColor], [Height], [MainPhone], [Race], [State], [Weight], [Zip], [School]) VALUES (4, 1, 1, N'Jacob', N'Jane', N'Marely', NULL, N'3674 E. Addington Ave.', 17, CAST(0x00008B0500000000 AS DateTime), N'Chicago', N'Black', N'Male', N'Black', N'f8', 3372382372, N'White', N'IL', 124, 42412, NULL)
    INSERT [dbo].[Juveniles] ([id], [ParentOrg], [ParentUser], [FirstName], [MiddleName], [LastName], [GangId], [Address], [Age], [Birthdate], [City], [EyeColor], [Gender], [HairColor], [Height], [MainPhone], [Race], [State], [Weight], [Zip], [School]) VALUES (5, 1, 1, N'Trent', N'Wallace', N'Willows', NULL, N'2342 E. Elanore', 18, CAST(0x00008C7200000000 AS DateTime), N'Chicago', N'Black', N'Male', N'Black', N'e10', 1231231231, N'White', N'IL', 152, 12424, NULL)
    INSERT [dbo].[Juveniles] ([id], [ParentOrg], [ParentUser], [FirstName], [MiddleName], [LastName], [GangId], [Address], [Age], [Birthdate], [City], [EyeColor], [Gender], [HairColor], [Height], [MainPhone], [Race], [State], [Weight], [Zip], [School]) VALUES (6, 1, 1, N'Jacob', N'Allen', N'Willos', NULL, N'4242 E Ashton Ave', 16, CAST(0x00008C7200000000 AS DateTime), N'Chicago', N'Black', N'Male', N'Black', N'e11', 2525252525, N'White', N'IL', 522, 45252, NULL)
    INSERT [dbo].[Juveniles] ([id], [ParentOrg], [ParentUser], [FirstName], [MiddleName], [LastName], [GangId], [Address], [Age], [Birthdate], [City], [EyeColor], [Gender], [HairColor], [Height], [MainPhone], [Race], [State], [Weight], [Zip], [School]) VALUES (7, 1, 1, N'Hupert', N'blah', N'Blitzington', NULL, N'1231231', 16, CAST(0x0000882B00BC14FE AS DateTime), N'sfasd', N'Black', N'Male', N'Black', N'a0', 1234551231, N'White', N'IL', 0, 12345, NULL)
    INSERT [dbo].[Juveniles] ([id], [ParentOrg], [ParentUser], [FirstName], [MiddleName], [LastName], [GangId], [Address], [Age], [Birthdate], [City], [EyeColor], [Gender], [HairColor], [Height], [MainPhone], [Race], [State], [Weight], [Zip], [School]) VALUES (8, 1, 1, N'Jacob', N'Ashton', N'Kutcher', NULL, N'12312 E Monroe', 14, CAST(0x0000882B00BD7581 AS DateTime), N'Chicago', N'Black', N'Male', N'Black', N'a0', 1231231231, N'White', N'IL', 123, 12312, NULL)
    INSERT [dbo].[Juveniles] ([id], [ParentOrg], [ParentUser], [FirstName], [MiddleName], [LastName], [GangId], [Address], [Age], [Birthdate], [City], [EyeColor], [Gender], [HairColor], [Height], [MainPhone], [Race], [State], [Weight], [Zip], [School]) VALUES (9, 1, 1, N'Gilbert', N'Joshua', N'Potter', NULL, N'1234 E Test St.', 15, CAST(0x0000882F00000000 AS DateTime), N'Chicago', N'Black', N'Male', N'Black', N'e10', 2512512512, N'White', N'IL', 125, 14525, N'St. Carol')
    SET IDENTITY_INSERT [dbo].[Juveniles] OFF
    ALTER TABLE [dbo].[GuardianAssociations]  WITH CHECK ADD  CONSTRAINT [FK_GuardianAssociations_0] FOREIGN KEY([GuardianId])
    REFERENCES [dbo].[GuardiansSource] ([id])
    ON DELETE CASCADE
    GO
    ALTER TABLE [dbo].[GuardianAssociations] CHECK CONSTRAINT [FK_GuardianAssociations_0]
    GO
    ALTER TABLE [dbo].[GuardianAssociations]  WITH CHECK ADD  CONSTRAINT [FK_GuardianAssociations_1] FOREIGN KEY([JuvenileId])
    REFERENCES [dbo].[Juveniles] ([id])
    GO
    ALTER TABLE [dbo].[GuardianAssociations] CHECK CONSTRAINT [FK_GuardianAssociations_1]
    GO
    

    Tuesday, October 15, 2013 8:38 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    Your narrative is fundamentally wrong! Not a little wrong, but fundamentally wrong. Fundamentally wrong. 

    Juvenile is a temporal status, not a set of entities. There is no generic, magical, universal “id” in RDBMS. See how it changes from table to table? Wow, magic! Can it be a squid next week? 

    Associations are a relationship, not an entity. We will start with entities. I am using ISO, ANSI and USPS standards for my guessing. 

    CREATE TABLE Juveniles
    (kid_id CHAR(10) NOT NULL PRIMARY KEY,
     parent_org ??, 
     parent_user ??,
     first_name VARCHAR(25) NOT NULL, 
     middle_name VARCHAR(25) NOT NULL, 
     last_name VARCHAR(25) NOT NULL, 
     street_address VARCHAR(35) NOT NULL, 
     city_name VARCHAR(25) NOT NULL, 
     state_code CHAR(2) NOT NULL, 
     zip_code CHAR(5) NOT NULL, 
     birth_date DATE NOT NULL,
     eye_color ??, 
     sex_code CHAR(1) NOT NULL
       CHECK (sex_code IN ('0', '1', '2', '9')), 
     hair_color ??, 
     kid_height, main_phone_nbr, race_code, 
     kid_weight INTEGER NOT NULL
       CHECK (kid_weight > 0), 
     school_name VARCHAR(25) NOT NULL,
     ..); 

    CREATE TABLE Guardians
    (guardian_id CHAR(10) NOT NULL PRIMARY KEY,
     guardian_type CHAR(7) NOT NULL 
       CHECK (guardian_type IN ('mother', 'father',.. ))
     ..);

    Now we have to model a relationship, which I will guess without any help from you, is one guardian to many kids. This is a common idiom. 

    CREATE TABLE Guardianships
    (kid_id CHAR(10) NOT NULL UNIQUE 
     REFERENCES Juveniles(kid_id)
     guardian_id CHAR(10) NOT NULL 
     REFERENCES Guardians (guardian_id),
     PRIMARY KEY (kid_id, guardian_id),
     ..);

    In RDBMS we have real keys; they are attributes with constraints. Why did you invent a separate, magical “id”?  Since you do not use the magical “id” for math, why would you waste a BIGINT on it?? Did you actually use a BIGINT IDENTITY!!?? That is a disaster. 

    >> I basically want to select any siblings of a juvenile object by use of the pk from table 1. <<

    How is a sibling tested? What is the business rule? No check digits, etc? Which “id” is that parameter? Why? is there both “age” and “birth_date”; I do not want to write the constraint to enforce valid data! The ISO term is “sex_code” and gender which means something else. 

    Why do you have a parameter you never use?? My guess is that a sibling group have the same guardian. More guessing ...

    CREATE PROCEDURE Fetch_Siblings
    (@in_guardian_id CHAR(10))
    AS
    SELECT J.kid_id, J.parent_org, J.parentuser, J.firstname, J.middle_name, J.last_name, J.street_address, J.birth_date, city_name, J.eye_color, J.sex_code, J.hair_color, J.kid_height, J.mainphone, J.race_code, J.state_code, J.kid_weight, J.zip_code, J.school_name
      FROM Juveniles AS J,
           Guardianships AS G
    WHERE J.kid_id = G.kid_id
      AND G.guardian_id = @in_guardian_id
      AND G.guardian_type IN ('mother', 'father')); 

    >> I need to select all the guardian_ids associated with the juvenile's kid-id and place them in a list (only if they are father or mother which is referenced by a column in the table called 'Type')<<

    No, there is no generic “type” in RDBMS. And there is no specific code in your posting. And there never a table called “Type” unless the encoding is large and dynamic; this is small and static. We use a CHECK() in the DDL. 80-95% of SQL is in DDL. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, October 15, 2013 9:24 PM
  • One way, (I just show some of the columns from the Juvenile and Guardian table, but you can use the same techniques to add whatever other columns you need)

    	;WITH cte As
    	(SELECT j.id As JuvId, j.FirstName As JuvFirstName, j.MiddleName As JuvMiddleName, j.LastName As JuvLastName,
    	  s.Type, s.FirstName As GuardFirstName, s.LastName As GuardLastName,
    	  Row_Number() Over(Partition By j.id Order By s.Type) As rn 
    	FROM Juveniles j
    	INNER JOIN GuardianAssociations a ON j.id = a.JuvenileId
    	INNER JOIN GuardiansSource s ON a.GuardianId = s.id And s.Type In ('Mother', 'Father'))
    	Select c.JuvId, Max(c.JuvFirstName) As JuvFirstName, Max(c.JuvMiddleName) As JuvMiddleName, Max(c.JuvLastName) As JuvLastName,
    	  Max(Case When rn = 1 Then c.Type End) As GuardOneType,
    	  Max(Case When rn = 1 Then c. GuardFirstName End) As GuardOneFirstName,
    	  Max(Case When rn = 1 Then c.GuardLastName End) As GuardOneLastName,
    	  Max(Case When rn = 2 Then c.Type End) As GuardTwoType,
    	  Max(Case When rn = 2 Then c. GuardFirstName End) As GuardTwoFirstName,
    	  Max(Case When rn = 2 Then c.GuardLastName End) As GuardTwoLastName
    	From cte c
    	Group By c.JuvId;
    Tom

    Tuesday, October 15, 2013 10:27 PM
  • SELECT 
    	j2.id, j2.ParentOrg, j2.ParentUser, j2.FirstName, j2.MiddleName, j2.LastName, j2.[Address], j2.Age, j2.Birthdate, j2.City, j2.EyeColor, j2.Gender, j2.HairColor, j2.Height, j2.MainPhone, j2.Race, j2.[State], j2.[Weight], j2.Zip, j2.School 
    FROM 
    	Juveniles j1
    	INNER JOIN GuardianAssociations ON j1.id = GuardianAssociations.JuvenileId
    	INNER JOIN Juveniles j2 ON j2.GuardianId = GuardianAssociations.Guardian
    WHERE
    	j1.id = @id 


    Muthukrishnan Ramasamy
    net4.rmkrishnan.net
    Use only what you need, Reduce global warming

    Tuesday, October 15, 2013 10:40 PM
  • Try this..

    Declare @Id int
    set @Id=1
    
    ;With DistSiblings
    as
    (
    SELECT 
    	Distinct sib_ga.JuvenileId as ID
    FROM 
    	GuardianAssociations juv_ga 
    	INNER JOIN GuardiansSource gs ON gs.id=juv_ga.GuardianId	and gs.type  in('Mother','Father')
    	INNER JOIN GuardianAssociations sib_ga ON sib_ga.GuardianId=gs.id 	
    WHERE
    	juv_ga.JuvenileId = @Id and sib_ga.JuvenileId<>@Id
    	)
    select * from Juveniles 
    join DistSiblings on Juveniles.Id=DistSiblings.Id
    	

    If you comment out the highlighted and section you will get the input juvenile as well


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer


    • Edited by Vinay Valeti Wednesday, October 16, 2013 1:16 AM
    • Marked as answer by MARV102 Wednesday, October 16, 2013 7:00 PM
    Wednesday, October 16, 2013 1:16 AM
  • @Vinay Valeti. Thank you, I can learn a lot from this query and it worked great.

    @Celko - I appreciate all the knowledge in your post; however, we are not fortunate enough to have a database guru like you on our team. We are a start up and are doing what we can with what we have. Everything is working great as is without any of what you posted and considering a lot of the questions you posed were already answered in my post. Thank you.

    Wednesday, October 16, 2013 7:04 PM