Professional Web Applications Themes

How to write this query... please help! - Microsoft SQL / MS SQL Server

Hi all, I can't find my way out of this little problem and my deadline is coming fast... So if someone could help me out... I have 2 tables, say: tblCategories(category_id, category_name) and tblItems(item_id, category_id, item_name) in a one-to-many relationship: each item in tblItems has 1 category from tblCategories. My problem is that I want, in a query run against the whole tblCategories table, to add a field "InUse", where a boolean value, computed at run-time, would indicate if any given category is used by at least 1 item in the tblItems table. For each row of tblCategories, this field ...

  1. #1

    Default How to write this query... please help!

    Hi all,

    I can't find my way out of this little problem and my deadline is coming
    fast... So if someone could help me out...

    I have 2 tables, say:

    tblCategories(category_id, category_name)

    and

    tblItems(item_id, category_id, item_name)

    in a one-to-many relationship: each item in tblItems has 1 category from
    tblCategories.

    My problem is that I want, in a query run against the whole tblCategories
    table, to add a field "InUse", where a boolean value, computed at run-time,
    would indicate if any given category is used by at least 1 item in the
    tblItems table. For each row of tblCategories, this field would be True if
    the category_id exists in tblItems, and False otherwise.

    I need an output like this:

    category1, TRUE
    category2, TRUE
    category3, FALSE

    etc.


    Seems quite simple, but I 'm not a SQL guru and I'm running out of time...
    So any help would be appreciated.

    Thanks in advance!

    Paul Dussault, MCP


    Paul Dussault Guest

  2. #2

    Default Re: How to write this query... please help!

    Try this:
    SELECT category_name,
    CASE WHEN EXISTS (SELECT *
    FROM item
    WHERE category_id = category.cateogry_id)
    THEN 'True'
    ELSE 'False'
    END
    FROM category

    The syntax maybe a little off as I have not tested it, but it should give
    you the idea.


    "Paul Dussault" <pauldusshotmail.com> wrote in message
    news:eGKVsXBRDHA.2176TK2MSFTNGP12.phx.gbl...
    > Hi all,
    >
    > I can't find my way out of this little problem and my deadline is coming
    > fast... So if someone could help me out...
    >
    > I have 2 tables, say:
    >
    > tblCategories(category_id, category_name)
    >
    > and
    >
    > tblItems(item_id, category_id, item_name)
    >
    > in a one-to-many relationship: each item in tblItems has 1 category from
    > tblCategories.
    >
    > My problem is that I want, in a query run against the whole tblCategories
    > table, to add a field "InUse", where a boolean value, computed at
    run-time,
    > would indicate if any given category is used by at least 1 item in the
    > tblItems table. For each row of tblCategories, this field would be True if
    > the category_id exists in tblItems, and False otherwise.
    >
    > I need an output like this:
    >
    > category1, TRUE
    > category2, TRUE
    > category3, FALSE
    >
    > etc.
    >
    >
    > Seems quite simple, but I 'm not a SQL guru and I'm running out of time...
    > So any help would be appreciated.
    >
    > Thanks in advance!
    >
    > Paul Dussault, MCP
    >
    >

    David Frommer Guest

  3. #3

    Default Re: How to write this query... please help!

    Thanks David,

    It worked perfectly.

    CASE WHEN EXISTS... I'll remember that one -

    Thanks again!

    Paul Dussault, MCP


    Paul Dussault Guest

Similar Threads

  1. how write query to get hourly rate based on multiconditions
    By shaoguan in forum Coldfusion Database Access
    Replies: 0
    Last Post: August 9th, 07:07 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  4. Replies: 2
    Last Post: June 1st, 08:38 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

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