TSQL question (join tables)

Question

TSQL question (join tables)

DZN61

SSChasing Mays

Points: 628
More actions
December 7, 2012 at 2:29 am

#271581

Dear all,
I have a question concerning a view UI need to create.
Let's me explain the situation.
I have 5 tables
Reports : which contains the name of the reports
Cars : which contains the name of the cars
Parts : which contains the name of parts
CarsAndParts : which contains the parts within cars
ReportsDetails : which contains the reportID, the CardId and the PartId
The challenge is the following.
If in the ReportDetails, I do not specify the PartId, the process must take all parts defined for the specific car.
If in the ReportDetails, I do specify the PartId(s), the process must take only the part(s) specified in the ReportDetails table.
I do not want to fill the partId column in the ReportDetails file cause, they are too many change(s) in the composition ....
Here the code to create the tables.
what kind of code should I have to handle this ?
USE [DBTEst]
GO
/****** Object: Table [dbo].[CarsAndParts] Script Date: 12/07/2012 10:23:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Parts]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Parts](
[Id] [int] NOT NULL,
[PartLabel] [nchar](10) NOT NULL,
CONSTRAINT [PK_Parts] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cars]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Cars](
[Id] [int] NOT NULL,
[CarLabel] [text] NULL,
CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CarsAndParts]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CarsAndParts](
[Id] [int] NOT NULL,
[CarId] [int] NOT NULL,
[PartId] [int] NOT NULL,
CONSTRAINT [PK_CarsAndParts] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Reports]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Reports](
[Id] [int] NOT NULL,
[ReportLabel] [nchar](10) NOT NULL,
CONSTRAINT [PK_Reports] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReportDetails]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ReportDetails](
[Id] [int] NOT NULL,
[ReportId] [int] NOT NULL,
[CarId] [int] NOT NULL,
[PartId] [int] NULL,
CONSTRAINT [PK_ReportDetails] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ReportDetails_Cars]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReportDetails]'))
ALTER TABLE [dbo].[ReportDetails] WITH CHECK ADD CONSTRAINT [FK_ReportDetails_Cars] FOREIGN KEY([CarId])
REFERENCES [dbo].[Cars] ([Id])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ReportDetails_Cars]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReportDetails]'))
ALTER TABLE [dbo].[ReportDetails] CHECK CONSTRAINT [FK_ReportDetails_Cars]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ReportDetails_CarsAndParts]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReportDetails]'))
ALTER TABLE [dbo].[ReportDetails] WITH CHECK ADD CONSTRAINT [FK_ReportDetails_CarsAndParts] FOREIGN KEY([PartId])
REFERENCES [dbo].[CarsAndParts] ([Id])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ReportDetails_CarsAndParts]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReportDetails]'))
ALTER TABLE [dbo].[ReportDetails] CHECK CONSTRAINT [FK_ReportDetails_CarsAndParts]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ReportDetails_Reports]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReportDetails]'))
ALTER TABLE [dbo].[ReportDetails] WITH CHECK ADD CONSTRAINT [FK_ReportDetails_Reports] FOREIGN KEY([ReportId])
REFERENCES [dbo].[Reports] ([Id])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ReportDetails_Reports]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReportDetails]'))
ALTER TABLE [dbo].[ReportDetails] CHECK CONSTRAINT [FK_ReportDetails_Reports]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CarsAndParts_Cars]') AND parent_object_id = OBJECT_ID(N'[dbo].[CarsAndParts]'))
ALTER TABLE [dbo].[CarsAndParts] WITH CHECK ADD CONSTRAINT [FK_CarsAndParts_Cars] FOREIGN KEY([CarId])
REFERENCES [dbo].[Cars] ([Id])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CarsAndParts_Cars]') AND parent_object_id = OBJECT_ID(N'[dbo].[CarsAndParts]'))
ALTER TABLE [dbo].[CarsAndParts] CHECK CONSTRAINT [FK_CarsAndParts_Cars]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CarsAndParts_Parts]') AND parent_object_id = OBJECT_ID(N'[dbo].[CarsAndParts]'))
ALTER TABLE [dbo].[CarsAndParts] WITH NOCHECK ADD CONSTRAINT [FK_CarsAndParts_Parts] FOREIGN KEY([PartId])
REFERENCES [dbo].[Parts] ([Id])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CarsAndParts_Parts]') AND parent_object_id = OBJECT_ID(N'[dbo].[CarsAndParts]'))
ALTER TABLE [dbo].[CarsAndParts] NOCHECK CONSTRAINT [FK_CarsAndParts_Parts]
GO

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Sample data and expected results based on the sample data would be useful. See this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, for help on how best to post sample data.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

DZN61 (12/7/2012)
I have 5 tables
Reports : which contains the name of the reports
Cars : which contains the name of the cars
Parts : which contains the name of parts
CarsAndParts : which contains the parts within cars
ReportsDetails : which contains the reportID, the CardId and the PartId
The challenge is the following.
If in the ReportDetails, I do not specify the PartId, the process must take all parts defined for the specific car.
If in the ReportDetails, I do specify the PartId(s), the process must take only the part(s) specified in the ReportDetails table.
I do not want to fill the partId column in the ReportDetails file cause, they are too many change(s) in the composition ....
Here the code to create the tables.
what kind of code should I have to handle this ?

You've... skipped a step in this discussion, at least from my perspective. If you don't specify a PartID, what is the process that's taking all parts? I'm afraid I'm not sure what you're even trying to do with the data to be able to help you find your solution.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA