Trouble writing a query

Hi,

I'm using MySQL 5.0. I have three tables ...

MODULES (lists available content for the page, e.g. Gmail, ESPN, CNN)
----------------
ID
NAME

TABS (lists names of groups of modules)
---------
ID
NAME

TAB_MODULES (lists modules that go in each group)
-----------------------
ID
MODULE_ID (foreign key to MODULES.ID column)
TAB_ID (foreign key to TABS.ID column)

I want to write a query that tells me whether a module is displayed on
a tab, given a tab's ID. The result set should always contain the
same number of rows as SELECT COUNT(*) FROM MODULES. I would like it
to contain

MODULE_NAME (name of module)
SELECTED (either 1 or 0)

How do I do this?

Thanks, - Dave

On 20 Apr, 17:27, "laredotorn...@zipmail.com"
<laredotorn...@zipmail.com> wrote:
> Hi,
>
> I'm using MySQL 5.0. I have three tables ...
>
> MODULES (lists available content for the page, e.g. Gmail, ESPN, CNN)
> ----------------
> ID
> NAME
>
> TABS (lists names of groups of modules)
> ---------
> ID
> NAME
>
> TAB_MODULES (lists modules that go in each group)
> -----------------------
> ID
> MODULE_ID (foreign key to MODULES.ID column)
> TAB_ID (foreign key to TABS.ID column)
>
> I want to write a query that tells me whether a module is displayed on
> a tab, given a tab's ID. The result set should always contain the
> same number of rows as SELECT COUNT(*) FROM MODULES. I would like it
> to contain
>
> MODULE_NAME (name of module)
> SELECTED (either 1 or 0)
>
> How do I do this?
>
> Thanks, - Dave

Well I think you want:

SELECT
`m`.`NAME` `MODULE_NAME`,
IF(`t`.`MODULE_ID`,1,0)
FROM `MODULES` `m`
LEFT JOIN `TAB_MODULES` `t` ON `m`.`ID` = `t`.`MODULE_ID` AND
`t`.`TAB_ID` = (the id of the tab you are intrrested in)

On 23 Apr, 13:09, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 20 Apr, 17:27, "laredotorn...@zipmail.com"
>
>
>
>
>
> <laredotorn...@zipmail.com> wrote:
> > Hi,
>
> > I'm using MySQL 5.0. I have three tables ...
>
> > MODULES (lists available content for the page, e.g. Gmail, ESPN, CNN)
> > ----------------
> > ID
> > NAME
>
> > TABS (lists names of groups of modules)
> > ---------
> > ID
> > NAME
>
> >TAB_MODULES(lists modules that go in each group)
> > -----------------------
> > ID
> > MODULE_ID (foreign key to MODULES.ID column)
> > TAB_ID (foreign key to TABS.ID column)
>
> > I want to write a query that tells me whether a module is displayed on
> > a tab, given a tab's ID. The result set should always contain the
> > same number of rows as SELECT COUNT(*) FROM MODULES. I would like it
> > to contain
>
> > MODULE_NAME (name of module)
> > SELECTED (either 1 or 0)
>
> > How do I do this?
>
> > Thanks, - Dave
>
> Well I think you want:
>
> SELECT
> `m`.`NAME` `MODULE_NAME`,
> IF(`t`.`MODULE_ID`,1,0)
> FROM `MODULES` `m`
> LEFT JOIN `TAB_MODULES` `t` ON `m`.`ID` = `t`.`MODULE_ID` AND
> `t`.`TAB_ID` = (the id of the tab you are intrrested in)- Hide quoted text -
>
> - Show quoted text -

Forgot a bit:
SELECT
`m`.`NAME` `MODULE_NAME`,
IF(`t`.`MODULE_ID`,1,0) `SELECTED`
FROM `MODULES` `m`
LEFT JOIN `TAB_MODULES` `t` ON `m`.`ID` = `t`.`MODULE_ID` AND
`t`.`TAB_ID` = (the id of the tab you are interested in)