JOIN on MANY to one and ensuring the JOIN is correct

Question

JOIN on MANY to one and ensuring the JOIN is correct

pnr8uk

SSCommitted

Points: 1828
More actions
April 16, 2013 at 3:01 am

#146047

Hi All
I have a particularly problem with a join which is driving me insane, it involves basically joining two tables (a third comes later but does not matter in the case below) on the PRODUCTID.
Table ONE and Table TWO are linked only by the PRODUCTID - UNIQUEIDENTIFIER.
Table one holds the MarketingTitle to which Table Two needs to know about
I need to write a query to pull the MarketingTitle from Table One and the TITLE from Table two and the AENumber from table two to form the following and ensure the results has the right marketingTitle, Title and AENumber
MarketingTitle, Title, AENumer
Example One (TableOne - CatalogueItem)
select MarketingTitle,ProductId from CatalogueItem
where MarketingTitle LIKE 'BBC TV Shakespeare: Henry%'
group by ProductId,MarketingTitle
MarketingTitleProductId
BBC TV Shakespeare: Henry IV Part I69976081-320A-476B-994D-B11F9B3C33C7
BBC TV Shakespeare: Henry IV Part II69976081-320A-476B-994D-B11F9B3C33C7
BBC TV Shakespeare: Henry V69976081-320A-476B-994D-B11F9B3C33C7
BBC TV Shakespeare: Henry VI Part I69976081-320A-476B-994D-B11F9B3C33C7
BBC TV Shakespeare: Henry VI Part II69976081-320A-476B-994D-B11F9B3C33C7
Table two using the productid
select Title,AeNumber,ProductId from Sku
where ProductId = '69976081-320A-476B-994D-B11F9B3C33C7'
Title AENumber Productid
BBC TELEVISION SHAKESPEARE: HENRY VIII (USE AEDI027Y)AELI011K69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: HENRY IV PART I (USE AEDI024R)AEJT504N69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: THE TEMPESTAEDI033N69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: COMEDY OF ERRORSAEGT504X69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: MERRY WIVES OF WINDSOR (USE AEFT508N)AELT013Y69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: TITUS ANDRONICUS (USE AEHT517F)#1AELT012E69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: CORIOLANUSAEGT502J69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: HAMLET PRINCE OF DENMARK (USE AEDI023X)AEJT503T69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: HENRY VAEDI026E69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: MEASURE FOR MEASURE (2 PART VERSION)AEJI017E69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: ANTONY & CLEOPATRAAEMM004S69976081-320A-476B-994D-B11F9B3C33C7
ZZA - BBC TELEVISION SHAKESPEARE: SEASON 06 (USE INDIVIDUAL EPISODES FOR SALES)AEDA148R69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: MIDSUMMER NIGHT'S DREAMAEEL083F69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: KING LEARAEFT501E69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: TIMON OF ATHENSAEMM002E69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: A WINTER'S TALE (USE AEMM005L)AEDA143W69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: HENRY VI PART II (USE AEFT503S)AELT016F69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: ANTONY & CLEOPATRA (USE AEMM004S)AEDA144P69976081-320A-476B-994D-B11F9B3C33C7
Now if I join on the tables using the productid I get the below (I added the productid clause only for example as the table has over 100,000 productids)
select c.MarketingTitle,s.Title,s.aenumber from CatalogueItem c
JOIN Sku s ON s.ProductId = c.ProductId
WHERE s.ProductId ='69976081-320A-476B-994D-B11F9B3C33C7'
The results below show the MarketingTitle (from Catalogue), the Title (from SKU) and the AENumber from (Sku)
BBC TV Shakespeare: Richard IIIBBC TELEVISION SHAKESPEARE: HENRY VIII (USE AEDI027Y)AELI011K
BBC TV Shakespeare: Richard IIIBBC TELEVISION SHAKESPEARE: HENRY IV PART I (USE AEDI024R)AEJT504N
BBC TV Shakespeare: Richard IIIBBC TELEVISION SHAKESPEARE: THE TEMPESTAEDI033N
BBC TV Shakespeare: Richard IIIBBC TELEVISION SHAKESPEARE: COMEDY OF ERRORSAEGT504X
BBC TV Shakespeare: Richard IIIBBC TELEVISION SHAKESPEARE: MERRY WIVES OF WINDSOR (USE AEFT508N)AELT013Y
BBC TV Shakespeare: Richard IIIBBC TELEVISION SHAKESPEARE: TITUS ANDRONICUS (USE AEHT517F)#1AELT012E
BBC TV Shakespeare: Richard IIIBBC TELEVISION SHAKESPEARE: CORIOLANUSAEGT502J
BBC TV Shakespeare: Richard IIIBBC TELEVISION SHAKESPEARE: HAMLET PRINCE OF DENMARK (USE AEDI023X)AEJT503T
BBC TV Shakespeare: Richard IIIBBC TELEVISION SHAKESPEARE: HENRY VAEDI026E
Now what I do know is the Title (SKU) and AENumber (SKU) are correct just the join to the MarketingTitle is definitely wrong.
Any any help at all?
Many thanks
Paul

