Professional Web Applications Themes

Query Question - Coldfusion - Advanced Techniques

I apologize if this gets posted twice. A lot of my messages are not getting through I could use some help with a query. I have a table called ProjectStatus which contains the Status history of a project. I want my query to output results in the following format: Status MonthName Total MonthNumber Complete Feb-2003 71 2 New Feb-2003 98 2 Outstanding Feb-2003 22 2 Complete Mar-2003 41 3 New Mar-2003 63 3 Outstanding Mar-2003 35 3 Complete Apr-2003 252 4 New Apr-2003 306 4 These results are not quite right but they give you an idea. The complete and ...

  1. #1

    Default Query Question

    I apologize if this gets posted twice. A lot of my messages are not getting
    through


    I could use some help with a query. I have a table called ProjectStatus
    which contains the Status history of a project. I want my query to output
    results in the following format:

    Status MonthName Total MonthNumber
    Complete Feb-2003 71 2
    New Feb-2003 98 2
    Outstanding Feb-2003 22 2
    Complete Mar-2003 41 3
    New Mar-2003 63 3
    Outstanding Mar-2003 35 3
    Complete Apr-2003 252 4
    New Apr-2003 306 4

    These results are not quite right but they give you an idea. The complete
    and new totals are right but I need the outstanding status to be use this
    formula:

    (Last month's outstanding) + (this month's new) - (this month's completed) =
    (this months outstanding)

    So for example,

    The outstanding for March should be: 22 + 63 - 41 = 44 (I currently get 35.
    Hence this post )

    In the ProjectStatus table, New is StatusID of 1 and Complete is 4

    Any help is greatly appreciated,

    Jason


    CREATE TABLE [dbo].[ProjectStatus] (
    [ProjectStatusID] [int] IDENTITY (1, 1) NOT NULL ,
    [ProjectID] [numeric](10, 0) NOT NULL ,
    [StatusID] [int] NOT NULL ,
    [StatusDate] [datetime] NOT NULL
    ) ON [PRIMARY]


    Jason MacKenzie Guest

  2. #2

    Default Re: Query Question

    I should mention that I'm using a 3 queries here with the UNION clause. One
    for New, Complete, and Outstanding. I only need help witht the Outstanding
    one - unless of course there's a better way to do the whole thing.

    Thanks,

    Jason


    Jason MacKenzie Guest

  3. #3

    Default Re: Query Question

    select StatusID
    , convert(varchar(7),statusdate,102) as yyyymm -- maybe use
    datename()function if you want names be carefull with orderby
    , count(*) as total
    , min(datepart(mm,statusdate) as monthnr)
    from projectstatus
    group by statusid, convert(varchar(7),statusdate,102)
    order by yyyymm desc


    jobi
    CREATE TABLE [dbo].[ProjectStatus] (
    > [ProjectStatusID] [int] IDENTITY (1, 1) NOT NULL ,
    > [ProjectID] [numeric](10, 0) NOT NULL ,
    > [StatusID] [int] NOT NULL ,
    > [StatusDate] [datetime] NOT NULL

    "Jason MacKenzie" <jmackenzie_nospamformet.com> wrote in message
    news:Oz4tIcjQDHA.3144tk2msftngp13.phx.gbl...
    > I apologize if this gets posted twice. A lot of my messages are not
    getting
    > through
    >
    >
    > I could use some help with a query. I have a table called ProjectStatus
    > which contains the Status history of a project. I want my query to output
    > results in the following format:
    >
    > Status MonthName Total MonthNumber
    > Complete Feb-2003 71 2
    > New Feb-2003 98 2
    > Outstanding Feb-2003 22 2
    > Complete Mar-2003 41 3
    > New Mar-2003 63 3
    > Outstanding Mar-2003 35 3
    > Complete Apr-2003 252 4
    > New Apr-2003 306 4
    >
    > These results are not quite right but they give you an idea. The complete
    > and new totals are right but I need the outstanding status to be use this
    > formula:
    >
    > (Last month's outstanding) + (this month's new) - (this month's completed)
    =
    > (this months outstanding)
    >
    > So for example,
    >
    > The outstanding for March should be: 22 + 63 - 41 = 44 (I currently get
    35.
    > Hence this post )
    >
    > In the ProjectStatus table, New is StatusID of 1 and Complete is 4
    >
    > Any help is greatly appreciated,
    >
    > Jason
    >
    >
    > CREATE TABLE [dbo].[ProjectStatus] (
    > [ProjectStatusID] [int] IDENTITY (1, 1) NOT NULL ,
    > [ProjectID] [numeric](10, 0) NOT NULL ,
    > [StatusID] [int] NOT NULL ,
    > [StatusDate] [datetime] NOT NULL
    > ) ON [PRIMARY]
    >
    >

    jobi Guest

  4. #4

    Default Re: Query Question

    Hi Jobi,

    Thanks for the response. I'm not sure that that answers my question though.

    All I need is this:

    (Last month's outstanding) + (this month's new) - (this month's completed)
    = (this months outstanding)

    I consider a completed project to be one who's record with the
    max(projectstatusid) has a statusid of 4. In other words, the last status is
    4.

    If this is my current result set with the Outstanding numbers being
    incorrect.

    Status MonthName Total MonthNumber
    Complete Feb-2003 71 2
    New Feb-2003 98 2
    Outstanding Feb-2003 22 2
    Complete Mar-2003 41 3
    New Mar-2003 63 3
    Outstanding Mar-2003 35 3


    What I need from this query is:

    Status MonthName Total MonthNumber
    Outstanding Feb-2003 27 2
    Outstanding Mar-2003 49 3

    The 49 would be 27 + 63-41




    "jobi" <jobireply2.group> wrote in message
    news:be43a4$218$1reader08.wxs.nl...
    > select StatusID
    > , convert(varchar(7),statusdate,102) as yyyymm -- maybe use
    > datename()function if you want names be carefull with orderby
    > , count(*) as total
    > , min(datepart(mm,statusdate) as monthnr)
    > from projectstatus
    > group by statusid, convert(varchar(7),statusdate,102)
    > order by yyyymm desc
    >
    >
    > jobi
    > CREATE TABLE [dbo].[ProjectStatus] (
    > > [ProjectStatusID] [int] IDENTITY (1, 1) NOT NULL ,
    > > [ProjectID] [numeric](10, 0) NOT NULL ,
    > > [StatusID] [int] NOT NULL ,
    > > [StatusDate] [datetime] NOT NULL
    >
    >
    > "Jason MacKenzie" <jmackenzie_nospamformet.com> wrote in message
    > news:Oz4tIcjQDHA.3144tk2msftngp13.phx.gbl...
    > > I apologize if this gets posted twice. A lot of my messages are not
    > getting
    > > through
    > >
    > >
    > > I could use some help with a query. I have a table called ProjectStatus
    > > which contains the Status history of a project. I want my query to
    output
    > > results in the following format:
    > >
    > > Status MonthName Total MonthNumber
    > > Complete Feb-2003 71 2
    > > New Feb-2003 98 2
    > > Outstanding Feb-2003 22 2
    > > Complete Mar-2003 41 3
    > > New Mar-2003 63 3
    > > Outstanding Mar-2003 35 3
    > > Complete Apr-2003 252 4
    > > New Apr-2003 306 4
    > >
    > > These results are not quite right but they give you an idea. The
    complete
    > > and new totals are right but I need the outstanding status to be use
    this
    > > formula:
    > >
    > > (Last month's outstanding) + (this month's new) - (this month's
    completed)
    > =
    > > (this months outstanding)
    > >
    > > So for example,
    > >
    > > The outstanding for March should be: 22 + 63 - 41 = 44 (I currently get
    > 35.
    > > Hence this post )
    > >
    > > In the ProjectStatus table, New is StatusID of 1 and Complete is 4
    > >
    > > Any help is greatly appreciated,
    > >
    > > Jason
    > >
    > >
    > > CREATE TABLE [dbo].[ProjectStatus] (
    > > [ProjectStatusID] [int] IDENTITY (1, 1) NOT NULL ,
    > > [ProjectID] [numeric](10, 0) NOT NULL ,
    > > [StatusID] [int] NOT NULL ,
    > > [StatusDate] [datetime] NOT NULL
    > > ) ON [PRIMARY]
    > >
    > >
    >
    >

    Jason MacKenzie Guest

  5. #5

    Default Re: Query Question

    Maybe looking for "running totals" might put you on the right track.

    In this query I assume that the three statusses are known data.

    select 'NewOutstandinig' as Status
    , convert(datetime,yyyymm,102) as dtYearMonth
    , total_new + total_outstanding - total_complete as Total
    , monthnr as MonthNumber
    from (
    select
    , convert(int,statusdate,102) as yyyymm
    , case StatusID
    when 'new' then 1
    else 0
    end as total_new
    , case StatusID
    when 'outstanding' then 1
    else 0
    end as total_outstanding
    , case StatusID
    when 'complete' then 1
    else 0
    end as total_complete
    , min(datepart(mm,statusdate)) as monthnr
    from projectstatus
    group by convert(int,statusdate,102)

    ) t1

    order by dtYearMonth desc


    jobi

    "Jason MacKenzie" <jmackenzie_nospamformet.com> wrote in message
    news:uJZ4PqjQDHA.4024tk2msftngp13.phx.gbl...
    > Hi Jobi,
    >
    > Thanks for the response. I'm not sure that that answers my question
    though.
    >
    > All I need is this:
    >
    > (Last month's outstanding) + (this month's new) - (this month's
    completed)
    > = (this months outstanding)
    >
    > I consider a completed project to be one who's record with the
    > max(projectstatusid) has a statusid of 4. In other words, the last status
    is
    > 4.
    >
    > If this is my current result set with the Outstanding numbers being
    > incorrect.
    >
    > Status MonthName Total MonthNumber
    > Complete Feb-2003 71 2
    > New Feb-2003 98 2
    > Outstanding Feb-2003 22 2
    > Complete Mar-2003 41 3
    > New Mar-2003 63 3
    > Outstanding Mar-2003 35 3
    >
    >
    > What I need from this query is:
    >
    > Status MonthName Total MonthNumber
    > Outstanding Feb-2003 27 2
    > Outstanding Mar-2003 49 3
    >
    > The 49 would be 27 + 63-41
    >
    >
    >
    >
    > "jobi" <jobireply2.group> wrote in message
    > news:be43a4$218$1reader08.wxs.nl...
    > > select StatusID
    > > , convert(varchar(7),statusdate,102) as yyyymm -- maybe use
    > > datename()function if you want names be carefull with orderby
    > > , count(*) as total
    > > , min(datepart(mm,statusdate) as monthnr)
    > > from projectstatus
    > > group by statusid, convert(varchar(7),statusdate,102)
    > > order by yyyymm desc
    > >
    > >
    > > jobi
    > > CREATE TABLE [dbo].[ProjectStatus] (
    > > > [ProjectStatusID] [int] IDENTITY (1, 1) NOT NULL ,
    > > > [ProjectID] [numeric](10, 0) NOT NULL ,
    > > > [StatusID] [int] NOT NULL ,
    > > > [StatusDate] [datetime] NOT NULL
    > >
    > >
    > > "Jason MacKenzie" <jmackenzie_nospamformet.com> wrote in message
    > > news:Oz4tIcjQDHA.3144tk2msftngp13.phx.gbl...
    > > > I apologize if this gets posted twice. A lot of my messages are not
    > > getting
    > > > through
    > > >
    > > >
    > > > I could use some help with a query. I have a table called
    ProjectStatus
    > > > which contains the Status history of a project. I want my query to
    > output
    > > > results in the following format:
    > > >
    > > > Status MonthName Total MonthNumber
    > > > Complete Feb-2003 71 2
    > > > New Feb-2003 98 2
    > > > Outstanding Feb-2003 22 2
    > > > Complete Mar-2003 41 3
    > > > New Mar-2003 63 3
    > > > Outstanding Mar-2003 35 3
    > > > Complete Apr-2003 252 4
    > > > New Apr-2003 306 4
    > > >
    > > > These results are not quite right but they give you an idea. The
    > complete
    > > > and new totals are right but I need the outstanding status to be use
    > this
    > > > formula:
    > > >
    > > > (Last month's outstanding) + (this month's new) - (this month's
    > completed)
    > > =
    > > > (this months outstanding)
    > > >
    > > > So for example,
    > > >
    > > > The outstanding for March should be: 22 + 63 - 41 = 44 (I currently
    get
    > > 35.
    > > > Hence this post )
    > > >
    > > > In the ProjectStatus table, New is StatusID of 1 and Complete is 4
    > > >
    > > > Any help is greatly appreciated,
    > > >
    > > > Jason
    > > >
    > > >
    > > > CREATE TABLE [dbo].[ProjectStatus] (
    > > > [ProjectStatusID] [int] IDENTITY (1, 1) NOT NULL ,
    > > > [ProjectID] [numeric](10, 0) NOT NULL ,
    > > > [StatusID] [int] NOT NULL ,
    > > > [StatusDate] [datetime] NOT NULL
    > > > ) ON [PRIMARY]
    > > >
    > > >
    > >
    > >
    >
    >

    jobi Guest

  6. #6

    Default Query question

    Hai,
    I have a table with 2 cols numid, subid both int
    numid subid
    1 2
    2 1
    3 1
    1 3
    2 4
    3 8
    1 5
    2 5
    3 9

    Now i like to make a query in dbaccess wich would give my this result:
    1 <--numid
    2 <\
    3 < --all the subid's belonging to numid 1
    5 </
    2 <--numid
    1 <\
    4 < --all the subid's belonging to numid 2
    5 </
    3 <--numid
    1 <\
    8 < --all the subid's belonging to numid 3
    9 </

    I tried it with union, i tried it with the set datatype via an temp
    table, but i can't get it working.
    Any ideas?

    Ronald
    Bickel Guest

  7. #7

    Default Re: Query question

    Check out cdi on groups.google.com, this has been covered in the
    last couple of days

    Bickel wrote:
    >
    > Hai,
    > I have a table with 2 cols numid, subid both int
    > numid subid
    > 1 2
    > 2 1
    > 3 1
    > 1 3
    > 2 4
    > 3 8
    > 1 5
    > 2 5
    > 3 9
    >
    > Now i like to make a query in dbaccess wich would give my this result:
    > 1 <--numid
    > 2 <\
    > 3 < --all the subid's belonging to numid 1
    > 5 </
    > 2 <--numid
    > 1 <\
    > 4 < --all the subid's belonging to numid 2
    > 5 </
    > 3 <--numid
    > 1 <\
    > 8 < --all the subid's belonging to numid 3
    > 9 </
    >
    > I tried it with union, i tried it with the set datatype via an temp
    > table, but i can't get it working.
    > Any ideas?
    >
    > Ronald
    --
    Paul Watson #
    Oninit Ltd # Growing old is mandatory
    Tel: +44 1436 672201 # Growing up is optional
    Fax: +44 1436 678693 #
    Mob: +44 7818 003457 #
    [url]www.oninit.com[/url] #
    Paul Watson Guest

  8. #8

    Default Re: Query question

    Bickel wrote:
    > I have a table with 2 cols numid, subid both int
    > numid subid
    > 1 2
    > 2 1
    > 3 1
    > 1 3
    > 2 4
    > 3 8
    > 1 5
    > 2 5
    > 3 9
    >
    > Now i like to make a query in dbaccess wich would give my this result:
    > 1 <--numid
    > 2 <\
    > 3 < --all the subid's belonging to numid 1
    > 5 </
    > 2 <--numid
    > 1 <\
    > 4 < --all the subid's belonging to numid 2
    > 5 </
    > 3 <--numid
    > 1 <\
    > 8 < --all the subid's belonging to numid 3
    > 9 </
    >
    > I tried it with union, i tried it with the set datatype via an temp
    > table, but i can't get it working.
    > Any ideas?
    SELECT DISTINCT numid AS numid, numid AS subid FROM table
    UNION
    SELECT DISTINCT numid AS numid, subid AS subid FROM table
    INTO TEMP t;

    SELECT subid FROM t ORDER BY numid, subid; -- IDS 9.40 only!

    Everywhere else, you'd have to select numid as well as subid and
    ignore the value.

    --
    Jonathan Leffler #include <disclaimer.h>
    Email: [email]jlefflerearthlink.net[/email], [email]jlefflerus.ibm.com[/email]
    Guardian of DBD::Informix v2003.04 -- [url]http://dbi.perl.org/[/url]

    Jonathan Leffler Guest

  9. #9

    Default Re: Query question

    Jonathan Leffler <jlefflerearthlink.net> wrote in message news:<7NEsb.11218> >
    >
    > SELECT DISTINCT numid AS numid, numid AS subid FROM table
    > UNION
    > SELECT DISTINCT numid AS numid, subid AS subid FROM table
    > INTO TEMP t;
    >
    > SELECT subid FROM t ORDER BY numid, subid; -- IDS 9.40 only!
    >
    > Everywhere else, you'd have to select numid as well as subid and
    > ignore the value.
    Sorry, but this doesn't work. i get a result of only 11 rows where i
    expect 12. Also on 9.40.
    As you can see here....numid 2 and 3 show 4 times, instead of 3 time.
    Also ignoring the numid (or working with 9.40) are no options.
    So i guess it can only be done with spl..?
    numid subid
    1 1
    1 3
    1 5
    2 1
    2 2
    2 4
    2 5
    3 1
    3 3
    3 8
    3 9
    Bickel Guest

  10. #10

    Default Re: Query question


    [email]bickelzonnet.nl[/email] (Bickel) wrote:
    >Jonathan Leffler <jlefflerearthlink.net> wrote in message
    >news:<7NEsb.11218> >
    > >
    > > SELECT DISTINCT numid AS numid, numid AS subid FROM table
    > > UNION
    > > SELECT DISTINCT numid AS numid, subid AS subid FROM table
    > > INTO TEMP t;
    > >
    > > SELECT subid FROM t ORDER BY numid, subid; -- IDS 9.40 only!
    > >
    > > Everywhere else, you'd have to select numid as well as subid and
    > > ignore the value.
    >
    >Sorry, but this doesn't work. i get a result of only 11 rows where i
    >expect 12. Also on 9.40.
    >As you can see here....numid 2 and 3 show 4 times, instead of 3 time.
    >Also ignoring the numid (or working with 9.40) are no options.
    >So i guess it can only be done with spl..?
    > numid subid
    > 1 1
    > 1 3
    > 1 5
    > 2 1
    > 2 2
    > 2 4
    > 2 5
    > 3 1
    > 3 3
    > 3 8
    > 3 9
    I was able to get the results you requested, but it isn't pretty. The test
    that I ran is as follows:

    create temp table testit
    (numid integer,
    subid integer) with no log;

    insert into testit values (1, 2);
    insert into testit values (2, 1);
    insert into testit values (3, 1);
    insert into testit values (1, 3);
    insert into testit values (2, 4);
    insert into testit values (3, 8);
    insert into testit values (1, 5);
    insert into testit values (2, 5);
    insert into testit values (3, 9);

    create temp table worktable
    (col1 integer,
    col2 integer) with no log;

    insert into worktable
    select unique numid * 10000 + numid, numid from testit;

    insert into worktable
    select numid * 10000 + subid * 100, subid from testit;

    select col1 c1, col2 c2 from worktable order by col1 into temp showtable;

    select c2 from showtable;

    drop table worktable;
    drop table showtable;
    drop table testit;


    With the results as in your original request:

    1
    2
    3
    5
    2
    1
    4
    5
    3
    1
    8
    9


    I don't have 9.40 available for testing, so had to go another way. I used
    two temp tables; the first holds a modified value to allow proper sorting,
    the second column is the item that is to print. (It is why I asked for the
    size of the actual values in an earlier email to you. I am guessing at a
    size that will work for the sort value (e.g. 10000 and 100).) The second
    temp table is simply built for the final output. I used an 'order by' when
    populating the second temp table, but... I would not trust that the sort
    order will always be maintained. If this is a one-time operation for you,
    you might want to give it a shot (and cross your fingers that the sort order
    is maintained), otherwise the SPL may be the way to go.

    As I said, this isn't pretty, but it did work. I'm not clear if you have
    9.40 yourself, but if so, you could try building the first temp table as I
    did and select from it with the 'order by' as Jonathan suggested. I think
    you are going to have to go just a little further than he suggested for the
    sort. Using his example but selecting both fields from temp table t, the
    sort order did not appear as in your original request. I did, however, get
    all twelve results.

    I hope there is something here that you can use.

    --
    June Hunt

    __________________________________________________ _______________
    MSN Shopping upgraded for the holidays! Snappier product search...
    [url]http://shopping.msn.com[/url]

    sending to informix-list
    June Hunt Guest

  11. #11

    Default Re: Query question

    Bickel wrote:
    > Jonathan Leffler <jlefflerearthlink.net> wrote:
    >>SELECT DISTINCT numid AS numid, numid AS subid FROM table
    >>UNION
    >>SELECT DISTINCT numid AS numid, subid AS subid FROM table
    >>INTO TEMP t;
    >>
    >>SELECT subid FROM t ORDER BY numid, subid; -- IDS 9.40 only!
    >>
    >>Everywhere else, you'd have to select numid as well as subid and
    >>ignore the value.
    >
    >
    > Sorry, but this doesn't work. i get a result of only 11 rows where i
    > expect 12. Also on 9.40.
    > As you can see here....numid 2 and 3 show 4 times, instead of 3 time.
    > Also ignoring the numid (or working with 9.40) are no options.
    > So i guess it can only be done with spl..?
    > numid subid
    > 1 1
    > 1 3
    > 1 5
    > 2 1
    > 2 2
    > 2 4
    > 2 5
    > 3 1
    > 3 3
    > 3 8
    > 3 9
    OK - I see what I missed. I dunno whether it's worth rescuing what I
    suggested, but you can add another column to be ignored except in the
    sort phase:

    SELECT DISTINCT numid AS numid, numid AS subid, 1 AS key FROM table
    UNION
    SELECT DISTINCT numid AS numid, subid AS subid, 2 AS key FROM table
    INTO TEMP t;

    SELECT subid FROM t ORDER BY numid, key, subid; -- IDS 9.40 only!

    That makes sure that the 'key' columns for the numid appear before the
    other values. I think that answers your query.

    --
    Jonathan Leffler #include <disclaimer.h>
    Email: [email]jlefflerearthlink.net[/email], [email]jlefflerus.ibm.com[/email]
    Guardian of DBD::Informix v2003.04 -- [url]http://dbi.perl.org/[/url]

    Jonathan Leffler Guest

  12. #12

    Default Query Question

    I've got two tables, 'associates' and 'pics'. One guy in associates will often
    have multiple entries in the pics table. I display each associate along with
    his pics. This works great using a join in the query and grouped output on the
    page. Here's my problem, how do I deal with associates who don't have an entry
    in the pics table. Right now they just don't show because they don't meet the
    requirements of the query. thanks Kevin

    SSgt P Guest

  13. #13

    Default Re: Query Question

    Post your query please.
    Sameer Guest

  14. #14

    Default Re: Query Question

    SELECT associates.assocID, associates.assocTgt, associates.assocName,
    pics.imgID, pics.imgAssoc, pics.imgThumb, pics.imgFull FROM associates, pics
    WHERE associates.assocTgt = #URL.tgtID# AND associates.assocID = pics.imgAssoc
    I need a way to deal with that assocID = imgAssoc piece.

    SSgt P Guest

  15. #15

    Default Re: Query Question

    Try this :-

    SELECT
    associates.assocID,
    associates.assocTgt,
    associates.assocName,
    pics.imgID,
    pics.imgAssoc,
    pics.imgThumb,
    pics.imgFull

    FROM associates
    LEFT JOIN pics ON associates.assocID = pics.imgAssoc AND associates.assocTgt =
    #URL.tgtID#

    WHERE pics.imgAssoc IS NULL

    Sameer Guest

  16. #16

    Default Re: Query Question

    This is a SQL Server solution, Sarge; YMMV on other platforms and, no, I'm not
    deciphering Access syntax for joins. SELECT associates.assocID,
    associates.assocTgt, associates.assocName, pics.imgID, pics.imgAssoc,
    pics.imgThumb, pics.imgFull FROM associates LEFT OUTER JOIN pics on
    associates.assocID = pics.imgAssoc WHERE associates.assocTgt = #URL.tgtID#
    This query returns NULL values for the pics columns, but still returns a record
    for the associate. HTH, (381SMW, 343COMPW, 831AD 11JAN1978-2FEB1987)

    philh Guest

  17. #17

    Default Query Question

    Hi guys, I have created an update query something simple like:

    UPDATE tableName
    SET variable = anotherVariable
    WHERE aVariable=var

    I was wondering could I have made this an Insert query as when i have tried to
    make this an insert query?
    It seems to be having problems with the where part of the statement.

    Thanks in advance.

    theDude28 Guest

  18. #18

    Default Re: Query Question

    If you are adding a new record it will be an insert query, but I'm not sure I understand the question.
    kyle969 Guest

  19. #19

    Default Re: Query Question

    Update MODIFIES an EXISTING record
    Insert CREATES a NEW record

    They cannot be interchanged; their syntax is different too

    UPDATE table
    SET column = value
    WHERE column = value

    INSERT into TABLE (columns)
    VALUES (variables)

    Aegis Kleais Guest

  20. #20

    Default Query question

    Hello,
    Can somebody help me on this issue:

    I have on DB table

    ID
    NAME
    REFER_TO_OTHER_PERSON_ID
    REFER_TO_OTHER_PERSON_ID_2

    1, jack, NULL, NULL
    2, bryan, NULL, NULL
    3, martin, 1,NULL
    4, james, NULL, 3

    What I want in the output is:
    JACK
    --martin
    ----james
    BRYAN

    Can somebody help me on the query?
    Thanks!


    Samall Guest

Page 1 of 3 123 LastLast

Similar Threads

  1. Query Question Please HELP
    By flooker in forum Coldfusion Database Access
    Replies: 4
    Last Post: June 26th, 04:45 PM
  2. SOS! Query Question
    By design in progress in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 11th, 05:39 AM
  3. 6.1 Query of Query Question. Ref to own Col
    By smcgovern in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: April 24th, 10:17 PM
  4. SQL query question
    By Joost Kraaijeveld in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: February 3rd, 11:48 AM
  5. ASP SQL query question
    By Brandon in forum ASP Database
    Replies: 6
    Last Post: May 3rd, 04:37 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