help in query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help in query

Author

Topic

simi28
Starting Member

27 Posts

Posted - 2007-04-09 : 05:22:47

Hi All

I have to retrieve few fields from few tables.
like
poid=im_purchase_order,im_products_stock_logs
warehouseid=im_purchase_order,im_products_stock_logs,im_warehouse_master
order_date-im_purchase_order
ship_date-im_purchase_order
date_promised=im_purchase_order
qty=im_products_stock_logs

for that I have written a query like
//
select im_purchase_order.poid,im_purchase_order.warehouseid,
isnull(im_warehouse_master.company_name,'-') as warehousename,
order_date,date_promised,ship_date,im_products_stock_logs.qty from
im_products_stock_logs inner join im_purchase_order on
im_purchase_order.poid= im_products_stock_logs.poid left join
im_warehouse_master on im_purchase_order.warehouseid=
im_warehouse_master.warehouseid where log_type='Posted'
and productid=657 and colorid=0 order by order_date desc
//

the above query is giving me 2 records
//
select im_purchase_order.poid,im_purchase_order.warehouseid,
isnull(im_warehouse_master.company_name,'-') as warehousename,
order_date,date_promised,ship_date,im_products_stock_logs.qty from
im_products_stock_logs inner join im_purchase_order on
im_purchase_order.poid= im_products_stock_logs.poid left join
im_warehouse_master on im_purchase_order.warehouseid=
im_warehouse_master.warehouseid where log_type='Posted'or log_type='Accepted'
and productid=657 and colorid=0 order by order_date desc
//
this record giving me 29 records which is not correct.

I know the query is wrong but I dont know how to modify it
can anybody help me to solve this problem?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-09 : 05:26:05

"I know the query is wrong but I dont know how to modify it"

And I don't know what you want, so I cannot modify it, either!

Please post relevant table structures, sample data and desired output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

simi28
Starting Member

27 Posts

Posted - 2007-04-09 : 05:43:16

I have three tables from which i need to retirve data
1)im_warehouse_master-warehouseid(numeric),warehouse(varchar)
2)im_products_stock_logs - poid(numeric),wrehouseid(numeric),qty(int),log_type(varchar),productid(numeric)
3)im_purchase_order-warehouseid(numeric),poid(nmeric),orer_date(datetime),ship_date(datetime),date_promised(datetime)
the fields I require from the tables are
poid-
warehouseid-
order_date
ship_date
order_promised
quantity
the output should like this
poid warehouseid warehousename order_date ship_date order_promised quantity log_type
63 10 test 4/3/2007 4/3/2007 4/3/2007 25 posted
71 0 test 4/3/2007 4/3/2007 4/3/2007 10 posted
45 10 test 4/3/2007 4/3/2007 4/3/2007 2 Accepted

but in my query in not getting the third one whose log_type is Accepted.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 05:44:32

Also post the sample data

simi28
Starting Member

27 Posts

Posted - 2007-04-09 : 05:55:53

im_warehouse_master
warehouseid warehousename
0 test
10 test1

im_purchase_order
poid warehouseid order_date ship_date date_promised
63 10 4/3/2003 4/3/2003 4/3/2003

im_products_stock_log

poid warehouseid qty log_type
63 10 25 posted
45 10 10 accepted
71 0 25 posted

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-09 : 06:05:07

You have not shown productid in your sample data. Also to which table, column "colorid" belongs to?

select 
	im_purchase_order.poid,
	im_purchase_order.warehouseid, 
	isnull(im_warehouse_master.company_name,'-') as warehousename, 
	order_date,
	date_promised,
	ship_date,
	im_products_stock_logs.qty 
from 
	im_products_stock_logs inner join im_purchase_order on im_purchase_order.poid= im_products_stock_logs.poid 
	left join im_warehouse_master on im_purchase_order.warehouseid= im_warehouse_master.warehouseid 
where 
	log_type in ('Posted','Accepted')
	and productid=657 
	and colorid=0 
order by 
	order_date desc

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

simi28
Starting Member

27 Posts

Posted - 2007-04-09 : 06:17:57

thanks its working perfectly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources