Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

JOIN on MANY to one and ensuring the JOIN is correct Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 3:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 1:50 PM
Points: 123, Visits: 322
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


MarketingTitle ProductId
BBC TV Shakespeare: Henry IV Part I 69976081-320A-476B-994D-B11F9B3C33C7
BBC TV Shakespeare: Henry IV Part II 69976081-320A-476B-994D-B11F9B3C33C7
BBC TV Shakespeare: Henry V 69976081-320A-476B-994D-B11F9B3C33C7
BBC TV Shakespeare: Henry VI Part I 69976081-320A-476B-994D-B11F9B3C33C7
BBC TV Shakespeare: Henry VI Part II 69976081-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) AELI011K 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: HENRY IV PART I (USE AEDI024R) AEJT504N 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: THE TEMPEST AEDI033N 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: COMEDY OF ERRORS AEGT504X 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: MERRY WIVES OF WINDSOR (USE AEFT508N) AELT013Y 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: TITUS ANDRONICUS (USE AEHT517F)#1 AELT012E 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: CORIOLANUS AEGT502J 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: HAMLET PRINCE OF DENMARK (USE AEDI023X) AEJT503T 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: HENRY V AEDI026E 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: MEASURE FOR MEASURE (2 PART VERSION) AEJI017E 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: ANTONY & CLEOPATRA AEMM004S 69976081-320A-476B-994D-B11F9B3C33C7
ZZA - BBC TELEVISION SHAKESPEARE: SEASON 06 (USE INDIVIDUAL EPISODES FOR SALES) AEDA148R 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: MIDSUMMER NIGHT'S DREAM AEEL083F 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: KING LEAR AEFT501E 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: TIMON OF ATHENS AEMM002E 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: A WINTER'S TALE (USE AEMM005L) AEDA143W 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: HENRY VI PART II (USE AEFT503S) AELT016F 69976081-320A-476B-994D-B11F9B3C33C7
BBC TELEVISION SHAKESPEARE: ANTONY & CLEOPATRA (USE AEMM004S) AEDA144P 69976081-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 III BBC TELEVISION SHAKESPEARE: HENRY VIII (USE AEDI027Y) AELI011K
BBC TV Shakespeare: Richard III BBC TELEVISION SHAKESPEARE: HENRY IV PART I (USE AEDI024R) AEJT504N
BBC TV Shakespeare: Richard III BBC TELEVISION SHAKESPEARE: THE TEMPEST AEDI033N
BBC TV Shakespeare: Richard III BBC TELEVISION SHAKESPEARE: COMEDY OF ERRORS AEGT504X
BBC TV Shakespeare: Richard III BBC TELEVISION SHAKESPEARE: MERRY WIVES OF WINDSOR (USE AEFT508N) AELT013Y
BBC TV Shakespeare: Richard III BBC TELEVISION SHAKESPEARE: TITUS ANDRONICUS (USE AEHT517F)#1 AELT012E
BBC TV Shakespeare: Richard III BBC TELEVISION SHAKESPEARE: CORIOLANUS AEGT502J
BBC TV Shakespeare: Richard III BBC TELEVISION SHAKESPEARE: HAMLET PRINCE OF DENMARK (USE AEDI023X) AEJT503T
BBC TV Shakespeare: Richard III BBC TELEVISION SHAKESPEARE: HENRY V AEDI026E

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


Post #1442652
Posted Tuesday, April 16, 2013 3:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,175, Visits: 13,621
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1442654
Posted Tuesday, April 16, 2013 3:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 1:50 PM
Points: 123, Visits: 322
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
Post #1442656
Posted Tuesday, April 16, 2013 3:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 1:50 PM
Points: 123, Visits: 322

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')



Post #1442665
Posted Tuesday, April 16, 2013 4:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,175, Visits: 13,621
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1442672
Posted Tuesday, April 16, 2013 4:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 1:50 PM
Points: 123, Visits: 322
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
Post #1442675
Posted Tuesday, April 16, 2013 4:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,175, Visits: 13,621
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1442679
Posted Tuesday, April 16, 2013 4:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 1:50 PM
Points: 123, Visits: 322
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
Post #1442683
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse