this is my first time posting here as i cannot find the answer myself.
I have couple tables i want to join and i can't seem to get it right. I
have the following tables:
Part: (Part ID), PartDescription
Part_warehouse: ( WarehouseID), (Part_ID), Available_QTY
Inventory_Trans: (Transaction_ID), PartID, QTY, TYPE
I want a query of the Available qty >0 for every part we have. When i
do a query like this, i get 5363 records.
SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
dbo.PART_WAREHOUSE.AVAILABLE_QTY
FROM dbo.PART left outer JOIN
dbo.PART_WAREHOUSE ON dbo.PART.ID =
dbo.PART_WAREHOUSE.PART_ID
WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
available_qty, part.unit_material_cost
Then i want to add a column for this query, the inventory_Trans.Qty
that has type =O. I tried the query below and it doesn't
work...obviously ican't inner join again from PART_WAREHOUSE as it
does the left join based on that table, so this wouldn't work:
SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
dbo.PART_WAREHOUSE.AVAILABLE_QTY
FROM dbo.PART
left outer JOIN dbo.PART_WAREHOUSE ON dbo.PART.ID =
dbo.PART_WAREHOUSE.PART_IS
left outer JOIN dbo.INVENTORY_TRANS ON dbo.PART_WAREHOUSE.PART_ID =
dbo.INVENTORY_TRANS.PART_ID
WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)AND
(INVENTORY_TRANS.TYPE='O')
group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
available_qty, part.unit_material_cost
I tried using this, but i am not familiar with this syntax and i am
getting errors.
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
FROM PART p1, PART p2
LEFT JOIN
dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
LEFT JOIN
dbo.INVENTORY_TRANS as i on p2.ID =
dbo.INVENTORY_TRANS.PART_ID
WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
group by w.WAREHOUSE_ID, p1.ID, p1.DESCRIPTION, w.available_qty,
p1.unit_material_cost
Order by p1.warehouse_Id
so i am out of ideas. Can anyone enlighten me about how to do this:'
thank you so much in advance.Although this probably isn't the answer that you are looking for, but I'm
wondering why you are using the GROUP BY clause in your query? You typically
use GROUP BY when using an aggregate function in the SELECT statement, such
as COUNT. Try running the second and third queries without the GROUP BY
clause.
Try
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
FROM PART p1
LEFT JOIN dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
LEFT JOIN dbo.INVENTORY_TRANS as i on w.ID = i.PART_ID
WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
Order by p1.warehouse_Id
"lytung@.gmail.com" wrote:
> Hi All,
> this is my first time posting here as i cannot find the answer myself.
> I have couple tables i want to join and i can't seem to get it right. I
> have the following tables:
> Part: (Part ID), PartDescription
> Part_warehouse: ( WarehouseID), (Part_ID), Available_QTY
> Inventory_Trans: (Transaction_ID), PartID, QTY, TYPE
> I want a query of the Available qty >0 for every part we have. When i
> do a query like this, i get 5363 records.
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART left outer JOIN
> dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
>
> Then i want to add a column for this query, the inventory_Trans.Qty
> that has type =O. I tried the query below and it doesn't
> work...obviously ican't inner join again from PART_WAREHOUSE as it
> does the left join based on that table, so this wouldn't work:
>
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART
> left outer JOIN dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_IS
> left outer JOIN dbo.INVENTORY_TRANS ON dbo.PART_WAREHOUSE.PART_ID =
> dbo.INVENTORY_TRANS.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)AND
> (INVENTORY_TRANS.TYPE='O')
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
> I tried using this, but i am not familiar with this syntax and i am
> getting errors.
> SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
> w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
> FROM PART p1, PART p2
> LEFT JOIN
> dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
> LEFT JOIN
> dbo.INVENTORY_TRANS as i on p2.ID =
> dbo.INVENTORY_TRANS.PART_ID
> WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
> group by w.WAREHOUSE_ID, p1.ID, p1.DESCRIPTION, w.available_qty,
> p1.unit_material_cost
> Order by p1.warehouse_Id
>
> so i am out of ideas. Can anyone enlighten me about how to do this:'
> thank you so much in advance.
>|||no that gave me an error.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'warehouse_Id'.
But even if that wo rked the logic doens't make sense.
I want the second query to be based on the first query. Maybe its not
about doing two joins but what i probably need is a transaction query.
First i need this:
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, w.WAREHOUSE_ID
FROM PART p1
LEFT JOIN dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
where ( w.AVAILABLE_QTY > 0)
Then i need the i.type='O' (from inventory_trans) based on those
results. I hope this make sense!|||Hi
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART left outer JOIN
> dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
is it possible that part with given ID doesn't belong to a part_warehouse?
in other words can you get null as warehouse_id in above query?
the second thing - group by clause here is really not necessary
> Then i want to add a column for this query, the inventory_Trans.Qty
> that has type =O. I tried the query below and it doesn't
> work...obviously ican't inner join again from PART_WAREHOUSE as it
> does the left join based on that table, so this wouldn't work:
how about this?
SELECT pw.WAREHOUSE_ID, p.ID, p.DESCRIPTION, p.UNIT_MATERIAL_COST,
pw.AVAILABLE_QTY
FROM dbo.PART p left outer JOIN
( dbo.PART_WAREHOUSE pw inner join dbo.INVENTORY_TRANS itr ON pw.PART_ID =
itr.PART_ID
) ON p.ID = pw.PART_ID
WHERE (pw.AVAILABLE_QTY > 0)
AND (itr.TYPE='O')
HTH
Peter|||Hi Peter,
thanks for replying. The query you gave me ended up with too many
records. You are right, i dont need the group by statement.
Part_Warehouse has 2 Primary Keys: Part_ID, and WAREHOUSE_ID
you skipped out the PART_WAREHOUSE join to PART. I guess for this join
it doesn't have to be a left join, but it has to be joined. The second
join has to be left, which you did...
I am getting
with mixing the joins. What is the general ruleof multiple joins? does the second join depend on the previous join? or
can they be independent?|||use parentheses to prioritize joins. the outer table is joined to result of
join in parentheses.
can you show the ddl of these tables and some sample data and describe
result you would like to obtain?
part_warehouse is a table that relates parts and warehouses?
> you skipped out the PART_WAREHOUSE join to PART. I guess for this join
> it doesn't have to be a left join, but it has to be joined. The second
> join has to be left, which you did...
FROM dbo.PART p left outer JOIN
( dbo.PART_WAREHOUSE pw inner join dbo.INVENTORY_TRANS itr ON pw.PART_ID =
itr.PART_ID
) ON p.ID = pw.PART_ID
no, I left joined PART to the result of inner join between PART_WAREHOUSE
and INVENTORY_TRANS.
again, do you have PARTs without WAREHOUSEs?
peter
