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 Link to comment https://forums.phpfreaks.com/topic/260610-count-across-3-tables-in-a-database/ 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? Link to comment https://forums.phpfreaks.com/topic/260610-count-across-3-tables-in-a-database/#findComment-1335641 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 Link to comment https://forums.phpfreaks.com/topic/260610-count-across-3-tables-in-a-database/#findComment-1335648 Share on other sites More sharing options...
AyKay47 Posted April 9, 2012 Share Posted April 9, 2012 yah, that's an implicit join. Link to comment https://forums.phpfreaks.com/topic/260610-count-across-3-tables-in-a-database/#findComment-1335656 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.