Correct SQL syntax selecting from two tables using values from first table

Question

I am trying to perform a SQL query in SQL Server.

1. What is correct syntax for pulling from data from two tables using a value from the first table to query the second table?

2. Can I extract a string of text from the first table column and use to query second table. If so, is the syntax I have correct?

Any advice is appreciated. Thanks.

I have posted the code and my notes below. I replaced actual table names with tableA and tableB to make it easier to read/understand.

/* Select what we need from tableA */

SELECT 
    sku, sku_desc, whse_code, create_stamp, stg_pm_f_id as a 
FROM  
    tableA 
WHERE 
    DATEDIFF(day,create_stamp,GETDATE()) < 365 
    AND stg_status = 5 

Select from tableB using key value extracted from text string within stg_pm_f_id from tableA.

The owner_muid column contains stg_pm_f_id as

wms|stg_pm_f|WHSE1|885563

with elements separated by vertical bar, the last element is what we need to query tableB, i.e. 885563.

Example:

select 
    column_name, group_by_key 
from 
    tableB 
where
    column_name is not null
    and owner_muid like '%885563%' 
order by 
    create_stamp desc

SELECT  
    column_name, group_by_key 
FROM
    tableB 
WHERE 
    column_name IS NOT NULL 
    AND owner_muid LIKE RIGHT(A.stg_pm_f, CHARINDEX('|', (REVERSE(A.stg_pm_f_id))) - 1)  /*locate the last vertical bar and grab everything to the right*/
ORDER BY 
    create_stamp DESC

Display the final results as one row:

a.sku, a.sku_desc, a.whse_code, a.create_stamp, b.column_name, b.group_by_key

I am getting various errors depending on how I configure, but basically I don't know enough SQL yet to execute the command as desired and hoping someone can educate me, thanks.

Answer 1

In general, what you're looking to do is called a "JOIN" and generally looks like this:

select «column list»
from tableA as a
join tableB as b
   on «join criteria predicates»
...

Let's apply that to your example step by step.

/* Select what we need from tableA */

SELECT 
    sku, sku_desc, whse_code, create_stamp, stg_pm_f_id as a 
FROM  
    tableA 
WHERE 
    DATEDIFF(day,create_stamp,GETDATE()) < 365 
    AND stg_status = 5 

Before we move onto joining with another table, I want to note that as written, the DATEDIFF predicate is going to be bad for performance. Why? You're asking the database to do that calculation for every row. But looking at it, a simple rewrite will help. You're asking for rows within the last year, so it can be re-written as create_stamp > DATEDIFF(day, -365, GETDATE()). The database is smart enough to calculate that value once at the start of the query and then use it to compare rows to as the query executes.

Now onto your join:

SELECT 
    a.sku, a.sku_desc, a.whse_code, a.create_stamp, b.column_name, b.group_by_key 
FROM  
    tableA as a
JOIN
    tableB as b
ON
    /*locate the last vertical bar and grab everything to the right*/
    b.owner_muid LIKE RIGHT(a.stg_pm_f, CHARINDEX('|', (REVERSE(a.stg_pm_f_id))) - 1)
WHERE 
    a.create_stamp > DATEDIFF(day, -365, GETDATE())
    AND a.stg_status = 5
    AND b.column_name IS NOT NULL
ORDER BY 
    b.create_stamp DESC;

Breaking this down, we're saying "get rows from tableB where column_name IS NOT NULL (from the WHERE clause) and owner_muid matches the last element in the pipe-separated column stg_pm_f_id from tableA".

---

Another comment here - the data as it exists violates first normal form. That is, having an element of your data be pipe-separated elements which are themselves data elements makes this query a) difficult to write and b) unlikely to perform well. If it's within your control, I'd recommend redesigning both tables to pull each of those elements in the pipe-separated list out and promoting them to first-class columns within their respective tables.