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)