• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

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
0
hojohappy
Asked:
hojohappy
1 Solution
 
Randy Knight, MCMCommented:
You shouldn't need a subquery for this.   Something like this should work.

SELECT a.ProductID, b.ProductName
FROM USMapTemplate a
	LEFT JOIN AtttributeMaster b ON a.ProductID = b.ProductID AND CHARINDEX(a.ProductID, b.ProductName) > 1
WHERE b.ProductID IS NOT NULL

Open in new window

0
 
chaauCommented:
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

0
 
PortletPaulCommented:
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
0
 
Pratik MakwanaCommented:
try this

SELECT ProductID, ProductName
FROM [USMapTemplate]
where ([USMapTemplate].[ProductID] in
(Select [Master].[ProductName] from [Attribute Master] where CHARINDEX([USMapTemplate].[ProductID], [Master].[ProuctName]) > 1))
0
 
awking00Commented:
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.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now