CODEWITHSUNDEEP
sql-servert-sqljoinconditional-statements
Adam
Adam

Reputation: 6122

Conditional join on tables

I want to create a conditional join on tables.

I already checked here. But I also read that an OUTER JOIN might not be the best way (performance wise) since a full table scan still takes place? And there also seems to be difference in opinion on the performance of using UNION.

What is the way I can conditionally join tables based on my requirements below? ps. If your solution requires it, I can implement this as a stored procedure too to create some more dynamic SQL.

For all rows where userfavorites.objecttype=100, based on the value of photos.objecttype I want to join another table. If the objecttype value is 1 I want to join [locations], if the value is 2 I want to join [persons].

As output I want the following columns:
photos.title,photos.locpath, (persons.title or locations.title), (persons.friendlyurl or locations.friendlyurl),userfavorites.objectid,userfavorites.objecttype

I now have this to get the rows for a single user:

SELECT * FROM userfavorites uf
INNER JOIN photos vp ON uf.objectid=vp.id
WHERE uf.objecttype=100 AND uf.userid='32DD30EB-1691-457B-9FF5-FC41D687E579'

DDL and insert statements:

CREATE TABLE [dbo].[photos](
    [id] [int] NOT NULL,
    [objectid] [int] NOT NULL,
    [locpath] [nvarchar](150) NOT NULL,
    [objecttype] [tinyint] NOT NULL,
    [title] [nvarchar](50) NULL) ON [PRIMARY]

CREATE TABLE [dbo].[userfavorites](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [userid] [uniqueidentifier] NOT NULL,
    [objectid] [int] NOT NULL,
    [objecttype] [tinyint] NOT NULL CONSTRAINT [DF_userfavorites_objecttype]  DEFAULT ((0)),
 CONSTRAINT [PK_userfavorites] 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]


CREATE TABLE [dbo].[persons](
    [id] [int] NOT NULL,
    [title] [nvarchar](80) NOT NULL,
    [friendlyurl] [nvarchar](80) NULL,
 CONSTRAINT [PK_persons_1] 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]

CREATE TABLE [dbo].[locations](
    [id] [int] NOT NULL,
    [title] [nvarchar](80) NOT NULL,
    [friendlyurl] [nvarchar](80) NULL,
 CONSTRAINT [PK_locations_1] 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]




USE [tt]
SET IDENTITY_INSERT [dbo].[userfavorites] ON 

INSERT [dbo].[userfavorites] ([userid], [objectid], [objecttype]) VALUES (N'32dd30eb-1691-457b-9ff5-fc41d687e579', 41029, 100)
INSERT [dbo].[userfavorites] ([userid], [objectid], [objecttype]) VALUES (N'32dd30eb-1691-457b-9ff5-fc41d687e579', 41030, 100)
INSERT [dbo].[userfavorites] ([userid], [objectid], [objecttype]) VALUES (N'32dd30eb-1691-457b-9ff5-fc41d687e579', 40880, 100)
INSERT [dbo].[userfavorites] ([userid], [objectid], [objecttype]) VALUES (N'32dd30eb-1691-457b-9ff5-fc41d687e579', 40885, 100)
INSERT [dbo].[userfavorites] ([userid], [objectid], [objecttype]) VALUES (N'32dd30eb-1691-457b-9ff5-fc41d687e579', 40882, 100)
INSERT [dbo].[userfavorites] ([userid], [objectid], [objecttype]) VALUES (N'32dd30eb-1691-457b-9ff5-fc41d687e579', 4067, 100)
INSERT [dbo].[userfavorites] ([userid], [objectid], [objecttype]) VALUES (N'BC5EB8A7-FEC2-4932-9C67-AE5A35C4012B', 4067, 100)
SET IDENTITY_INSERT [dbo].[userfavorites] OFF

