Combining Two SQL Queries

[robsmith]

Hi guys,



I have two queries in a database. One was made using the Query Design function and the other using SQL. I'm an SQL novice so please go easy!

I would like to combine the two queries so that all the fields in the first query are returned and the ranking and sorting in the second query are also returned (although I can live without the sorting if that complicates things).

I've tried several times to do it but can't get it to work.

Can anyone point me in the right direction?

Thanks

SELECT BallByBall.matchId, BallByBall.inningsNo, BallByBall.ballNumber, BallByBall.bowler, BallByBall.batsman, BallByBall.runs, BallByBall.byes, BallByBall.legByes, BallByBall.wides, BallByBall.noBalls, BallByBall.wicket, Lineups.bowlingStyle, Matches.League, Matches.convDate, ScorecardBatting.howDismissedFROM ScorecardBatting RIGHT JOIN (Matches RIGHT JOIN (Lineups RIGHT JOIN BallByBall ON (Lineups.playerId = BallByBall.bowler) AND (Lineups.matchId = BallByBall.matchId)) ON Matches.matchId = BallByBall.matchId) ON (ScorecardBatting.batsmanId = BallByBall.batsman) AND (ScorecardBatting.matchId = BallByBall.matchId);

SELECT BallByBall.matchId, BallByBall.inningsNo, BallByBall.ballNumber, BallByBall.bowler, BallByBall.batsman, BallByBall.runs, BallByBall.byes, BallByBall.legbyes, BallByBall.wides, BallByBall.noBalls, BallByBall.wicket, (   SELECT  COUNT(T2.ballNumber) + 1 
            FROM    BallByBall T2
            WHERE   T2.matchId = BallByBall.matchId
            AND     T2.batsman = BallByBall.batsman
            AND     T2.ballNumber < BallByBall.ballNumber
        ) AS Rank
FROM BallByBall
ORDER BY BallByBall.matchId, BallByBall.batsman, BallByBall.ballNumber;

[ranman256]

If you are a novice, don't use SQL, instead use queries.
if both queries are the same, put them in a UNION query.

Union Q3:

select * from Q1
Union
select * from Q2

then make Q4 to sort Q3.

[robsmith]

Hi ranman,

Thanks. I had to use SQL for the second query as I understand it's the only way to rank in Access queries.