waltonic Posted April 9, 2012 Share Posted April 9, 2012 php / MySQL Help - please... Hi, Here's my problem. [ system config: Apache 2.0.63 MySQL 5.1.44 PHP 5.2.13 & 5.3.2 ] Below is the (basic) schema for 3 related MySQL databases [more detailed info at the end of the message] show_info ------------- show_id - index show_date - date of show song_id - integer representing song_id (see table below) song_info ------------- song_id - index / id of song info artist_id - id of artist info (see table below) / etc. - lots of other info about the song in this table. duration / release date etc. artist_info ------------- artist_id - index / id of artist info is_welsh - boolean value: TRUE - artist is Welsh / FALSE - artist isn't Welsh / etc. - much more info about the artist I want to write a query that will COUNT () the amount of artist_info entries that are 'is_welsh' related to / contained in the show_info table. Any thoughts, please? Thank you for your time. Adam show_info Field Type Null Default Comments show_id int(10) No show_date date No song_id int(10) No pos int(11) No Indexes: Keyname Type Unique Packed Field Cardinality Collation Null Comment PRIMARY BTREE Yes No show_id 2840 A show_date BTREE No No show_date 66 A Space usage: Type Usage Data 45,456 B Index 62,464 B Overhead 16 B Effective 107,904 B Total 107,920 B Row Statistics: Statements Value Format static Rows 2,840 Row length ø 16 Row size ø 38 B Next Autoindex 3,345 Creation Apr 03, 2012 at 10:55 AM Last update Apr 08, 2012 at 02:51 PM Last check Apr 03, 2012 at 10:55 AM song_info Field Type Null Default Comments song_id int(10) No artist_id int(10) No title_id int(10) No composer_id int(10) No publisher_id int(10) No album_id int(10) No album_track int(10) No label_id int(10) No cat_no varchar(60) No format enum('album_track', 'bootleg', 'demo', 'download', 'guest', 'live_set', 'promo', 'single', 'session') No duration varchar(6) No intro varchar(4) No fade varchar(4) No explicit enum('yes', 'no') No no count int(10) No broadcast tinyint(3) No year smallint(5) No medium enum('file', 'CD', 'guest', 'vinyl') No release_date date No song_rating tinyint(3) No recommended tinyint(1) No share_url varchar(256) No Indexes: Keyname Type Unique Packed Field Cardinality Collation Null Comment PRIMARY BTREE Yes No song_id 2250 A song_rating BTREE No No song_rating 7 A Space usage: Type Usage Data 144,528 B Index 45,056 B Total 189,584 B Row Statistics: Statements Value Format dynamic Rows 2,250 Row length ø 64 Row size ø 84 B Next Autoindex 2,253 Creation Apr 03, 2012 at 10:55 AM Last update Apr 08, 2012 at 02:51 PM Last check Apr 03, 2012 at 10:55 AM artist_info Field Type Null Default Comments artist_id int(10) No artist_name varchar(256) No artist_location_id int(10) No artist_url varchar(256) No artist_twitter varchar(128) No artist_phone varchar(40) No is_welsh tinyint(1) No 1 Indexes: Keyname Type Unique Packed Field Cardinality Collation Null Comment PRIMARY BTREE Yes No artist_id 1151 A Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted April 9, 2012 Share Posted April 9, 2012 Use a JOIN along with COUNT(). Have you tried anything thus far? Quote Link to comment Share on other sites More sharing options...
waltonic Posted April 9, 2012 Author Share Posted April 9, 2012 I tripped myself up through rustiness. This works: SELECT COUNT(is_welsh) AS welsh_count FROM show_info, song_info, artist_info WHERE show_info.song_id=song_info.song_id AND song_info.artist_id=artist_info.artist_id AND artist_info.is_welsh=TRUE I had misused the COUNT () function. Many thanks x Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted April 9, 2012 Share Posted April 9, 2012 yah, that's an implicit join. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.