3 Table Join Problem - SQL Server Forums

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

Transact-SQL (2008)

3 Table Join Problem

Author

Topic

BlackIce662
Starting Member

11 Posts

Posted - 2011-03-28 : 03:15:30

Hi so I have 3 tables with info I need and they're all connected via a MemberID I need 1 SQL Statement that gives the information from the 3 SELECT statements below. I don't want to use nested whiles to do this which is why I'm asking if it can all be done in one SQL Statement. I've provided my table create scripts below the 3 statements I need combined. I appreciate any help provided

SELECT MemberID, Member_Name, Map, Pic1, Pic2, Pic3, Specials_Pic1, Specials_Pic2, Logo
FROM Member
WHERE Active = 'Full'
ORDER By Member_Name Asc

SELECT Member.MemberID, Member.Member_Name, MemCat.Pic1 AS FacPic1, MemCat.Pic2 AS FacPic2, MemCat.Pic3 AS FacPic3, MemCat.Pic4 AS FacPic4, MemCat.Pic5 AS FacPic5
FROM MemCat Inner Join Member On Member.MemberID = MemCat.MemberID
WHERE Member.Active = 'Full'
ORDER By Member.Member_Name Asc

SELECT Member.MemberID, Member.Member_Name, Rooms.Pic1 AS RoomPic1, Rooms.Pic2 AS RoomPic2
FROM Rooms Inner Join Member on Member.MemberID = Rooms.MemberID
WHERE Member.Active = 'Full'
ORDER By Member.Member_Name Asc

Member Table Create***************************************
USE [db1]
GO

