Returning one field multiple times with different values

[Marcotte]

Table 1 has Merchandise Class and 4 GL account number fields.

Merch     InventoryAcct      CostAcct      PayableAcct   SalesAcct
Fish          1000             5000             2000          4000
Beef          1100             5100             2100          4100

Table 2 is the GL Master table which hold Account #s and Account Descriptions

Acct      Description
1000       Fish Inventory
1100       Beef Inventory
etc.

I want to return:

Merch   InvAcct   InvAcctDesc         CostAcct    CostAcctDesc    etc....
Fish      1000      Fish Inventory      5000           Fish Cost
Beef      1100      Beef Inventory      5100           Beef Cost
etc..

I am querying an Informix database (unsure of version) on a Linux (Redhat) server using a variety of tools, including Crystal Reports and MS Query (through and ODBC link) and 'dbaccess', a program on the Linux server.

Also, as a bonus question, what book(s) or websites would you recommend to me to teach myself SQL?

[ck9663]

Table 1 has Merchandise Class and 4 GL account number fields.

Merch     InventoryAcct      CostAcct      PayableAcct   SalesAcct
Fish          1000             5000             2000          4000
Beef          1100             5100             2100          4100

Table 2 is the GL Master table which hold Account #s and Account Descriptions

Acct      Description
1000       Fish Inventory
1100       Beef Inventory
etc.

I want to return:

Merch   InvAcct   InvAcctDesc         CostAcct    CostAcctDesc    etc....
Fish      1000      Fish Inventory      5000           Fish Cost
Beef      1100      Beef Inventory      5100           Beef Cost
etc..

I am querying an Informix database (unsure of version) on a Linux (Redhat) server using a variety of tools, including Crystal Reports and MS Query (through and ODBC link) and 'dbaccess', a program on the Linux server.

Also, as a bonus question, what book(s) or websites would you recommend to me to teach myself SQL?

Try:

select Merch, InventoryAcct , CostAcct, PayableAcct, SalesAcct
from MerchandiseClass
inner join GLMaste ron MerchandiseClass.InventoryAcct = GLMaster.Acct

You did not include the table for CostAcctDesc so I would'nt know where to get it.

Also, INNER join was used assuming all InventoryAcct are in Acct

-- CK

[Marcotte]

Try:

select Merch, InventoryAcct , CostAcct, PayableAcct, SalesAcct
from MerchandiseClass
inner join GLMaste ron MerchandiseClass.InventoryAcct = GLMaster.Acct

You did not include the table for CostAcctDesc so I would'nt know where to get it.

Also, INNER join was used assuming all InventoryAcct are in Acct

-- CK

CostAcctDesc is on the GLMaster table.
Don't I also need to select AcctDesc?

I get a syntax error when I try your query (see below). Oddly, it appears that the error is "join". After I get the syntax error message, when I go back into the editor the cursor goes to the "join" on line three.

select m_mcl, m_cst, m_sal, m_cic, m_ipa
from mglasg
inner join glgmas on mglasg.m_cst = glgmas.glmacn

table mglasg (Merchandise Class Master) contains:
m_mcl = Merch Class Code
m_cst = Inventory Acct
m_sal = Sales Acct
m_cic = Cost Acct
m_ipa = Payable Acct

glgmas is the GL Master table which is a list of GL accounts (glmacn) and their corresponding descriptions (glmdes).

Here is what I originally tried:
select m_mcl, m_cst, m_sal, m_cic, m_ipa, glmdes
from mglasg, glgmas
where m_cst = glmacn

but this returns:

Merch   InvAcct   InvAcctDesc         CostAcct    CostAcctDesc    etc....
Fish      1000      Fish Inventory      5000           Fish Inventory
Beef      1100      Beef Inventory      5100           Beef Inventory
etc..

In other words, it's matching the inventory account (oddly named m_cst) to the glgmas.glmacn field and returning glmdes which is Fish (or Beef, etc.) Inventory, whereas I want column 3 of my returned data to be the Inventory Account Description and column 5 to be the Cost Account Description (there would also be columns 6-9 for Sales and Payable account # and description).

[ck9663]