Viewing 8 posts - 1 through 7 (of 7 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Paul

Can you set up your sample data as ddl and dml please? There are instructions for this in the first article in my sig. Cheers.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pnr8uk SSCommitted Points: 1828 More actions · Answer 2

Will do Chris - sorry didn't realise there was such a great article as yours, I wondered how so many people got such neat data in the forum.

Will be back soon

Cheers

Paul

pnr8uk SSCommitted Points: 1828 More actions · Answer 3

CREATE TABLE [dbo].[CatalogueItem](

[MarketingTitle] [varchar](255) NULL,

[ProductID] [uniqueidentifier] NOT NULL

) ON [PRIMARY]

INSERT INTO CatalogueItem (MarketingTitle,ProductID) VALUES ('BBC TV Shakespeare: Henry VI Part III','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO CatalogueItem (MarketingTitle,ProductID) VALUES ('BBC TV Shakespeare: Henry IV Part II','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO CatalogueItem (MarketingTitle,ProductID) VALUES ('BBC TV Shakespeare: Henry VIII','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO CatalogueItem (MarketingTitle,ProductID) VALUES ('BBC TV Shakespeare: Henry V','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO CatalogueItem (MarketingTitle,ProductID) VALUES ('BBC TV Shakespeare: Henry VI Part I','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO CatalogueItem (MarketingTitle,ProductID) VALUES ('BBC TV Shakespeare: Henry IV Part I','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO CatalogueItem (MarketingTitle,ProductID) VALUES ('BBC TV Shakespeare: Henry VI Part II','69976081-320A-476B-994D-B11F9B3C33C7')

CREATE TABLE [dbo].[Sku]

(

[ProductId] [uniqueidentifier] NULL,

[Title] [varchar](255) NOT NULL,

[AeNumber] [char](8) NOT NULL

)

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HENRY VIII (USE AEDI027Y)','AELI011K','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HENRY IV PART I (USE AEDI024R)','AEJT504N','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: THE TEMPEST','AEDI033N','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: COMEDY OF ERRORS','AEGT504X','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: MERRY WIVES OF WINDSOR (USE AEFT508N)','AELT013Y','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TITUS ANDRONICUS (USE AEHT517F)#1','AELT012E','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: CORIOLANUS','AEGT502J','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HAMLET PRINCE OF DENMARK (USE AEDI023X)','AEJT503T','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HENRY V','AEDI026E','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: MEASURE FOR MEASURE (2 PART VERSION)','AEJI017E','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: ANTONY & CLEOPATRA','AEMM004S','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('ZZA - BBC TELEVISION SHAKESPEARE: SEASON 06 (USE INDIVIDUAL EPISODES FOR SALES)','AEDA148R','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: MIDSUMMER NIGHT''S DREAM','AEEL083F','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: KING LEAR','AEFT501E','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TIMON OF ATHENS','AEMM002E','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: A WINTER''S TALE (USE AEMM005L)','AEDA143W','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HENRY VI PART II (USE AEFT503S)','AELT016F','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: ANTONY & CLEOPATRA (USE AEMM004S)','AEDA144P','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: ALL''S WELL THAT ENDS WELL','AEDI036W','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TROILUS & CRESSIDA (USE AEEL074J)','AELL027S','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TRAGEDY OF RICHARD III (USE AEFT509H)','AEKL018K','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: A WINTER''S TALE (2 PART VERSION)','AELA112W','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('ZZA - BBC TELEVISION SHAKESPEARE: SEASON 05 (USE INDIVIDUAL EPISODES FOR SALES)','AEDA146D','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: AS YOU LIKE IT','AEDI022D','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: MACBETH','AEFT507T','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HENRY IV PART II','AEDI025K','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TWELFTH NIGHT (USE AEDI034H)','AEJT505H','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: MERRY WIVES OF WINDSOR','AEFT508N','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: PERICLES','AEGT510L','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TWELFTH NIGHT','AEDI034H','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HENRY VI PART III (USE AEFT504L)','AELT015L','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: RICHARD II','AEDI030F','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: ROMEO & JULIET','AEDI031A','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TIMON OF ATHENS (USE AEMM002E)#2','AELA127H','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: KING LEAR (2 PART VERSION)','AEJT046J','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: A WINTER''S TALE (2 PART VERSION) (USE AELA112W)','AEMM007A','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: MACBETH (USE AEFT507T)','AEJT501F','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: MUCH ADO ABOUT NOTHING','AEHT507P','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HENRY VI PART III','AEFT504L','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: ALL''S WELL THAT ENDS WELL (USE AEDI036W)','AELI008D','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TAMING OF THE SHREW (2 PART VERSION)','AELI004B','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TROILUS & CRESSIDA','AEEL074J','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TWO GENTLEMEN OF VERONA','AEGT505R','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TITUS ANDRONICUS (USE AEHT517F)#2','AEJT003L','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HAMLET PRINCE OF DENMARK','AEDI023X','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HENRY IV PART I','AEDI024R','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: CYMBELINE','AEFT511W','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HENRY VI PART II','AEFT503S','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: LIFE & DEATH OF KING JOHN','AEHT502T','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TAMING OF THE SHREW','AEDI032T','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: AS YOU LIKE IT (2 PART VERSION)','AELI003H','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: ANTONY & CLEOPATRA (2 PART VERSION) (USE AELA111B)','AEMM003Y','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: THE TEMPEST (2 PART VERSION)','AELI002N','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HENRY VI PART I (USE AEFT502Y)','AELT017A','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('ZZA - BBC TELEVISION SHAKESPEARE: SEASON 04 (USE INDIVIDUAL EPISODES FOR SALES)','AEDA147X','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: OTHELLO (2 PART VERSION)','AEJL087T','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TITUS ANDRONICUS','AEHT517F','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: COMEDY OF ERRORS (2 PART VERSION)','AELT002N','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: ROMEO & JULIET (2 PART VERSION)','AEJI018Y','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HENRY V (2 PART VERSION)','AELI007J','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: PERICLES (USE AEGT510L)','AELT020H','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: MERCHANT OF VENICE','AEDI035B','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: MERCHANT OF VENICE (USE AEDI035B)','AEKL017R','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: ANTONY & CLEOPATRA (2 PART VERSION)','AELA111B','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TRAGEDY OF RICHARD III','AEFT509H','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE (COMPILATION)','AEFP019K','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: JULIUS CAESAR (USE AEDI028S)','AEJT502A','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: LOVE''S LABOURS LOST (USE AEHT512K)','AELT011K','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: CYMBELINE (USE AEFT511W)','AELT021B','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: LIFE & DEATH OF KING JOHN (USE AEHT502T)','AELT014S','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: LOVE''S LABOUR''S LOST','AEHT512K','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HENRY VIII','AEDI027Y','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: OTHELLO','AEEL019H','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HENRY VI PART I','AEFT502Y','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: MUCH ADO ABOUT NOTHING (2 PART VERSION)','AELT001T','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: MIDSUMMER NIGHT''S DREAM (USE AEEL083F)','AEJT506B','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TWO GENTLEMEN OF VERONA (2 PART VERSION)','AELT008D','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: MEASURE FOR MEASURE','AEDI029L','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: JULIUS CAESAR','AEDI028S','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: A WINTER''S TALE','AEMM005L','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: RICHARD II (USE AEDI030F)','AELI009X','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TIMON OF ATHENS (USE AEMM002E)#3','AEMM006F','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: CORIOLANUS (USE AEGT502J)','AELT009X','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: HENRY IV PART II (USE AEDI025K)','AELI001T','69976081-320A-476B-994D-B11F9B3C33C7')

INSERT INTO SKU (Title,AENumber,ProductID)VALUES ('BBC TELEVISION SHAKESPEARE: TIMON OF ATHENS (USE AEMM002E)#1','AEDA145J','69976081-320A-476B-994D-B11F9B3C33C7')

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

pnr8uk (4/16/2013)
Will do Chris - sorry didn't realise there was such a great article as yours, I wondered how so many people got such neat data in the forum.
Will be back soon
Cheers
Paul

Ah, that would be Jeff's article 😉

Thanks for posting up the scripts.

I reckon your productid in the catalogue table is messed up. Have a look at the output of this - if it's anything like what you are looking for, then it would add weight to the theory:

SELECT y.MarketingTitle, b.Title, s.*

FROM CatalogueItem c

CROSS APPLY (SELECT pos = CHARINDEX(':',c.MarketingTitle,0)) x

CROSS APPLY (SELECT MarketingTitle = SUBSTRING(c.MarketingTitle,x.pos+2,LEN(c.MarketingTitle))) y

LEFT JOIN SKU s ON s.ProductId = c.ProductID

CROSS APPLY (SELECT pos = CHARINDEX(':',s.Title,0)) a

CROSS APPLY (SELECT Title = SUBSTRING(s.Title,a.pos+2,LEN(s.Title))) b

WHERE b.Title + ' ' LIKE y.MarketingTitle + ' %'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pnr8uk SSCommitted Points: 1828 More actions · Answer 5

Well it works beautifully so does that mean the productid on the Catalogue table is messed up?

The script looks like a thing of beauty and works nicely....

Intrigued now though as to what you mean the productid may be messed up?

Cheers

Paul

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

pnr8uk (4/16/2013)
Well it works beautifully so does that mean the productid on the Catalogue table is messed up?
The script looks like a thing of beauty and works nicely....
Intrigued now though as to what you mean the productid may be messed up?
Cheers
Paul

It's only a guess Paul, but the productid in the catalogue table seems to be common to several titles when I suspect it shouldn't. You need to check the ERD or ask your DBA.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pnr8uk SSCommitted Points: 1828 More actions · Answer 7

Hi Chris many thanks for this and you are absolutely right about the productid. I can't sady do anything with this however your query does look good.

As I only gave a sample of the data how could this be used when there is no : in the title name?

thanks for all your help

Paul