3 subselect in the query sql

Question

Post reply

3 subselect in the query sql

ayreshp

SSC Enthusiast

Points: 114
More actions
April 16, 2008 at 8:16 am

#186554

Hello Guys!
I have one query that work with 2 subselect
the query is:
"SELECT prod.*, B.Bounce as Bounce
FROM [SELECT pn, modelo, Count(pn) as ENC from PMarço WHERE tag_atual = 'CDGAR' GROUP BY pn , modelo]. AS prod
LEFT JOIN [SELECT pn, Count(pn) as Bounce from BMarço WHERE day_bnc < 31 AND tag_atual = 'CDGAR' GROUP BY pn]. AS B
ON prod.pn = B.pn"
Ok this is working
now, I would like add more one subselect in my query
is that:
select pn, Count(pn) as NTF from BMarço where day_bnc < 31 AND pbl_desc_atual = 'Defeito não encontrado' AND tag_atual = 'CDGAR' GROUP BY pn
the idea is show select prod.*, B.Bounce as Bounce, here 3subselect.
How can I do ?
tks!

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

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

Perry Harts Ten Centuries Points: 1271 More actions · Answer 1

You could create a view, then query the view or add a derived table.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Well, a derived table IS a subselect (as Gail pointed out to me once).

Anyway, i am just guessing since we do not really have enough information to complete this, but try this:

SELECT prod.*, B.Bounce AS Bounce, sub3.NTF

FROM [SELECT pn, modelo, COUNT(pn) AS ENC

FROM PMarço

WHERE tag_atual = 'CDGAR'

GROUP BY pn , modelo]. AS prod

LEFT JOIN [SELECT pn, COUNT(pn) AS Bounce

FROM BMarço

WHERE day_bnc < 31

AND tag_atual = 'CDGAR'

GROUP BY pn]. AS B

ON prod.pn = B.pn

LEFT JOIN [SELECT pn, COUNT(pn) AS NTF

FROM BMarço

WHERE day_bnc < 31

AND pbl_desc_atual = 'Defeito não encontrado'

AND tag_atual = 'CDGAR'

GROUP BY pn] AS sub3

ON prod.pn = sub3.pn

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ayreshp SSC Enthusiast Points: 114 More actions · Answer 3

thanks for answer

I Did

SELECT prod.*, B.Bounce AS Bounce, NTF.NTF, (Bounce - NTF.NTF) AS B_Real, ROUND((NTF.NTF/(Bounce)*100),2) AS SDEF, ROUND((B_Real/(prod.ENC)*100),2) AS INDB, ROUND((Bounce/(prod.ENC)*100),2) AS TTB

FROM ([SELECT pn, modelo, Count(pn) as ENC from PMarço WHERE tag_atual = 'SDXGAR' OR tag_atual = 'GAR' GROUP BY pn , modelo]. AS prod

LEFT JOIN [SELECT pn, Count(pn) as Bounce from BMarço WHERE day_bnc < 31 AND tag_atual = 'SDXGAR' OR tag_atual = 'GAR' GROUP BY pn]. AS B

ON prod.pn = B.pn)

LEFT JOIN [SELECT pn, Count(pn) AS NTF FROM BMarço WHERE day_bnc < 31 AND pbl_desc_atual = 'Defeito não encontrado' AND tag_atual = 'SDXGAR' OR tag_atual = 'GAR' GROUP BY pn]. AS NTF

ON B.pn = NTF.pn

this work but for it is necessary to have all Conditions.

for exemplo incase I dont have the condition tag_atual = 'SDXGAR' , it not work well 🙁

What Can I do?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

You'll have to give us more information.

Here is a link that explains how: http://www.sqlservercentral.com/articles/Best+Practices/61537/

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ayreshp SSC Enthusiast Points: 114 More actions · Answer 5

Thanks rbarryyoung !

I get the solution hehe

was one condition wrong .

Tks again

Ayres