Advanced SQL Query Problem ...

[Chen Fock Siong]

Dear Expert,

I have 2 tables as the following,

Table : Product
Column : ID, Code, Name

Table : Trx
Column : Trx_ID, Trx_Date, Product_ID
How can perform the following query in MSSQL or is there any other way
to perform the same query ??? For you info I can do this in Sybase and
it's work fine. Thank you.

================================================== ==========
Select Trx.Product_ID,
(Select Produc.Code
From Product
Where Product.ID = Trx.Product_ID) as Product_Code
From Trx
Where Product_Code = '0001'
================================================== ==========

Regards,
Chen Fock Siong
2004-05-27

[Hugo Kornelis]

On 26 May 2004 21:00:56 -0700, Chen Fock Siong wrote:

Dear Expert,

I have 2 tables as the following,

Table : Product
Column : ID, Code, Name

Table : Trx
Column : Trx_ID, Trx_Date, Product_ID
How can perform the following query in MSSQL or is there any other way
to perform the same query ??? For you info I can do this in Sybase and
it's work fine. Thank you.

================================================= ===========
Select Trx.Product_ID,
(Select Produc.Code
From Product
Where Product.ID = Trx.Product_ID) as Product_Code
From Trx
Where Product_Code = '0001'
================================================= ===========

Regards,
Chen Fock Siong
2004-05-27

Hi Chen,

SELECT Trx.Product_ID, Product.Code
FROM Trx
INNER JOIN Product
ON Product.ID = Trx.Product_ID
WHERE Product_Code = '0001'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[WangKhar]

I'm not sure, I don't know the ins and outs of the syntax you are
using or what you want but:

this may be helpful?

select t.Product_ID, p.Code as Product_Code
from Trx t
join Product p
on p.ID = t.Product_ID
Where p.Code = '0001'

fs****@biztrak.biz (Chen Fock Siong) wrote in message news:<f6**************************@posting.google. com>...

Dear Expert,

I have 2 tables as the following,

Table : Product
Column : ID, Code, Name

Table : Trx
Column : Trx_ID, Trx_Date, Product_ID
How can perform the following query in MSSQL or is there any other way
to perform the same query ??? For you info I can do this in Sybase and
it's work fine. Thank you.

================================================== ==========
Select Trx.Product_ID,
(Select Produc.Code
From Product
Where Product.ID = Trx.Product_ID) as Product_Code
From Trx
Where Product_Code = '0001'
================================================== ==========

Regards,
Chen Fock Siong
2004-05-27

[David Portas]

In Standard SQL a WHERE clause may only refer to the column names in the
base table or a derived table. This is the rule that MS SQLServer uses. If
you nest your query inside a derived table then it will work but, as others
have suggested, there are probably more efficient ways of getting the result
you want.

SELECT product_id, product_code
FROM
(SELECT Trx.product_id,
(SELECT Product.Code
FROM Product
WHERE Product.ID = Trx.Product_ID) AS product_code
FROM Trx) AS T
WHERE product_code = '0001'

--
David Portas
SQL Server MVP
--

[--CELKO--]

Sybase does NOT follow the ANSI standards. You also used TWO names
for the same data element. In a correct data model, there is no such
thing as an "ID"; it is the identifier of something. Why is a
product_code different from a product_id? Is this what you meant?
Without any DDL or a usable spec, we can only guess.

SELECT TRX.product_id, '1001'
FROM TRX, Products
WHERE Products.product_id = TRX.product_id;