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]

Re: Advanced SQL Query Problem ...

On 26 May 2004 21:00:56 -0700, Chen Fock Siong wrote:
[color=blue]
>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[/color]

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]

Re: Advanced SQL Query Problem ...

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'

fschen@biztrak. biz (Chen Fock Siong) wrote in message news:<f68938bb. 0405262000.4f9e be4a@posting.go ogle.com>...[color=blue]
> 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[/color]

[David Portas]

Re: Advanced SQL Query Problem ...

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--]

Re: Advanced SQL Query Problem ...

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.produc t_id = TRX.product_id;