Professional Web Applications Themes

Trouble writing a query - MySQL

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 ...

  1. #1

    Default 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

    laredotornado@zipmail.com Guest

  2. #2

    Default Re: Trouble writing a query

    On 20 Apr, 17:27, "com"
    <com> wrote: 

    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)

    Captain Guest

  3. #3

    Default Re: Trouble writing a query

    On 23 Apr, 13:09, Captain Paralytic <com> wrote: 








    >
    > 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 -[/ref]

    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)



    Captain Guest

Similar Threads

  1. Help writing a query
    By alexp in forum MySQL
    Replies: 3
    Last Post: September 25th, 05:15 PM
  2. I Need help writing a query
    By charlese in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 17th, 03:22 PM
  3. Writing SQL query without subselect
    By Steven Stern in forum PHP Development
    Replies: 15
    Last Post: October 15th, 08:27 AM
  4. trouble with writing to file
    By Incognito in forum PERL Beginners
    Replies: 10
    Last Post: March 3rd, 03:25 PM
  5. Trouble writing to EventLog
    By Eric Johannsen in forum ASP.NET General
    Replies: 1
    Last Post: August 11th, 04:33 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139