TSQL question (join tables)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » TSQL question (join tables)

TSQL question (join tables)

Rate Topic

Display Mode

Topic Options

Author

Message

DZN61

Posted Friday, December 07, 2012 2:29 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 4:05 AM
Points: 24, Visits: 60

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

Post #1393934

Cadavre

Posted Friday, December 07, 2012 2:36 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:17 AM
Points: 2,239, Visits: 6,546

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

Not a DBA, just trying to learn

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/

If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...
(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #1393939

Evil Kraig F

Posted Friday, December 07, 2012 1:16 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:35 PM
Points: 5,722, Visits: 6,194

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1394217

DZN

Posted Sunday, December 09, 2012 3:28 AM

SSC Rookie

Group: General Forum Members
Last Login: Sunday, December 09, 2012 3:24 AM
Points: 29, Visits: 90

IF I do not specify the Part ID, this means that all Parts from the specified carID must be taken.
If I do specify the partId(s) then only those parts need to be retrieved.

Is it clear?

Tks for your input.

Post #1394388