Professional Web Applications Themes

SQL Question - Microsoft SQL / MS SQL Server

I'm an amateur at SQL statements, so I was wondering if anybody could help me out. I have a database that has a Tasks table and its primary key is taskId. The database also has an Updates table and Updates has its primary key updateId and a foreign key to Tasks called task. Basically, one task can have many updates. I'm looking for a query that will select all of the task's columns, plus the row count of Updates that are associated with that task. Any help would be greatly appreciated. Thanks, Dan...

  1. #1

    Default SQL Question

    I'm an amateur at SQL statements, so I was wondering if
    anybody could help me out. I have a database that has a
    Tasks table and its primary key is taskId. The database
    also has an Updates table and Updates has its primary key
    updateId and a foreign key to Tasks called task.
    Basically, one task can have many updates. I'm looking
    for a query that will select all of the task's columns,
    plus the row count of Updates that are associated with
    that task. Any help would be greatly appreciated.

    Thanks,

    Dan

    Dan Guest

  2. #2

    Default Re: SQL Question

    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    --CELKO--


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  3. #3

    Default Re: SQL Question

    Task
    taskid (PK)

    updates
    updateid (PK)
    taskid (FK)

    --Required query.

    select col1,col2, col3, (select count(*) from updates where taskid=a.taskid)
    'counts'
    from task a

    --
    -Vishal
    "Dan" <com> wrote in message
    news:01af01c36424$996e0370$gbl... 


    Vishal Guest

  4. #4

    Default Re: SQL Question

    Vishal,

    I am fairly new to SQL as well. I am always looking for the most
    efficient way to accomplish a task, since our SQL server is normally
    pretty busy. Essentially, I have two questions for you:

    1. Would a statement such as:

    SELECT a.col1, a.col2, a.col3, count(b.taskid) from task a inner join
    updates b on a.taskid = b.taskid
    group by a.col1, a.col2, a.col3

    accomplish the same thing as your query?


    2. Which one is more efficient? I do a lot of joins in my queries
    and would like to make sure that I am doing it the most efficient way
    possible.


    Thank you for your time.


    Random




    On Sat, 16 Aug 2003 11:59:18 -0700, "Vishal Parkar"
    <com> wrote:
     

    Random Guest

  5. #5

    Default Re: SQL Question

    > Given customers table: 

    select right (Name , len(Name) - charindex(' ', Name))
    from customers

    This will work correctly for items with exactly one space in the
    name.

    Linda


    lindawie Guest

  6. #6

    Default Re: SQL Question

    sorry for the delay.
    your query is correct as well.but use outer join to do this.

    SELECT a.col1, a.col2, a.col3, count(b.taskid) 'counts'
    from task a left outer join
    updates b on a.taskid = b.taskid
    group by a.col1, a.col2, a.col3

    --
    -Vishal
    "Random" <Randomnwhere> wrote in message
    news:com... [/ref]
    taskid=a.taskid) 
    >[/ref]


    Vishal Guest

  7. #7

    Default Re: SQL Question

    Cool, thanks for the response.

    Random

    On Mon, 18 Aug 2003 09:17:15 -0700, "Vishal Parkar"
    <com> wrote:
     

    Random Guest

  8. #8

    Default SQL Question

    I have a table with shows an user's name, and sign up data and time.

    What is the SQL query that shows the number of users that signed up on each
    day for the current month??

    Thanks,
    John




    John Guest

  9. #9

    Default SQL question

    All,
    I am having problems with a query. I've tried using outer join and
    intersection but with no avail. Any help would be appreciate it.
    Thanks.
    Joel.


    Sample Data
    Table A
    ID Amount
    1000 500
    1001 500
    1002 500
    1003 550


    Table B
    ID Completed_IND
    1000 Yes
    1002 No
    1003 Yes


    Desired Result
    All Rows from Table A if they do NOT exist on Table B and only Rows from
    Table B where Completed_IND = Yes

    i.e.
    Result Set
    ID Amount Completed_IND
    1000 500 Yes
    1001 500 Null
    1003 500 Yes




    Joel Guest

  10. #10

    Default Re: SQL question

    SELECT table1.id, table1.amount, table2.completed_ind
    FROM Table1 LEFT OUTER JOIN
    Table2 ON Table1.id = Table2.id
    WHERE (Table2.id IS NULL)
    UNION
    SELECT table1.id, table1.amount, table2.completed_ind
    FROM table1 INNER JOIN
    table2 ON table1.id = table2.id
    WHERE completed_ind = 1

    Vadim Rapp
    ----------------------------------------
    Vadim Rapp Consulting
    SQL, Access, VB Solutions
    847-685-9073
    www.vadimrapp.com

    JR> I am having problems with a query. I've tried
    JR> using outer join and intersection but with no
    JR> avail. Any help would be appreciate it. Thanks.
    JR> Joel.

    JR> Sample Data
    JR> Table A
    JR> ID Amount
    JR> 1000 500
    JR> 1001 500
    JR> 1002 500
    JR> 1003 550

    JR> Table B
    JR> ID Completed_IND
    JR> 1000 Yes
    JR> 1002 No
    JR> 1003 Yes

    JR> Desired Result
    JR> All Rows from Table A if they do NOT exist on
    JR> Table B and only Rows from Table B where
    JR> Completed_IND = Yes

    JR> i.e.
    JR> Result Set
    JR> ID Amount Completed_IND
    JR> 1000 500 Yes
    JR> 1001 500 Null
    JR> 1003 500 Yes



    Vadim Guest

  11. #11

    Default RE: SQL Question

    Give this a try

    /** -- Sample
    select count(<userid>), convert(varchar(25), <date>, 110)
    from <tablename>
    where datepart(mm, getdate()) = datepart(mm, <date>)
    and datepart(yyyy, getdate()) = datepart(yyyy, <date>)
    group by convert(varchar(25), <date>, 110)
    order by convert(varchar(25), <date>, 110)
    **/

    select count(orderid), convert(varchar(25), orderdate, 110)
    from northwind..orders
    where datepart(mm, getdate()) = datepart(mm, orderdate)
    group by convert(varchar(25), orderdate, 110)
    order by convert(varchar(25), orderdate, 110)
    /**
    note I hade to exclude the year constraint on the query here as the
    Northwind DB is a bit Dated
    **/

    Hope this helps

    Art Guest

  12. #12

    Default Re: SQL question

    Joel,

    Try this,

    select A.[ID],Amount,B.Completed_IND
    from TableA as A left join TableB B on A.[ID]= B.[ID]
    WHERE B.Completed_IND='yes' OR B.Completed_IND is null


    Praveen
    MCSD, MCDBA




    "Joel Russell" <com> wrote in message
    news:#phx.gbl... 


    praveen Guest

Similar Threads

  1. Newbie Question: Biz Card Template Question
    By Thomas_Porter@adobeforums.com in forum Adobe Indesign Windows
    Replies: 4
    Last Post: May 30th, 08:08 AM
  2. Replies: 9
    Last Post: April 27th, 04:44 AM
  3. Pen Tool Use Question. (Embarrassingly Newbie Question)
    By Bozo Schmozo in forum Macromedia Flash
    Replies: 0
    Last Post: November 12th, 10:00 PM
  4. regexp question + html::pr question on the side
    By boris in forum PERL Miscellaneous
    Replies: 4
    Last Post: September 27th, 02:24 AM
  5. newB question: related tables question
    By Blue man in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 04:13 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