Retrieving Data from Multiple Tables

I have four tables - news, events, photos and team_members which
contain different data. Each of the tables has a field called 'draft'
allowing users to save an entry as a draft rather than publish it
right away (if it is saved as draft 'yes' is entered into the draft
field). What i want to do is do a COUNT of all items which are saved
as drafts.

So what i need to do is get the data for all four tables and do a
COUNT of rows, but how can i get the data from all four tables. It
doesn't seem to be a JOIN function as that links two corresponding
values, i just want to grab all data from the four tables WHERE draft
= 'yes'.


I have tried:

SELECT local_news.draft, events.draft, photos.draft,
team_members.draft
FROM local_news, events, photos, team_members
WHERE local_news.draft = 'yes' AND events.draft = 'yes' AND
photos.draft = 'yes' AND team_members.draft = 'yes'

but that doesn't work.

"chris_huh" <chris.huh@gmail.com> wrote in message
news:1175076229.625049.215320@o5g2000hsb.googlegro ups.com...
>I have four tables - news, events, photos and team_members which
> contain different data. Each of the tables has a field called 'draft'
> allowing users to save an entry as a draft rather than publish it
> right away (if it is saved as draft 'yes' is entered into the draft
> field). What i want to do is do a COUNT of all items which are saved
> as drafts.
>
> So what i need to do is get the data for all four tables and do a
> COUNT of rows, but how can i get the data from all four tables. It
> doesn't seem to be a JOIN function as that links two corresponding
> values, i just want to grab all data from the four tables WHERE draft
> = 'yes'.
>
>
> I have tried:
>
> SELECT local_news.draft, events.draft, photos.draft,
> team_members.draft
> FROM local_news, events, photos, team_members
> WHERE local_news.draft = 'yes' AND events.draft = 'yes' AND
> photos.draft = 'yes' AND team_members.draft = 'yes'
>
> but that doesn't work.
>


I am not an expert with MySQL ... but this worked in SQL ...

SELECT
(SELECT count(draft) from local_news WHERE draft = 'yes') AS
DRAFT_LOCAL_NEWS,
(SELECT count(draft) from events WHERE draft = 'yes') AS DRAFT_EVENTS,
(SELECT count(draft) from photos WHERE draft = 'yes') AS DRAFT_PHOTOS
(SELECT count(draft) from team_members WHERE draft = 'yes') AS
DRAFT_TEAM_MEMBERS

Incidentally, I would have had the draft column as a boolean/bit field and
use 1/0 rather than entering the text for "yes" and "no"

Sean