Table_E

Table_Z

SELECT EE.name
      ,EE.Product
      ,Left(ZZ.Product_ID,4,3) 

  FROM Table_e EE
  Left join Table_z ZZ
  On Case When EE.Product = Tablet then 'TA'   ' '   EE.Client_ID
          when EE.Product = Phone then 'PH'   ' '   EE.Client_ID
          when EE.Product = Laptop Then 'LT'   ' '   EE.Client_ID
          Else 'N/A' ON ZZ.Client_ID 

To join these two tables I know it is on Client_ID. However, the Client_ID on Table_Z has a prefix plus a 'space' based on the product which you can see in Table_E. Also, I want to retrieve in my 'SELECT' statement the Product_ID numbers only from table Z. How would I accomplish this? What would the correct code look like?

CodePudding user response：

As mentioned by Panagiotis Kanavos really the prefix shouldn't be there at all. You are using the prefix to denote a second piece of information, yet that information is already in the table Table_E; don't do this. Really you should completely removing the prefixes, changing the data type and creating a FOREIGN KEY constraint on Client_ID in Table_Z. Fix your design, fix the problem

If, for some reason, you can't do this then use a computed column. In ths following I assume that ClientID in Table_E is an int and is either a PRIMARY KEY or used as a UNIQUE INDEX.

Firstly, create the new column:

ALTER TABLE dbo.Table_Z ADD Client_ID_np AS TRY_CONVERT(int,STUFF(Client_ID,1,CHARINDEX(' ',Client_ID),'')) PERSISTED;

This new column removes the prefix and attempts to convert the value to an int (if it can't be converted NULL is returned).

Then let's create the FOREIGN KEY:

ALTER TABLE dbo.Table_Z ADD CONSTRAINT FK_TableZ_TableE_ClientID FOREIGN KEY (Client_ID_np) REFERENCES Table_E (ClientID);

This ensures there are no invalid values. If this fails then either one of my assumptions are wrong, or you have bad data. Address that here before progressing.

Then (after addressing data integrity issues as needed) you can do a simple join:

SELECT {Your Columns}
FROM dbo.Table_E E
     JOIN dbo.Table_Z Z ON E.Client_ID = Z.Client_ID_np;

CodePudding user response：

I think what you're trying to do would be something like this. I'm not sure what you're trying to accomplish with the case expression, but if I understand that you're simply trying to join the two tables without worrying about the prefix on table_z, then I think you should try this.

SELECT e.client_id, e.name, e.product, z.product_id
FROM table_e as e
JOIN table_z as z ON
    e.client_id = SUBSTRING(z.client_id, CHARINDEX(' ', z.client_id), LEN(z.client_id))

I've created a sqlfiddle for you to validate this answer. http://sqlfiddle.com/#!18/5453ad/2/0

I understand now that the above didn't work for you so here's a new recommendation.

I'd create a join table that describes products and their associated codes and call it something like product_codes. It would look like so.

CREATE TABLE product_codes(
    product varchar(20),
    product_code char(2)
)

I'd also update your table_z to separate the client_id and product_code into separate fields. That way you could write your query like so.

SELECT e.client_id, e.name, e.product, z.product_id
FROM table_e AS e
    JOIN product_codes AS pc ON e.product = pc.product
    JOIN table_z AS z 
        ON e.client_id = z.client_id 
        AND pc.product_code = z.product_code

However, I'm going to assume that you can't change the structure of the database in any way. If that's the case then you could use a CTE to create a product_codes table for your query.

WITH product_codes(product, product_code) AS (
  SELECT 'Laptop' as product, 'LT' as product_code
  UNION
  SELECT 'Phone' as product, 'PH' as product_code
  UNION
  SELECT 'Tablet' as produt, 'TA' as product_code
)
SELECT e.client_id, e.name, e.product, z.product_id
FROM table_e as e
JOIN product_codes pc 
  ON e.product = pc.product
JOIN table_z as z 
  ON pc.product_code   ' '   CONVERT(CHAR(7), e.client_id) = z.client_id

I've updated the sqlfiddle for you to try this out.

http://sqlfiddle.com/#!18/5453ad/11

CodePudding user response：

As others have noted, the best solution would be to change your design. If that is not possible (not your design etc.), I suggest using a CTE to concatenate the desired fields, then joining with that. If the Table_E Client_ID field is INT, you'll need to CAST or CONVERT that in the CASE expression in the CTE.

DECLARE @Table_E TABLE (Client_ID VARCHAR(20), C_Name VARCHAR(50), Product VARCHAR(20))
INSERT INTO @Table_E
VALUES
(2217, 'John Smith', 'Tablet'),
(3109, 'Rebecca Anderson', 'Phone'),
(1569, 'Rajat Singh', 'Laptop')

DECLARE @Table_Z TABLE (Client_ID VARCHAR(20), City VARCHAR(20), Product_ID VARCHAR(20))
INSERT INTO @Table_Z
VALUES
('TA 2217', 'Boston', 'xcg153hkk'),
('PH 3109', 'Houston', 'dfr983uht'),
('LT 1569', 'Seattle', 'tty455phq');

WITH Client_IDZ AS
(
    SELECT Client_ID,
        C_Name,
        Product,
        CASE WHEN EE.Product = 'Tablet' THEN 'TA'  ' '  EE.Client_ID
             WHEN EE.Product = 'Phone' THEN 'PH'  ' '  EE.Client_ID
             WHEN EE.Product = 'Laptop' THEN 'LT'  ' '  EE.Client_ID
             ELSE NULL END AS Client_IDZ
    FROM @Table_E EE
)



SELECT EE.C_Name
      ,EE.Product
      ,ZZ.Product_ID
FROM Client_IDZ EE
  LEFT JOIN @Table_z ZZ ON EE.Client_IDZ = ZZ.Client_ID