INSERT [dbo].[photos] ([id], [objectid], [objecttype], [locpath], [title]) VALUES (41029, 1, 1, N'hiltonsky.jpg', N'Overview')
INSERT [dbo].[photos] ([id], [objectid], [objecttype], [locpath], [title]) VALUES (41030, 1, 1, N'pool.jpg', N'Swimming')
INSERT [dbo].[photos] ([id], [objectid], [objecttype], [locpath], [title]) VALUES (40880, 2, 1, N'entrance.jpg', N'Lobby Entrance')
INSERT [dbo].[photos] ([id], [objectid], [objecttype], [locpath], [title]) VALUES (40885, 2, 1, N'room.jpg', N'Room view')
INSERT [dbo].[photos] ([id], [objectid], [objecttype], [locpath], [title]) VALUES (40882, 1, 2, N'zuck.jpg', N'Mark')
INSERT [dbo].[photos] ([id], [objectid], [objecttype], [locpath], [title]) VALUES (4067, 2, 2, N'gates.jpg', N'Bill')
INSERT [dbo].[photos] ([id], [objectid], [objecttype], [locpath], [title]) VALUES (50673, 3, 2, N'musk.jpg', N'Elon')

INSERT [dbo].[locations] ([id], [title], [friendlyurl]) VALUES (1, N'Hilton Hotel', 'hilton-hotel')
INSERT [dbo].[locations] ([id], [title], [friendlyurl]) VALUES (2, N'Marriot Hotel', 'marriot-hotel')


INSERT [dbo].[persons] ([id], [title], [friendlyurl]) VALUES (1, N'Mark Zuckerberg', 'mark-zuckerberg')
INSERT [dbo].[persons] ([id], [title], [friendlyurl]) VALUES (2, N'Bill Gates', 'bill-gates')
INSERT [dbo].[persons] ([id], [title], [friendlyurl]) VALUES (3, N'Elon Musk', 'elon-musk')

So for userid 32dd30eb-1691-457b-9ff5-fc41d687e579, the outcome would be:

+----------------+---------------+-----------------+-----------------+----------+------------+
|   phototitle   |    locpath    |      title      |   friendlyurl   | objectid | objecttype |
+----------------+---------------+-----------------+-----------------+----------+------------+
| Overview       | hiltonsky.jpg | Hilton Hotel    | hilton-hotel    |        1 |          1 |
| Swimming       | pool.jpg      | Hilton Hotel    | hilton-hotel    |        1 |          1 |
| Lobby Entrance | entrance.jpg  | Marriot Hotel   | marriot-hotel   |        2 |          1 |
| Room view      | room.jpg      | Marriot Hotel   | marriot-hotel   |        2 |          1 |
| Mark           | zuck.jpg      | Mark Zuckerberg | mark-zuckerberg |        1 |          2 |
| Bill           | gates.jpg     | Bill Gates      | bill-gates      |        2 |          2 |
+----------------+---------------+-----------------+-----------------+----------+------------+

Upvotes: 2

Views: 104

Answers (2)

paparazzo
paparazzo

Reputation: 45096

with cte as 
( SELECT * FROM userfavorites uf
  INNER JOIN photos vp ON uf.objectid=vp.id
  WHERE uf.objecttype=100 AND uf.userid='32DD30EB-1691-457B-9FF5-FC41D687E579' )
SELECT * 
from cte 
JOIN locations on cte.objecttype = 1 and cte.objectid = locations.id
union all
SELECT * 
from cte 
JOIN persons   on cte.objecttype = 2 and cte.objectid = persons.id

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

There is nothing wrong with outer joins and it seems to be the straight forward approach to the problem. Whether or not a full table scan will be used is up to the DBMS; it will decide for the fastest data access whichever that may be.

SELECT 
  vp.phototitle, 
  vp.locpath, 
  coalesce(l.title, p.title) as title, 
  coalesce(l.friendlyurl, p.friendlyurl) as friendlyurl, 
  uf.objectid, 
  uf.objecttype
FROM userfavorites uf
INNER JOIN photos vp ON uf.objectid = vp.id
LEFT JOIN locations l on vp.objecttype= 1 and vp.objectid = l.id
LEFT JOIN persons p on vp.objecttype= 2 and vp.objectid = p.id
WHERE uf.objecttype = 100 AND uf.userid = '32DD30EB-1691-457B-9FF5-FC41D687E579';

However, if the table persons and locations are really very similar you might want to consider making them a single table with a flag saying whether a record is a person or a location. That would make things easier and speed up access.

Upvotes: 4

Related Questions