A confusing join (for me) - 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

General SQL Server Forums

New to SQL Server Programming

A confusing join (for me)

Author

Topic

ompadme
Starting Member

5 Posts

Posted - 2006-07-10 : 11:16:17

I have 5 tables, the first 4 are responsible for getting wines with all their associated info:

Areas - Regions - Wineries - Wines

The last table is an artwork table, and both wineries and wines can have artwork associated with them. How do I integrate a join on the 'Artwork' table for both wineries and wines, taking into account that sometimes (for a single record) there is art for both winery and wine, sometimes for one, sometimes there is none?

I think I understand the 'ON' statement, for instance:

Wineries LEFT JOIN Artwork
ON Wineries.winery_id = Artwork.artwork_winery_id

'and'

Wines LEFT JOIN Artwork
ON Wines.wine_id = artwork_wine_id

But how do I integrate that into the following SELECT statement?

SELECT
area_id, area_name,
region_id, region_area_id, region_name,
winery_id, winery_region_id, winery_name,
wine_id, wine_winery_id, wine_name,
artwork_id, artwork_name, artwork_wine_id, artwork_winery_id

FROM Areas INNER JOIN
Regions ON Areas.area_id = Regions.region_area_id

INNER JOIN Wineries ON
Regions.region_id = Wineries.winery_region_id

INNER JOIN Wines ON
Wineries.winery_id = Wines.wine_winery_id

ORDER BY winery_name ASC;

- ompadme

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-10 : 11:27:30

It will be helpful if you can give us some CREATE TABLE and INSERT statements along with expected results so that we can know precisely what data you are dealing with.

- Jeff

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-07-10 : 11:29:02

Hi,

Welcome to SQL Team.

It would help if you can give some sample data and expected output

Thanks
Karunakaran

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-07-10 : 19:53:38

Looks like you'd want to do two outer joins on your artwork table and alias the columns (& tables) to reflect whether it is for the wine or winery.

ompadme
Starting Member

5 Posts

Posted - 2006-07-10 : 20:17:21

Here is the script for creating the 5 tables...

I'm not sure how to send some sample data for each table?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Wineries]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Wineries](
[winery_id] [int] IDENTITY(1,1) NOT NULL,
[winery_region_id] [int] NULL,
[winery_name] [nvarchar](255) NULL,
[winery_description] [nvarchar](max) NULL,
[winery_active] [bit] NOT NULL,
CONSTRAINT [PK_Wineries] PRIMARY KEY CLUSTERED
(
[winery_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Areas]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Areas](
[area_id] [int] IDENTITY(1,1) NOT NULL,
[area_name] [nvarchar](255) NULL,
[area_active] [bit] NOT NULL,
CONSTRAINT [PK_Areas] PRIMARY KEY CLUSTERED
(
[area_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Artwork]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Artwork](
[artwork_id] [int] IDENTITY(1,1) NOT NULL,
[artwork_filename] [nvarchar](50) NULL,
[artwork_winery_id] [int] NULL,
[artwork_wine_id] [int] NULL,
CONSTRAINT [PK_Artwork] PRIMARY KEY CLUSTERED
(
[artwork_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Regions]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Regions](
[region_id] [int] IDENTITY(1,1) NOT NULL,
[region_name] [nvarchar](255) NULL,
[region_area_id] [int] NULL,
[region_active] [bit] NOT NULL,
CONSTRAINT [PK_Regions] PRIMARY KEY CLUSTERED
(
[region_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Wines]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Wines](
[wine_id] [int] IDENTITY(1,1) NOT NULL,
[wine_winery_id] [int] NULL,
[wine_name] [nvarchar](255) NULL,
[wine_size] [int] NULL,
[wine_year] [int] NULL,
[wine_reg_bottle] [money] NULL,
[wine_reg_case] [money] NULL,
[wine_post_off_bottle] [money] NULL,
[wine_post_off_case] [money] NULL,
[wine_sold_out] [bit] NOT NULL,
[wine_active] [bit] NOT NULL,
[wine_soon] [bit] NOT NULL,
[wine_desc] [nvarchar](max) NULL,
[wine_rating] [nvarchar](max) NULL,
CONSTRAINT [PK_Wines] PRIMARY KEY CLUSTERED
(
[wine_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

- ompadme

ompadme
Starting Member

5 Posts

Posted - 2006-07-10 : 20:25:12

LoztInSpace,

Because I'm already joining areas, regions, wineries, and wines... I'm not sure where I would put the last left join. I can get everything I want, even the artwork for each wine, but I'm not sure where I work in the winery/artwork join...

Here's what I have so far...

FROM dbo.Areas INNER JOIN
dbo.Regions ON Areas.area_id = dbo.Regions.region_area_id

INNER JOIN dbo.Wineries ON
dbo.Regions.region_id = dbo.Wineries.winery_region_id

INNER JOIN dbo.Wines ON
dbo.Wineries.winery_id = dbo.Wines.wine_winery_id

LEFT JOIN dbo.Artwork ON
wines.wine_id = dbo.Artwork.artwork_wine_id

So I'll need to add something like the following:

dbo.Wineries AS W2 LEFT JOIN dbo.Artwork AS A2 ON
W2.winery_id = A2.artwork_winery_id

but where do I do this?

- ompadme

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-07-11 : 19:11:27

Not sure why you need W2. Just stick it on the end of the list with the others.

FROM dbo.Areas INNER JOIN
dbo.Regions ON Areas.area_id = dbo.Regions.region_area_id

INNER JOIN dbo.Wineries ON
dbo.Regions.region_id = dbo.Wineries.winery_region_id

INNER JOIN dbo.Wines ON
dbo.Wineries.winery_id = dbo.Wines.wine_winery_id

LEFT JOIN dbo.Artwork ON
wines.wine_id = dbo.Artwork.artwork_wine_id

LEFT JOIN dbo.Artwork AS A2 ON
dbo.Wineries.winery_id = A2.artwork_winery_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources