[Paul]

Trying to form a query to return bands with no media uploaded, including youtube videos, and can't work out how to 'join' the tables (if that is the correct terminology).

The following query obviously does not work, but might give an idea of what I'm trying to do -

SELECT
jamroom_band_info.band_id
FROM
jamroom_band_info
WHERE
jamroom_band_info.band_song_count = 0
AND
jamroom_band_info.band_video_count = 0
AND
COUNT (
SELECT
*
FROM
jamroom_paYouTubePlus
WHERE
jamroom_paYouTubePlus.youtube_band_id = jamroom_band_info.band_id
) = 0

Any Suggestions?
Thanks
Pa


_________________
Paul Asher
Jamroom Network Team Member: http://www.jamroom.net
Priority Support: http://www.jamroom.net/Support_Center

[smith.kyle]

Try this:

Code

SELECT         jamroom_band_info.band_id     FROM         jamroom_band_info     WHERE         jamroom_band_info.band_song_count = 0         AND jamroom_band_info.band_video_count = 0         AND COUNT(SELECT                 *             FROM                 jamroom_paYouTubePlus                     LEFT JOIN jamroom_paYouTubePlus                         ON jamroom_paYouTubePlus.youtube_band_id = jamroom_band_info.band_id) = 0

_________________
kyle[at]jamroom.net

Yes...that's a soda machine...

I get bored when no one's posting...

[Paul]

Thanks Kyle, but ....

Quote:

SQL query: Documentation SELECT jamroom_band_info.band_id FROM jamroom_band_info WHERE jamroom_band_info.band_song_count =0 AND jamroom_band_info.band_video_count =0 AND jamroom_band_info.band_quota =2 AND COUNT( SELECT * FROM jamroom_paYouTubePlus LEFT JOIN jamroom_paYouTubePlus ON jamroom_paYouTubePlus.youtube_band_id = jamroom_band_info.band_id ) =0 LIMIT 0 , 30 MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM jamroom_paYouTubePlus ' at line 9

_________________
Paul Asher
Jamroom Network Team Member: http://www.jamroom.net
Priority Support: http://www.jamroom.net/Support_Center

[smith.kyle]

bah, my bad:

Code

SELECT jamroom_band_info.band_id FROM jamroom_band_info WHERE jamroom_band_info.band_song_count =0 AND jamroom_band_info.band_video_count =0 AND jamroom_band_info.band_quota =2 AND COUNT( SELECT * FROM jamroom_paYouTubePlus LEFT JOIN jamroom_band_info ON jamroom_paYouTubePlus.youtube_band_id = jamroom_band_info.band_id ) =0 LIMIT 0 , 30

_________________
kyle[at]jamroom.net

Yes...that's a soda machine...

I get bored when no one's posting...

[Paul]

Still errors -

SELECT jamroom_band_info.band_id
FROM jamroom_band_info
WHERE jamroom_band_info.band_song_count =0
AND jamroom_band_info.band_video_count =0
AND jamroom_band_info.band_quota =2
AND COUNT(
SELECT *
FROM jamroom_paYouTubePlus
LEFT JOIN jamroom_band_info ON jamroom_paYouTubePlus.youtube_band_id = jamroom_band_info.band_id ) =0
LIMIT 0 , 30

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT *
FROM jamroom_paYouTubePlus
LEFT JOIN jamroom_band_info ON jamroom_paYou' at line 7


_________________
Paul Asher
Jamroom Network Team Member: http://www.jamroom.net
Priority Support: http://www.jamroom.net/Support_Center

[smith.kyle]

Argh...OK, last try - I think this is the ticket:

Code

SELECT         b.band_id     FROM         jamroom_band_info b     WHERE         b.band_photo_count = 0         AND b.band_event_count = 0         AND (             SELECT                     COUNT(*)                 FROM                     jamroom_paYouTubePlus p                         LEFT JOIN jamroom_band_info bb                             ON p.youtube_band_id = bb.band_id                 WHERE                     bb.band_id = b.band_id         ) = 0

I tried it on my dev system, except I used band_event_count and band_photo_count and did the subselect from the jrYouTube module and everything returned correctly.

Kyle


_________________
kyle[at]jamroom.net

Yes...that's a soda machine...

I get bored when no one's posting...

[Paul]

Thanks Kyle - That got it

There a couple of 'new to me' mysql techniques in there - the table aliasing and the LEFT JOIN ON bit. Have to get the book out and read up on them

Thanks again
Pa


_________________
Paul Asher
Jamroom Network Team Member: http://www.jamroom.net
Priority Support: http://www.jamroom.net/Support_Center

[smith.kyle]

Awesome! Glad to hear it Table aliasing is a nice little shorthand I try to use whenever possible. Take a look at the queries throughout Jamroom - Brian uses both table aliases and left joins a lot throughout them. It was figuring out that subquery that killed me If you're up for a challenge, you might try adding in a band_youtube_count column to the jamroom_band_info table and then update that value through your module so you don't need the subquery. You should be able to do it in your schema.php file with something like:

// Jamroom Band Info
$tbl = "band_youtube_count INT(3) UNSIGNED NOT NULL DEFAULT '0',";
dbVerifyTable($jamroom_db['band_info'],$tbl);


_________________
kyle[at]jamroom.net

Yes...that's a soda machine...

I get bored when no one's posting...

[Paul]

What a good idea - might try that. Thanks.


_________________
Paul Asher
Jamroom Network Team Member: http://www.jamroom.net
Priority Support: http://www.jamroom.net/Support_Center