COUNT () across 3 tables in a database

[waltonic]

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		

[AyKay47]

Use a JOIN along with COUNT().

Have you tried anything thus far?

[waltonic]

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

[AyKay47]

yah, that's an implicit join.