I have this SQL statement that does not turn out to be the way I want it to be.
I have 2 tables. Supplier and BrandNSupplier.
Supplier
----------------------------
SupplierCode | CompanyName
----------------------------
A123 | ABC PTE
----------------------------
B123 | BCE PTE
----------------------------
_______________________________
BrandNSupplier
------------------------------------
ID | SupplierList | BrandCode
------------------------------------
1 | A123, B123 | 1ALPA
------------------------------------
2 | A123 | 2AL
------------------------------------
3 | B123 | 3ALP
------------------------------------
My SQL Statement
_________________________________
SELECT CompanyName
FROM Supplier
WHERE SupplierCode IN
(SELECT SupplierList
FROM BrandNSupplier
WHERE BrandCode = '1ALPA')
___________________________________
Output (Result)
____________________________________
Nothing.
Result expected is: ABC PTE, BCE PTE.
Can any one help me with the SQL to get the expected results?
//////////////////////////////////////////////////////////
Below is another method using stored procedure. I hope to get the same results. Please help in the either method.
My stored procedure:
CREATE PROCEDURE GetSupplier
@ProdCode char(20)
AS
BEGIN
declare @supplist varchar(50)
Select @Supplist = SupplierList
FROM BrandNSupplier
WHERE BrandCode = @ProdCode
Select * From Supplier where
CharIndex(SupplierCode,@Supplist,0) <> 0
END
My CF codes:
<cfstoredproc procedure="GetSupplier" datasource="#application.dsn#" username="#application.dsnUsername#" password="#application.dsnPassword#" RETURNCODE="YES" debug="Yes">
<cfprocresult name="SupplierList">
<cfprocparam type="IN" value="1ALPA" DBVARNAME="@ProdCode"
cfsqltype="CF_SQL_VARCHAR">
</cfstoredproc>
<cfoutput query="SupplierList">
#CompanyName# <br>
</cfoutput>
I have 2 tables. Supplier and BrandNSupplier.
Supplier
----------------------------
SupplierCode | CompanyName
----------------------------
A123 | ABC PTE
----------------------------
B123 | BCE PTE
----------------------------
_______________________________
BrandNSupplier
------------------------------------
ID | SupplierList | BrandCode
------------------------------------
1 | A123, B123 | 1ALPA
------------------------------------
2 | A123 | 2AL
------------------------------------
3 | B123 | 3ALP
------------------------------------
My SQL Statement
_________________________________
SELECT CompanyName
FROM Supplier
WHERE SupplierCode IN
(SELECT SupplierList
FROM BrandNSupplier
WHERE BrandCode = '1ALPA')
___________________________________
Output (Result)
____________________________________
Nothing.
Result expected is: ABC PTE, BCE PTE.
Can any one help me with the SQL to get the expected results?
//////////////////////////////////////////////////////////
Below is another method using stored procedure. I hope to get the same results. Please help in the either method.
My stored procedure:
CREATE PROCEDURE GetSupplier
@ProdCode char(20)
AS
BEGIN
declare @supplist varchar(50)
Select @Supplist = SupplierList
FROM BrandNSupplier
WHERE BrandCode = @ProdCode
Select * From Supplier where
CharIndex(SupplierCode,@Supplist,0) <> 0
END
My CF codes:
<cfstoredproc procedure="GetSupplier" datasource="#application.dsn#" username="#application.dsnUsername#" password="#application.dsnPassword#" RETURNCODE="YES" debug="Yes">
<cfprocresult name="SupplierList">
<cfprocparam type="IN" value="1ALPA" DBVARNAME="@ProdCode"
cfsqltype="CF_SQL_VARCHAR">
</cfstoredproc>
<cfoutput query="SupplierList">
#CompanyName# <br>
</cfoutput>