/****** Object: Table [dbo].[Member] Script Date: 03/28/2011 09:08:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Member](
[MemberID] [int] IDENTITY(1,1) NOT NULL,
[Logo] [varchar](50) NULL,
[Logo_Height] [varchar](50) NULL,
[Logo_Width] [varchar](50) NULL,
[Member_Name] [varchar](50) NULL,
[Address1] [varchar](50) NULL,
[Address2] [varchar](50) NULL,
[Address3] [varchar](50) NULL,
[PostCode] [varchar](50) NULL,
[Contact] [varchar](50) NULL,
[Tel] [varchar](50) NULL,
[Fax] [varchar](50) NULL,
[Web] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[StringURL] [varchar](100) NULL,
[Map] [varchar](50) NULL,
[Map_Height] [varchar](50) NULL,
[Map_Width] [varchar](50) NULL,
[Directions] [text] NULL,
[AvPrice] [varchar](50) NULL,
[LocationID] [int] NULL,
[Pic1] [varchar](50) NULL,
[Pic1_Height] [varchar](50) NULL,
[Pic1_Width] [varchar](50) NULL,
[Pic2] [varchar](50) NULL,
[Pic2_Height] [varchar](50) NULL,
[Pic2_Width] [varchar](50) NULL,
[Pic3] [varchar](50) NULL,
[Pic3_Height] [varchar](50) NULL,
[Pic3_Width] [varchar](50) NULL,
[Content1] [text] NULL,
[Content2] [text] NULL,
[Content3] [text] NULL,
[AccommodationTypeID] [int] NULL,
[AccommodationTypeID_1] [int] NULL,
[Active] [varchar](50) NULL,
[Paid] [varchar](50) NULL,
[Password] [varchar](50) NULL,
[Bookings] [char](1) NULL,
[VTour] [text] NULL,
[ADate] [smalldatetime] NULL,
[Awards] [text] NULL,
[Complete] [varchar](5) NOT NULL,
[ExtraRatesInfo] [text] NULL,
[Cell] [varchar](50) NULL,
[Email_GenContact] [varchar](50) NULL,
[AgentID] [int] NULL,
[Deposit] [smallint] NULL,
[SOComments] [text] NULL,
[VatNo] [varchar](20) NULL,
[Specials] [text] NULL,
[Specials_Intro] [text] NULL,
[Specials_SDate] [smalldatetime] NULL,
[Specials_EDate] [smalldatetime] NULL,
[Specials_Pic1] [varchar](50) NULL,
[Specials_Pic1_Height] [varchar](50) NULL,
[Specials_Pic1_Width] [varchar](50) NULL,
[Specials_Pic2] [varchar](50) NULL,
[Specials_Pic2_Height] [varchar](50) NULL,
[Specials_Pic2_Width] [varchar](50) NULL,
[Specials_SentDate] [smalldatetime] NULL,
[BranchName] [varchar](50) NULL,
[BranchCode] [varchar](50) NULL,
[BankName] [varchar](50) NULL,
[AccountHolder] [varchar](50) NULL,
[AccountNumber] [varchar](50) NULL,
[AccountType] [varchar](50) NULL,
[SMSNot] [varchar](10) NULL,
[CheckedBy] [varchar](100) NULL,
[CheckDate] [smalldatetime] NOT NULL,
[LastMemberUpdate] [smalldatetime] NOT NULL,
[AvailabilityAgentID] [int] NULL,
[extMemID] [varchar](20) NULL,
[MinNights] [smallint] NULL,
[MaxCapacity] [smallint] NULL,
[Enquirybutton] [varchar](1) NULL,
[TGC] [int] NULL,
[Lat] [float] NULL,
[Lng] [float] NULL,
[gZoom] [int] NULL,
[Specials_Discount] [int] NULL,
[VatExempt] [varchar](5) NULL,
[AllowPayment] [varchar](50) NULL,
[BestPrice] [varchar](5) NULL,
[OnlyCCBookings] [varchar](5) NULL,
[AVAgentActive] [varchar](5) NULL,
[SwiftCode] [varchar](50) NULL,
[AgentZARRates] [varchar](5) NULL,
[FreeAcc] [varchar](50) NULL,
CONSTRAINT [PK__Member__300424B4] PRIMARY KEY CLUSTERED
(
[MemberID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Member] ADD CONSTRAINT [DF_Member_ADate] DEFAULT (1 / 1 / 1900) FOR [ADate]
GO

ALTER TABLE [dbo].[Member] ADD CONSTRAINT [DF__Member__Complete__619B8048] DEFAULT ('No') FOR [Complete]
GO

ALTER TABLE [dbo].[Member] ADD CONSTRAINT [DF_Member_VatNo] DEFAULT ('') FOR [VatNo]
GO

ALTER TABLE [dbo].[Member] ADD CONSTRAINT [DF_Member_Specials_SDate] DEFAULT (1 / 1 / 1900) FOR [Specials_SDate]
GO

ALTER TABLE [dbo].[Member] ADD CONSTRAINT [DF_Member_Specials_EDate] DEFAULT (1 / 1 / 1900) FOR [Specials_EDate]
GO

ALTER TABLE [dbo].[Member] ADD CONSTRAINT [DF_Member_Specials_SentDate] DEFAULT (1 / 1 / 1900) FOR [Specials_SentDate]
GO

ALTER TABLE [dbo].[Member] ADD CONSTRAINT [DF__member__CheckDat__4EA8A765] DEFAULT (1 / 1 / 1900) FOR [CheckDate]
GO

ALTER TABLE [dbo].[Member] ADD CONSTRAINT [DF__member__LastMemb__2D12A970] DEFAULT (1 / 1 / 1900) FOR [LastMemberUpdate]
GO

ALTER TABLE [dbo].[Member] ADD CONSTRAINT [DF__Member__MinNight__0E240DFC] DEFAULT (1) FOR [MinNights]
GO

ALTER TABLE [dbo].[Member] ADD CONSTRAINT [DF_Member_MaxCapacity] DEFAULT (2) FOR [MaxCapacity]
GO

ALTER TABLE [dbo].[Member] ADD CONSTRAINT [DF__Member__Enquiryb__3414ACBA] DEFAULT ('Y') FOR [Enquirybutton]
GO

ALTER TABLE [dbo].[Member] ADD CONSTRAINT [DF_Member_TGC] DEFAULT (0) FOR [TGC]
GO

ALTER TABLE [dbo].[Member] ADD DEFAULT (0) FOR [Lat]
GO

ALTER TABLE [dbo].[Member] ADD DEFAULT (0) FOR [Lng]
GO

ALTER TABLE [dbo].[Member] ADD DEFAULT (0) FOR [gZoom]
GO

ALTER TABLE [dbo].[Member] ADD DEFAULT (0) FOR [Specials_Discount]
GO

ALTER TABLE [dbo].[Member] ADD DEFAULT ('No') FOR [VatExempt]
GO

ALTER TABLE [dbo].[Member] ADD DEFAULT ('Yes') FOR [AllowPayment]
GO

ALTER TABLE [dbo].[Member] ADD CONSTRAINT [DF_Member_BestPrice] DEFAULT ('No') FOR [BestPrice]
GO

ALTER TABLE [dbo].[Member] ADD DEFAULT ('No') FOR [OnlyCCBookings]
GO

ALTER TABLE [dbo].[Member] ADD DEFAULT ('Yes') FOR [AVAgentActive]
GO

ALTER TABLE [dbo].[Member] ADD DEFAULT ('') FOR [AgentZARRates]
GO
**************************************************
MemCat Table create***************************************
USE [db1]
GO

/****** Object: Table [dbo].[MemCat] Script Date: 03/28/2011 09:10:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MemCat](
[MemCatID] [int] IDENTITY(1,1) NOT NULL,
[CategoryID] [int] NOT NULL,
[MemberID] [int] NOT NULL,
[Descr] [text] NULL,
[Pic1] [varchar](50) NULL,
[Pic2] [varchar](50) NULL,
[Pic3] [varchar](50) NULL,
[Pic4] [varchar](50) NULL,
[Pic5] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[MemCatID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

***********************************************************
Rooms Table create ****************************************
USE [db1]
GO

/****** Object: Table [dbo].[Rooms] Script Date: 03/28/2011 09:11:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Rooms](
[RoomID] [int] IDENTITY(1,1) NOT NULL,
[MemberID] [int] NULL,
[RoomName] [varchar](50) NULL,
[Descr] [text] NULL,
[Pic1] [varchar](50) NULL,
[Width1] [varchar](50) NULL,
[Height1] [varchar](50) NULL,
[Pic2] [varchar](50) NULL,
[Width2] [varchar](50) NULL,
[Height2] [varchar](50) NULL,
[DPPSRate] [varchar](5) NULL,
[Sleeps] [varchar](100) NULL,
[Currency] [varchar](5) NULL,
PRIMARY KEY CLUSTERED
(
[RoomID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Rooms] ADD CONSTRAINT [DF__Rooms__DPPSRate__6754599E] DEFAULT ('Yes') FOR [DPPSRate]
GO

ALTER TABLE [dbo].[Rooms] ADD DEFAULT ('ZAR') FOR [Currency]
GO

****************************************************************

Greatness... Live It!!

lappin
Posting Yak Master

182 Posts

Posted - 2011-03-28 : 04:57:54

You can join like this:
SELECT Member.MemberID, Member.Member_Name, MemCat.Pic1 AS FacPic1, MemCat.Pic2 AS FacPic2, MemCat.Pic3 AS FacPic3, MemCat.Pic4 AS FacPic4,
MemCat.Pic5 AS FacPic5, Rooms.Pic1 AS RoomPic1, Rooms.Pic2 AS RoomPic2
,Member.Map, Member.Pic1 as MemPic1, Member.Pic2 as MemPic2, Member.Pic3as MemPic3, Member.Specials_Pic1, Member.Specials_Pic2, Member.Logo
FROM Member
Inner Join MemCat On Member.MemberID = MemCat.MemberID
Inner Join Rooms on Member.MemberID = Rooms.MemberID
WHERE Member.Active = 'Full'
ORDER By Member.Member_Name Asc

To get started building complicated joins: click new query and in blank pane right-click and choose Design Query in Editor, you can drag tables and joined fields.

BlackIce662
Starting Member

11 Posts

Posted - 2011-03-28 : 07:05:59

Thank you much appreciated

Greatness... Live It!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources