Query

Question

Query

bhaumik_svit

Grasshopper

Points: 12
More actions
December 14, 2007 at 12:39 am

#62992

I have three table
MaterialInvetory
column --- id, materialinID, materialOUTID, date
MaterialIN
column --- id, date, stock
MaterialOUT
column- --- id, date, stock
now if MaterialIN of MaterialOUT add entry in to table at that time
MaterialInventory table also add the data
Mean at one query MaterialINID and MaterialOUTID , both not present
either one of them is present
Now I want query in which materialINID and materialOUTID
are come if both date is same otherwise only one is printed
in MaterialInventory
pls give me reply

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

I am not sure whether I understood what you want... I'm not even sure about relations between the tables. Can all three tables be joined on ID column?

If you want to merge IN and OUT rows with the same date, you could use this query (however, you will lose some information in case one material has several IN - or several OUT - rows with the same date):

SELECT id, date, MAX(materialinID), MAX(materialoutID)

FROM MaterialInventory

GROUP BY id, date

But that's probably not what you are looking for. Please, try to describe better what you need, and mention some example.

bhaumik_svit Grasshopper Points: 12 More actions · Answer 2

MaterialInvetory is connected with MaterialIN

and also MaterialOUT

but materialIN and MaterialOUT is not connected

MaterialInvetory has field

id, date, materiaINID, materialOUTID, CurrentStock

MaterialIN

id, date, stock

materialOUT

id, date, stock

when new data ADDed to MaterialIN

then MaterialInvetory is also add entry and currentStock is update

same process for MaterialOUt

NOW there are some data in table MateriaINventory

ID date MaterialINID MaterialOUtID STock

web12007-12-14 11:04:48.640 NULLweb180

web22007-12-14 11:04:48.640 NULLweb290

web3 2007-12-14 11:05:00.357 NULLweb3100

web42007-12-14 11:05:12.623 NULLweb410

web82007-12-14 11:06:28.983 web1NULL90

web92007-12-14 11:06:36.060 web2NULL35

AND I want to show it in

date materialID stock materialOUTID stock

2007-12-14 web1 90 web1 80

GSquared SSC Guru Points: 260824 More actions · Answer 3

It looks to me like your MaterialInventory table should be a view, not a table. A union query between MaterialIn and MaterialOut might be better than a table. Then you'd have all your data in the format you want, and it would be up-to-date automatically.

Would that do what you need?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Vladan SSC-Insane Points: 22072 More actions · Answer 4

bhaumik,

I still have problem to see through your data structure.

What means "id" column in table MaterialInventory? From your example with "web1" it seems, that "id" in table MaterialInventory is just internal ID of this table, and has nothing in common with "id" columns in the remaining two tables. Is that right?

I agree with GSquared that the entire table MaterialInventory might be unnecessary and you could replace it with a view. Generally, this table is quite close to UNION of the remaining two tables. If there is some reason behind this table, please explain it...

Also, what if there are several rows with the same date and material in one of the 2 fact tables? Or is that something that can't happen by design (like UNIQUE CONSTRAINT on these columns)?