468,121 Members | 1,457 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,121 developers. It's quick & easy.

Joinging multiple select query into one select query(multiple tables and attributes)

1
Hi!
II was wondering if you can help me with SQL query..

Below 7 separated select query works fine(only when they are retrieved separately)
But I want to combined them together and so that i can make report from my database on the one sheet rather than querying each select statement ( I will use MS Query from the Excel)
================================================== =======
1)

select convert(varchar, create_date_time, 112) as Date, count(pkt_ctrl_nbr) as Count
from outpt_pkt_hdr (nolock) where stat_code<>99
group by convert(varchar, create_date_time, 112)
order by convert(varchar, create_date_time, 112)

2)
select convert(varchar, create_date_time, 112) as Date, sum(units_pakd) as "Sum Units"
from outpt_carton_dtl (nolock)
group by convert(varchar, create_date_time, 112)
order by convert(varchar, create_date_time, 112)

3)

select convert(varchar, create_date_time, 112) as Date, sum(units_pakd) as "Units Sum"
from outpt_carton_dtl (nolock) where style like 'S%'
group by convert(varchar, create_date_time, 112)
order by convert(varchar, create_date_time, 112)



4)
select convert(varchar, create_date_time, 112) as Date, sum(units_pakd) as "Units Sum"
from outpt_carton_dtl (nolock) where style not like 'S%'
group by convert(varchar, create_date_time, 112)
order by convert(varchar, create_date_time, 112)

5)
select convert(varchar, create_date_time, 112) as Date, count(carton_nbr) as Count
from outpt_carton_hdr (nolock)
group by convert(varchar, create_date_time, 112)
order by convert(varchar, create_date_time, 112)

6)

select convert(varchar, create_date_time, 112) as Date, count(carton_nbr) as Count
from outpt_carton_hdr (nolock) where carton_size in ('A4', 'A5')
group by convert(varchar, create_date_time, 112)
order by convert(varchar, create_date_time, 112)

7)
select convert(varchar, create_date_time, 112) as Date, count(carton_nbr) as Count
from outpt_carton_hdr (nolock) where carton_type = 'POS'
group by convert(varchar, create_date_time, 112)
order by convert(varchar, create_date_time, 112)

================================================== =======

Below is what I combined

It works fine!

But!! If you see above language, there are WHERE attribute in each SELECT Query
which is in order to get desired data only.
(If I use below query, it comes with Mass!!)
So we need to put WHERE attribute somewhere below
(I need to have where attribute in each different columns as seen above statement)
However, since I already input some query in order to join them in WHERE attribute, I am confused how I can do.



++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++

SELECT OUTPT_PKT_HDR.CREATE_DATE_TIME AS 'Date',
Count(OUTPT_PKT_HDR.PKT_CTRL_NBR) AS 'SO',
Sum(OUTPT_CARTON_DTL.UNITS_PAKD) AS 'SU',
Sum(OUTPT_CARTON_DTL.UNITS_PAKD) AS 'SUP',
Sum(OUTPT_CARTON_DTL.UNITS_PAKD) AS 'SUS',
Count(OUTPT_CARTON_HDR.CARTON_NBR) AS 'SC',
Count(OUTPT_CARTON_HDR.CARTON_NBR) AS 'SCJ',
Count(OUTPT_CARTON_HDR.CARTON_NBR) AS 'SCP'

FROM PKMS.dbo.OUTPT_CARTON_DTL OUTPT_CARTON_DTL,
PKMS.dbo.OUTPT_CARTON_HDR OUTPT_CARTON_HDR,
PKMS.dbo.OUTPT_PKT_HDR OUTPT_PKT_HDR

WHERE OUTPT_PKT_HDR.INVC_BATCH_NBR = OUTPT_CARTON_DTL.INVC_BATCH_NBR AND

OUTPT_PKT_HDR.PKT_CTRL_NBR = OUTPT_CARTON_DTL.PKT_CTRL_NBR AND
OUTPT_CARTON_HDR.CARTON_NBR = OUTPT_CARTON_DTL.CARTON_NBR AND
OUTPT_CARTON_HDR.INVC_BATCH_NBR = OUTPT_CARTON_DTL.INVC_BATCH_NBR AND
OUTPT_CARTON_HDR.INVC_BATCH_NBR = OUTPT_PKT_HDR.INVC_BATCH_NBR AND
OUTPT_CARTON_HDR.PKT_CTRL_NBR = OUTPT_PKT_HDR.PKT_CTRL_NBR

GROUP BY OUTPT_PKT_HDR.CREATE_DATE_TIME
ORDER BY OUTPT_PKT_HDR.CREATE_DATE_TIM

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++


Anyway, Thank you in advance


^____________^
Feb 12 '07 #1
0 7248

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Chris | last post: by
11 posts views Thread by dskillingstad | last post: by
1 post views Thread by Felix_WafyTech | last post: by
18 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.