Trying to find results if at least a value

[richrock]

I'm looking at a way to do a query where a music entry has at least one published value in another table.  My mysql knowledge is ropey at best  but learning all the time anyway.

 

Here's what I've tried :

 

 $q = "SELECT COUNT( a.id )
            FROM #__music_artists a
            LEFT JOIN #__music_albums al ON a.id = al.artist_id
            WHERE a.surname LIKE '" . $id . "%'
            AND 1 IN (al.published)";

 

Which doesn't work.  Basically my problem is is that each artist may have published and unpublished albums, so I need to check the albums table to see if there is at least one published album amongst what could be loads of unpublished albums, or the opposite. 

 

I just need the published to return true, and let the artist do the counting for calculating a table display.

 

Any ideas/help on this?  I looked at LEAST() but that did nothing as well.

 

[fenway]

Sorry, I don't follow.

[bluejay002]

I am not sure what you are getting at but are you trying to fetch data that has a matching entry? If that's the case, don't use LEFT JOIN, use INNER JOIN instead.

 

bluejay,