November 3, 2009 at 9:52 am
Hi, this one is giving me some hard time this morning...
Let's say I have this request:
SELECT IForf.Bundle, IAM.Analyse, IAM.Material
FROM env_anal_auto IForf
INNER JOIN t_anal_env_mat IAM
ON IForf.Analyse = IAM.Analyse
Returning value like theses:
BundleAnalyseMaterial
XCOM AL 100
XCOM AL 905
XCOM AL 909
XCOM AS 100
XCOM AS 905
XCOM AS 909
XCOM B1 100
XCOM B1 905
XCOM B1 909
XCOM *V 100
XCOM *V 905
XCOM *V 909
XCOM *V 913
...
How can I get Material common for all Analyse for a Bundle??
In this example, result would be like this:
BundleMaterial
XCOM100
XCOM905
XCOM909
Because Material 913 is only available with *V
Thanks,
Frank
November 3, 2009 at 9:55 am
So You don't need the Analyse Column at all?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 3, 2009 at 9:57 am
I think something like this would work.
CREATE TABLE #temp (bundle VARCHAR(10), analyse VARCHAR(10), material INT)
INSERT INTO #temp
SELECT 'XCOM','AL',100
UNION ALL
SELECT 'XCOM','AL',905
UNION ALL
SELECT 'XCOM','AL',909
UNION ALL
SELECT 'XCOM','AS',100
UNION ALL
SELECT 'XCOM','AS',905
UNION ALL
SELECT 'XCOM','AS',909
UNION ALL
SELECT 'XCOM','B1',100
UNION ALL
SELECT 'XCOM','B1',905
UNION ALL
SELECT 'XCOM','B1',909
UNION ALL
SELECT 'XCOM','*V',100
UNION ALL
SELECT 'XCOM','*V',905
UNION ALL
SELECT 'XCOM','*V',909
UNION ALL
SELECT 'XCOM','*V',913
SELECT bundle, material FROM #temp
GROUP BY bundle, material HAVING COUNT(*) = (SELECT COUNT(DISTINCT analyse) FROM #temp)
DROP TABLE #temp
November 3, 2009 at 10:24 am
Fast replies!! thanks! 🙂
Bru Medishetty (11/3/2009)
So You don't need the Analyse Column at all?
No, I don't need it in the result.
@ Matt
Yeah, your solution is pretty close, in fact, it's working as wanted if I have only one bundle involve. But what should I add to it if my insert to #temp look like this? And this select return many bundle.
CREATE TABLE #temp (bundle VARCHAR(10), analyse VARCHAR(10), material INT)
INSERT INTO #temp
SELECT IForf.Bundle, IAM.Analyse, IAM.Material
FROM env_anal_auto IForf
INNER JOIN t_anal_env_mat IAM
ON IForf.Analyse = IAM.Analyse
Frank
November 3, 2009 at 11:12 am
Why would you need the Column Analyse in the Temp table when you don't need it?
Can't the below code work?
SELECT IForf.Bundle, IAM.Material
FROM env_anal_auto IForf
INNER JOIN t_anal_env_mat IAM
ON IForf.Analyse = IAM.Analyse
GROUP BY IForf.Bundle, IAM.Material
When you only need Bundle and Material from these 2 table, why do you need to create a temp Table and populate that?
Obviously, I might be misunderstanding your requirement.. Please correct me if I have overlooked something...
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 3, 2009 at 11:35 am
Bru Medishetty (11/3/2009)
Why would you need the Column Analyse in the Temp table when you don't need it?Can't the below code work?
SELECT IForf.Bundle, IAM.MaterialFROM env_anal_auto IForf
INNER JOIN t_anal_env_mat IAM
ON IForf.Analyse = IAM.Analyse
GROUP BY IForf.Bundle, IAM.Material
When you only need Bundle and Material from these 2 table, why do you need to create a temp Table and populate that?
Obviously, I might be misunderstanding your requirement.. Please correct me if I have overlooked something...
I need Column Analyse to filter the result. Here is a part of the return of the select use to populate the #temp:
Bundle Analyse Material
ALDPCHO2186
ALDVCHO2186
ALDISOCHO2186
ALDISVCHO2186
ALDFURCHO2186
ALDHCHO2186
ALDHEPCHO2186
ALDHEXCHO2186
ALDACHO2186
ALDACROL2186
ALDBCHO2186
AMINDIE955
AMINDIM955
AMINETHA955
AMINANI955
AMINMETH955
AMINMORP955
AMINISOP955
[...]
XCOM*AG100
XCOM*AG905
XCOM*AG909
XCOM*AG913
XCOM*AG915
XCOM*AG916
XCOM*AL100
XCOM*AL905
XCOM*AL909
XCOM*AS100
XCOM*AS905
XCOM*AS909
XCOM*B1100
XCOM*B1905
XCOM*B1909
XCOM*BA100
XCOM*BA905
XCOM*BA909
XCOM*BE100
XCOM*BE905
XCOM*BE909
XCOM*BIS100
XCOM*BIS905
XCOM*BIS909
XCOM*CA100
XCOM*CA905
XCOM*CA909
XCOM*CD100
XCOM*CD905
XCOM*CD909
XCOM*CO100
XCOM*CO905
XCOM*CO909
XCOM*CR100
XCOM*CR905
XCOM*CR909
XCOM*CU100
XCOM*CU905
XCOM*CU909
XCOM*FE100
XCOM*FE905
XCOM*FE909
XCOM*K100
XCOM*K905
XCOM*K909
XCOM*LI100
XCOM*LI905
XCOM*LI909
XCOM*MG100
XCOM*MG905
XCOM*MG909
XCOM*MN100
XCOM*MN905
XCOM*MN909
XCOM*MO100
XCOM*MO905
XCOM*MO909
XCOM*NI100
XCOM*NI905
XCOM*NI909
XCOM*PB100
XCOM*PB905
XCOM*PB909
XCOM*SB100
XCOM*SB905
XCOM*SB909
XCOM*SE100
XCOM*SE905
XCOM*SE909
XCOM*SN100
XCOM*SN905
XCOM*SN909
XCOM*SO100
XCOM*SO905
XCOM*SO909
XCOM*SR100
XCOM*SR905
XCOM*SR909
XCOM*TE100
XCOM*TE905
XCOM*TE909
XCOM*THA100
XCOM*THA905
XCOM*THA909
XCOM*TI100
XCOM*TI905
XCOM*TI909
XCOM*V100
XCOM*V905
XCOM*V909
XCOM*V913
XCOM*ZN100
XCOM*ZN905
XCOM*ZN909
The result should keep all Bundle-Material for which the material could be use in all analyse of the bundle.
As example, for the bundle XCOM the result should be:
Bundle Material
XCOM100
XCOM905
XCOM909
Because material 913, 915 and 916 can't be used in all analyse of the bundle XCOM.
I hope my goal is clearer now.
Thanks for your help!
Frank
November 3, 2009 at 11:38 am
Yeah, I was just creating a temp table so I had something to work with. Since you have the actual source tables you won't have to do that. So since I don't have the source tables I am going to try to do this without testing. 😀
Let me know if this works.
SELECT IForf.Bundle, IAM.Material
FROM env_anal_auto IForf
INNER JOIN t_anal_env_mat IAM
ON IForf.Analyse = IAM.Analyse
group by IForf.Bundle, IAM.Material having count(*) = (select count(distinct analyse) from env_anal_auto IForf2 where IForf.bundle = IForf2.bundle)
November 3, 2009 at 11:52 am
Matt Wilhoite (11/3/2009)
Let me know if this works.
YAHOOOO!
Thanks man!!! 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply