asked on

SQL Nested Select

I need to display a column from a nested selected query.

SELECT ProductID
FROM [USMapTemplate]
where ([USMapTemplate].[ProductID] EXISTS
(Select [Master].[ProductName] from [Attribute Master] where CHARINDEX([USMapTemplate].[ProductID], [Master].[ProuctName]) > 1)

My output needs to include the Product Name column from the nested table [Master] table.

ProductID Product Name
-------------- ---------------------
ddddddd dhdhhdhdhh ddddddddd

ASKER CERTIFIED SOLUTION

Randy Knight, MCM

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chaau

You can convert your query to INNER JOIN:

SELECT [USMapTemplate].ProductID, [Attribute Master].[ProductName] 
FROM [USMapTemplate] INNER JOIN [Attribute Master] 
     ON CHARINDEX([USMapTemplate].[ProductID], [Attribute Master].[ProductName]) > 1

Open in new window

PortletPaul

This probably will be slow, but I think this will work.

SELECT
      mt.ProductID
    , am.ProductName
FROM USMapTemplate mt
      INNER JOIN [Attribute Master] am
                  ON CHARINDEX(mt.ProductID, am.ProductName) > 1
;

Open in new window

By the way, the query code you posted in the question has syntax errors it should look like this:

SELECT
      mt.ProductID
FROM USMapTemplate mt
WHERE EXISTS (
            SELECT
                  NULL
            FROM [Attribute Master] AS am
            WHERE CHARINDEX(mt.ProductID, am.ProductName) > 1
      )
;

Open in new window

Notes:
[Attribute Master] I don't know if this is a table name (boy I hope not) or if the alias was intended to be "Master"
I wouldn't recommend using "Master" as an alias (could be confused with the master db)

ProuctName I assume is ProductName

Pratik Makwana

try this

SELECT ProductID, ProductName
FROM [USMapTemplate]
where ([USMapTemplate].[ProductID] in
(Select [Master].[ProductName] from [Attribute Master] where CHARINDEX([USMapTemplate].[ProductID], [Master].[ProuctName]) > 1))

awking00

with cte as
(select productname
from [Attribute Master])
select mt.productid, cte.productname
from usmaptemplate, cte
where charindex(mt.productid,cte.productname) > 0

Note - where charindex > 0, using where charindex > 1 would not return a record where the productid was 'eeeeee' and the productname was 'eeeeeeee efefefefef', for example.