Solved

SQL server query

Posted on 2016-09-28
2
40 Views
Last Modified: 2016-09-28
I have the following query, it works fine but need to extend it a little

The query looks at strshort and where the clients share the same strshort description it returns the rows (essentially its matching client codes)

There are some exceptions in this case its "XC90 VOR PREMIUM" it will never get into the rows as that client used an abbreviation of VOR. to fix that I created a new table called TblShortDescLink
Ive manually made the links in this table by making strcommon
exampleI would like to include the strshort from the third table where a.strshort =thirdtable.strcommon

I am not sure how to do that.

use Dictionary
SELECT a.Strshort, a.ClientName, a.ClientCode
FROM TblWords A
JOIN TblWords B
            ON A.StrShort = B.StrShort
                  AND A.ClientName <> B.ClientName
where a.Strshort like'xc90%Ocean%' --and a.ClientName='Smmt'

group by a.Strshort, a.ClientName, a.ClientCode
order by a.StrShort, a.ClientName, a.ClientCode

Open in new window

query output
0
Comment
Question by:PeterBaileyUk
2 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41819299
Can you post the expected result?
0
 
LVL 32

Accepted Solution

by:
Stefan Hoffmann earned 500 total points
ID: 41819301
The question is: Why want you to include that third table?

E.g.
SELECT  A.Strshort ,
        A.ClientName ,
        A.ClientCode
FROM    TblWords A
WHERE   A.Strshort LIKE 'xc90%Ocean%'
        AND EXISTS ( SELECT *
                     FROM   TblWords B
                     WHERE  A.StrShort = B.StrShort
                            AND A.ClientName <> B.ClientName )
        AND EXISTS ( SELECT *
                     FROM   thirdtable C
                     WHERE  A.StrShort = C.strcommon )
ORDER BY A.StrShort ,
        A.ClientName ,
        A.ClientCode;

Open in new window

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now