I have “values” that have text appended to the numeric value. OR12345 for instance.

We have multiple tables and in the primary table we have values such as OR12345, OR12346, OR12347 etc

Secondary tables are just the INTEGER part of the ID such as 12345, 12346, and 12347

I've tried a basic LEFT JOIN but it only picks up one tables values. I've considered a LIKE wildcard value but that did not seem viable or at least I did not know the syntax to achieve that method and create a dynamic selection variable.

My basic query is such:

SELECT 
    J.CurJLId, SC.OrderId, SC.ShippingMethodId, SC.SendProgressEmails 
FROM 
    [PD_Jobs] J
LEFT JOIN 
    [SM_ShopCart] SC ON SC.OrderId = J.InvoiceId
WHERE 
    J.CurJLId > 'JL30' 
    AND SC.SendProgressEmails = 1

Table [SM_ShopCart]:

Table [PD_Jobs]:

Should I be creating an embedded SELECT and then screen that to remove the 'OR'?

This is a very complicated query for my skill level. If I can get some help in the logic and proper structure it would be appreciated.

CodePudding user response：

When joining tables the data types of the joined columns should match.

Joining a varchar column with an int column will require one type to be cast to the other, which will mean an index seek will not be possible as this is an unsargable operation; you are forcing SQL Server to evaluate every row before it knows whether the casted value can be joined.

To join your tables you can do either of the following:

SELECT J.CurJLId, SC.OrderId, SC.SendProgressEmails 
FROM  PD_Jobs J
LEFT JOIN SM_ShopCart SC ON Stuff(SC.OrderId,1,2,'') = J.InvoiceId
WHERE J.CurJLId > 'JL30' and SC.SendProgressEmails = 1

SELECT J.CurJLId, SC.OrderId, SC.SendProgressEmails 
FROM  PD_Jobs J
LEFT JOIN SM_ShopCart SC ON SC.OrderId = Concat('OR',J.InvoiceId)
WHERE J.CurJLId > 'JL30' and SC.SendProgressEmails = 1

However, if performance matters at all you would be better off adding a persisted indexed computed column to the SM_ShopCart table to permanently fix the data type as int which will allow SQL Server to efficiently join your tables.

alter table SM_ShopCart add InvoiceId as Cast(Stuff(OrderId,1,2,'') as int) persisted
create index IX_InvoiceId on SM_ShopCart(InvoiceId) /* include (covering columns)*/

Then your query can just join the two tables with no data type conversion

SELECT J.CurJLId, SC.OrderId, SC.SendProgressEmails 
FROM  PD_Jobs J
LEFT JOIN SM_ShopCart SC ON SC.InvoiceId = J.InvoiceId
WHERE J.CurJLId > 'JL30' and SC.SendProgressEmails = 1

Note that in addition, your criteria J.CurJLId > 'JL30' is not going to work as you expect:

if 'JL30' > 'JL100' print 'oops'

CodePudding user response：

See if something similar to this works?

SELECT J.CurJLId, SC.OrderId, SC.ShippingMethodId, SC.SendProgressEmails 
FROM [PD_Jobs] J
LEFT JOIN [SM_ShopCart] SC  ON SC.OrderId = 'OR'   CAST(J.InvoiceId as VARCHAR)  
WHERE J.CurJLId > 'JL30' AND SC.SendProgressEmails = 1