CODEWITHSUNDEEP
mysql
Ivo Renkema
Ivo Renkema

Reputation: 2198

mySQL subquery to see if

I need to know: For each album, is the album current?

I tried:

 "SELECT *, ".$subquery." AS current FROM tbl_album"

where $subquery is:

 SELECT EXISTS( SELECT 1 FROM tbl_icon2album JOIN tbl_album ON tbl_album.id=tbl_icon2album.albumID WHERE tbl_icon2album.b_current_in_timeproof = 0)

But this does not work. I get the message "1064 You have an error in your SQL syntax"

Interestingly, the subquery works fine as a stand-alone. Also, the main query works fine as follows:

SELECT *, 1 AS current FROM tbl_album

So if the sub-query works, and the main query works, why won't they work together?

post scriptum: A working solution for this problem was presented in this question.

Upvotes: 0

Views: 47

Answers (2)

Michael J. Anderson
Michael J. Anderson

Reputation: 479

You don't need a subquery here, try this:

SELECT  a.albumId,
        IF( SUM(IF(i.current<>1,1,0)) = 0,'Current','Not Current') as IsCurrent
FROM    tbl_album a
LEFT JOIN tbl_icon2album i ON i.albumId = a.albumId
GROUP BY a.albumId

Updated query to reflect if an album is current based on the sum of icon current statuses.

Updated: changed i.current<>0 to i.current<>1

Upvotes: 2

boodle
boodle

Reputation: 516

So I am taking a few guesses here as you didn't supply the structure of the tables. You are trying to select all albums, and state if they are "current" or not, where "current" is specified as them having one or more icons that are current:

SELECT 
    IF(
        tbl_icon2album.current != 0,
        "current",
        "not current"
    ) AS isAlbumCurrent,
    tbl_album.*
FROM tbl_album
LEFT JOIN tbl_icon2album ON 
    tbl_album.id=tbl_icon2album.albumID
GROUP BY tbl_album.id;

Upvotes: 0

Related Questions