• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 57
  • Last Modified:

Query Statement | Need Help | URGENT ||

Scenario1:-

SELECT DISTINCT MATERIAL_CAT11_CD AS A
FROM TABLE1 M
where curr_rec_ind = 'Y' and (MATERIAL_CAT11_CD <> '-' and MATERIAL_CAT11_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD (Coming From Table XX)

Scenario2:-

SELECT DISTINCT MATERIAL_CAT13_CD AS B
FROM TABLE1 M
where curr_rec_ind = 'Y' and (MATERIAL_CAT13_CD <> '-' and MATERIAL_CAT13_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD(Coming From Table XX)

Scenario3:-

SELECT DISTINCT MATERIAL_CAT12_CD AS C
FROM TABLE1 M
where curr_rec_ind = 'Y' and (MATERIAL_CAT12_CD <> '-' and MATERIAL_CAT12_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD(Coming From Table XX)


--------------------------------------------------------------------------------------------------------------------------------------------------------------

Please help experts I need to combine all three of the above scenarios in one combination.

<<Help URGENT>>
0
Jasmine Sandlas
Asked:
Jasmine Sandlas
  • 4
  • 4
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
for which database engine? your topics are showing both MySql and MS SQL. They are 2 different beast!

Can't you just "union all" your 3 queries?

BTW, if it is urgent, you should have set the priority to high.
0
 
Jasmine SandlasAuthor Commented:
This is for Microsoft SQL server
0
 
Éric MoreauSenior .Net ConsultantCommented:
Can't you just "union all" your 3 queries?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jasmine SandlasAuthor Commented:
coming from the same table (TABLE1) - can't it be combined and have 1 combination. UNION ALL will not give perfect results.
0
 
Éric MoreauSenior .Net ConsultantCommented:
you are not querying for the same fields in your 3 queries.

have you tried (you don't even need the DISTINCT clause as the UNION takes care of it!):
SELECT MATERIAL_CAT11_CD AS A
FROM TABLE1 M
where curr_rec_ind = 'Y' and (MATERIAL_CAT11_CD <> '-' and MATERIAL_CAT11_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD (Coming From Table XX)

union 

SELECT MATERIAL_CAT13_CD AS B
FROM TABLE1 M
where curr_rec_ind = 'Y' and (MATERIAL_CAT13_CD <> '-' and MATERIAL_CAT13_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD(Coming From Table XX)

union 

SELECT MATERIAL_CAT12_CD AS C
FROM TABLE1 M
where curr_rec_ind = 'Y' and (MATERIAL_CAT12_CD <> '-' and MATERIAL_CAT12_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD(Coming From Table XX)

Open in new window

0
 
Jasmine SandlasAuthor Commented:
This doesn't work, Need to populate all the three fields (A,B,C) with the conditions that are given in Another Table TEST
0
 
Éric MoreauSenior .Net ConsultantCommented:
do you need to return the values in 3 rows or 3 columns?

for rows, add a dummy field to each sub-query:
SELECT 11 as Category, MATERIAL_CAT11_CD AS A
FROM TABLE1 M
where curr_rec_ind = 'Y' and (MATERIAL_CAT11_CD <> '-' and MATERIAL_CAT11_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD (Coming From Table XX)
union 
SELECT 13,MATERIAL_CAT13_CD AS B
FROM TABLE1 M
where curr_rec_ind = 'Y' and (MATERIAL_CAT13_CD <> '-' and MATERIAL_CAT13_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD(Coming From Table XX)
union 
SELECT 12, MATERIAL_CAT12_CD AS C
FROM TABLE1 M
where curr_rec_ind = 'Y' and (MATERIAL_CAT12_CD <> '-' and MATERIAL_CAT12_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD(Coming From Table XX)

Open in new window


if you need columns:
select 
(SELECT MATERIAL_CAT11_CD AS A FROM TABLE1 M where curr_rec_ind = 'Y' and (MATERIAL_CAT11_CD <> '-' and MATERIAL_CAT11_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD (Coming From Table XX)) AS Cat11
, (SELECT MATERIAL_CAT13_CD AS B FROM TABLE1 M where curr_rec_ind = 'Y' and (MATERIAL_CAT13_CD <> '-' and MATERIAL_CAT13_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD(Coming From Table XX)) AS Cat13
, (SELECT MATERIAL_CAT12_CD AS C FROM TABLE1 M where curr_rec_ind = 'Y' and (MATERIAL_CAT12_CD <> '-' and MATERIAL_CAT12_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD(Coming From Table XX)) AS Cat12

Open in new window

0
 
Jasmine SandlasAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now