I think I might have complicated myseft with this table structure but I've
created a Products table to store different manufacturers of the same model
and their respective product codes:

TABLE1

CodProd  Manuf1  CodManuf1  Manuf2  CodManuf2
CP001    M012    Acme0456     M045    Xpto0234
CP002    M045    Xpto7228     M052    Abcd0001
CP003    M006    Hilo34       M012    Acme1436

What I want now is to retrieve a list of all products made by one
manufacturer, with its respective product code and my model code.
Let's say, for M012, this would be the results:

CodProd  CodManuf
CP001    Acme0456
CP003    Acme1436

I've already done a query to find my code, like
SELECT CodProd FROM Table1 WHERE ((Manuf1 = :Manufacturer) OR (Manuf2 =
:Manufacturer))
So I just need to fill the ":Manufacturer" ParamByName.

But how do I put those different manufacturers code into the same column at
the query?

Thanks
Marcio Ehrlich

"M.E." <no@no.com.br> wrote in message
news:805E6B9C-D932-4FF1-A267-E94B1DB0F494@news.elevatesoft.com...
>
> But how do I put those different manufacturers code into the same column
> at the query?
>

SELECT CODEPROD, CODEMANUF1 FROM TABLE
UNION
SELECT CODEPROD, CODEMANUF2 FROM TABLE
WHERE ((Manuf1 = :Manufacturer) OR (Manuf2 =
:Manufacturer))
ORDER BY CODEPROD


Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:8F754754-A29B-4390-A0D0-3457137509AE@news.elevatesoft.com...
>
> "M.E." <no@no.com.br> wrote in message
> news:805E6B9C-D932-4FF1-A267-E94B1DB0F494@news.elevatesoft.com...
>>
>> But how do I put those different manufacturers code into the same column
>> at the query?
>>
>
> SELECT CODEPROD, CODEMANUF1 FROM TABLE
> UNION
> SELECT CODEPROD, CODEMANUF2 FROM TABLE
> WHERE ((Manuf1 = :Manufacturer) OR (Manuf2 =
> :Manufacturer))
> ORDER BY CODEPROD
>

Of course, you need the where clause on each select.

Thanks, Robert, I got the idea. I have never used the UNION clause before,
it was a very good hint.
Indeed this is the code I got the best result now, without using the "OR":

SELECT CODEPROD, CODEMANUF1 AS CODMANUF FROM TABLE
  WHERE Manuf1 = :Manufacturer
UNION
SELECT CODEPROD, CODEMANUF2 AS COD MANUF FROM TABLE
  WHERE Manuf2 = :Manufacturer
ORDER BY CODEPROD

Thanks again,
Marcio


"Robert" <ngsemail2005withoutthis@yahoo.com.ar> escreveu:
>> SELECT CODEPROD, CODEMANUF2 FROM TABLE
>> WHERE ((Manuf1 = :Manufacturer) OR (Manuf2 =
>> :Manufacturer))
>> ORDER BY CODEPROD
> Of course, you need the where clause on each select.