CostAcctDesc is on the GLMaster table.
Don't I also need to select AcctDesc?

I get a syntax error when I try your query (see below). Oddly, it appears that the error is "join". After I get the syntax error message, when I go back into the editor the cursor goes to the "join" on line three.

select m_mcl, m_cst, m_sal, m_cic, m_ipa
from mglasg
inner join glgmas on mglasg.m_cst = glgmas.glmacn

table mglasg (Merchandise Class Master) contains:
m_mcl = Merch Class Code
m_cst = Inventory Acct
m_sal = Sales Acct
m_cic = Cost Acct
m_ipa = Payable Acct

glgmas is the GL Master table which is a list of GL accounts (glmacn) and their corresponding descriptions (glmdes).

Here is what I originally tried:
select m_mcl, m_cst, m_sal, m_cic, m_ipa, glmdes
from mglasg, glgmas
where m_cst = glmacn

but this returns:

Merch   InvAcct   InvAcctDesc         CostAcct    CostAcctDesc    etc....
Fish      1000      Fish Inventory      5000           Fish Inventory
Beef      1100      Beef Inventory      5100           Beef Inventory
etc..

In other words, it's matching the inventory account (oddly named m_cst) to the glgmas.glmacn field and returning glmdes which is Fish (or Beef, etc.) Inventory, whereas I want column 3 of my returned data to be the Inventory Account Description and column 5 to be the Cost Account Description (there would also be columns 6-9 for Sales and Payable account # and description).

Sorry, my bad. the GLMaster and on got stuck together.

Try this:

select Merch, InventoryAcct , CostAcct, PayableAcct, SalesAcct, inventory.Description as InvAcctDesc, Cost.Description as CostAcctDesc              
from MerchandiseClass
inner join GLMaster inventory on MerchandiseClass.InventoryAcct = inventory.Acct
inner join GLMaster Cost on MerchandiseClass.CostAcct = Cost.Acct

-- CK

[Marcotte]

Sorry, my bad. the GLMaster and on got stuck together.

Try this:

select Merch, InventoryAcct , CostAcct, PayableAcct, SalesAcct, inventory.Description as InvAcctDesc, Cost.Description as CostAcctDesc              
from MerchandiseClass
inner join GLMaster inventory on MerchandiseClass.InventoryAcct = inventory.Acct
inner join GLMaster Cost on MerchandiseClass.CostAcct = Cost.Acct

-- CK

No, the syntax error wasn't due to the "GLMaste ron" part of your first post. I translated it all into the field names. The query that generated the error is that one in my first reply (2nd post), and it appears not to like the join keyword for some strange reason.

I have a call into our software vendor. Perhaps it has something to do with this particular flavor of Informix. When I've needed to to inner joins before I've just used a WHERE clause.

In the query you just posted, what exactly do you mean by "inventory.description as InvAcctDesc"? Which of those are table names, field names and aliases?

[ck9663]

No, the syntax error wasn't due to the "GLMaste ron" part of your first post. I translated it all into the field names. The query that generated the error is that one in my first reply (2nd post), and it appears not to like the join keyword for some strange reason.

I have a call into our software vendor. Perhaps it has something to do with this particular flavor of Informix. When I've needed to to inner joins before I've just used a WHERE clause.

In the query you just posted, what exactly do you mean by "inventory.description as InvAcctDesc"? Which of those are table names, field names and aliases?

Whenever you see this ""inventory.description as InvAcctDesc", it means inventory.description is a column on the table and InvAcctDesc will be the new name/alias. If you save the query into a new table using the INTO keyword, the new name/alias will be the column name of the new table. So it has to be unique.

-- CK

[Marcotte]

I finally got it to work with this:

select m_mcl , m_dsc, m_sal ,
(select glmdsc Desc from glgmas where
m_sal=glmacn),
 
m_cst,
(select glmdsc from glgmas where
m_cst=glmacn),
 
m_cic,
(select glmdsc from glgmas where
m_cic=glmacn),
 
m_ipa,
(select glmdsc from glgmas where
m_ipa=glmacn)
 
from mglasg
 

[ck9663]

That'll be slow specially if you have big tables. But if it suit you and it's working, it's your call.

-- CK