Subquery - 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

Other Forums

MS Access

Subquery

Author

Topic

dimoss
Yak Posting Veteran

52 Posts

Posted - 2006-05-28 : 09:11:51

Hi,

I have a table with 4 fields (id, nomarxia, adt, moria)

sample data follows..

Table: DE

id..nomarxia..adt.....moria
1...Ath.......M1234....700
2...Ath.......F5323....800
3...Ait.......M1234....700
4...Kav.......M1234....700
5...Xan.......F5323....800
6...Kav.......F5323....800
7...Spa.......G5654....1000
8...Xan.......L9876....1100
9...Ait.......P2134....950
10..Kav.......M1234....600

For the selected condition---> WHERE adt = "M1234"
I expect to get this result.

nomarxia.moria.MAX_moria..MIN_moria..Count_id
Ath......700...800........700...........2
Ait......700...950........700...........2
Kav......700...800........600...........3

I know that is a subquery problem...As I am novice to this I would appreciate any help..

www.tabletennis.gr

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-28 : 11:44:09

No idea if this works in Access, but in pure SQL it would be:

SELECT nomarxia
      ,moria
      ,(SELECT MAX(moria) FROM DE i WHERE i.nomarxia = o.nomarxia) AS MAX_moria
      ,(SELECT MIN(moria) FROM DE i WHERE i.nomarxia = o.nomarxia) AS MIN_moria
      ,(SELECT COUNT(moria) FROM DE i WHERE i.nomarxia = o.nomarxia) AS Count_id
FROM DE o
WHERE adt = "M1234"

SELECT nomarxia
      ,moria
      ,MAX_moria
      ,MIN_moria
      ,Count_id
FROM DE
INNER JOIN (
      SELECT nomarxia
            ,MAX(moria) AS MAX_moria
            ,MIN(moria) AS MIN_moria
            ,COUNT(moria) AS Count_id
           } AS DTbl
   ON DTbl.nomarxia = DE.nomarxia
WHERE adt = "M1234"

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-28 : 12:40:18

Hmm sorry, just noticed that you got almost these answers already in an other thread.
Lets see if there is anyone who actually knows anything about Access.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources