Dapper SQL Join table into another-CodePudding

Newbie here. I have the following 2 tables. One for Albums:

And the other for Songs:

So ALL of these songs belong to the "Freedom" album. In an api call I'm using dapper to display all the albums which is fine.

[HttpGet("[action]/{id}")]
        public async Task<IActionResult> GetSingleAlbumViaDapper(int id)
        {
            var sql = "SELECT * FROM Albums WHERE Id = @AlbumId";
            var album = (await _dbDapper.QueryAsync<Album>(sql, new { AlbumId = id })).SingleOrDefault();
        }

And here's the result:

But I want to join the Songs relevant for this album. I've tried the following but it's not working, any ideas why?

[HttpGet("[action]/{id}")]
        public async Task<IActionResult> GetSingleAlbumViaDapper(int id)
        {
            var sql = "SELECT * FROM Albums WHERE Id = @AlbumId JOIN Songs in Songs on Albums.Songs";
            var album = (await _dbDapper.QueryAsync<Album>(sql, new { AlbumId = id })).SingleOrDefault();
        }

All it says is: Incorrect syntax near the keyword 'JOIN'.

CodePudding user response：

There is an error in how you're joining tables. First join, then filter (where-part)

The correct query would be something like that

SELECT * 
  FROM Albums a
  JOIN Songs s 
    ON s.AlbumId = a.Id
 WHERE a.Id = @AlbumId;

UPD. Since you're looking for songs only in the second query, from your table scema I see join is not needed here. You're already passing album.id to the query. Try this one and let me know the result

SELECT * 
  FROM Songs s 
 WHERE s.AlbumId = @AlbumId;

CodePudding user response：

Try to change your join query as follows:

SELECT a.* FROM Albums as a inner join Songs as s on s.AlbumId = a.Id WHERE a.Id = @AlbumId