Professional Web Applications Themes

Newbie question - Microsoft SQL / MS SQL Server

select d.employeeid, f.shipvia, d.mx1, g.shipvia, d.mx2 from (select employeeid, max(cnt) mx1, max(cnt) mx2 from (select employeeid, shipvia, count(*) cnt from orders group by employeeid, shipvia) c group by employeeid) d selct from max(cnt) mx1, max(cnt) mx2 from (select employeeid, shipvia, count(*) cnt from orders group by employeeid, shipvia) c group by employeeid) d join (select employeeid, shipvia, count(*) cnt from orders where orders.employeeid = employeeid group by employeeid, shipvia) f on d.employeeid = g.employeeid and d.mx1 = f.cnt join (select employeeid, shipvia, count(*) cnt from orders where orders.employeeid = employeeid group by employeeid, shipvia) g on d.employeeid = g.employeeid and ...

  1. #1

    Default Newbie question

    select d.employeeid, f.shipvia, d.mx1, g.shipvia,
    d.mx2
    from
    (select employeeid, max(cnt) mx1, max(cnt) mx2
    from
    (select employeeid, shipvia, count(*) cnt
    from orders
    group by employeeid, shipvia) c
    group by employeeid) d
    selct from max(cnt) mx1, max(cnt) mx2
    from
    (select employeeid, shipvia, count(*) cnt
    from orders
    group by employeeid, shipvia) c
    group by employeeid) d

    join
    (select employeeid, shipvia, count(*) cnt
    from orders where orders.employeeid = employeeid
    group by employeeid, shipvia) f
    on d.employeeid = g.employeeid and d.mx1 = f.cnt

    join
    (select employeeid, shipvia, count(*) cnt
    from orders where orders.employeeid = employeeid
    group by employeeid, shipvia) g
    on d.employeeid = g.employeeid and d.mx2 = g.cnt




    it is part of a code that i am doing sefl learn
    process on.
    how do i add a condition to the second join to make
    d.mx2 less than d.mx1?.





    --
    Sent by msg_2222 from yahoo piece of com
    This is a spam protected message. Please answer with reference header.
    Posted via http://www.usenet-replayer.com/cgi/content/new
    msg_2222@yahoo.com Guest

  2. #2

    Default newbie question

    I am very new to SQL Server, and was currently assigned a project to
    switch a MySQL db to MSSQL. So I thought...I can just go to
    phpMyAdmin, get the dump that creates all the tables and inserts the
    data. Create a database in SQL Server, then use the Query yzer to
    run the SQL statement from the dump file and it would create the db
    structure and populate with data.

    However, I ran into a lot of errors. Iniatially, I took out all the
    TYPE=MyISAM; statements from the sql stmt. Then, I took all
    auto_increment stmts out. But it still keeps burping at me. Am I
    just completely off, in left field? Is there another way of doing
    this? Please help me, because I have no idea what's going on. Below
    are some of the errors from the query yzer:

    Server: Msg 156, Level 15, State 1, Line 16
    Incorrect syntax near the keyword 'KEY'.
    Server: Msg 156, Level 15, State 1, Line 331
    Incorrect syntax near the keyword 'KEY'.
    Server: Msg 156, Level 15, State 1, Line 398
    Incorrect syntax near the keyword 'User'.
    Server: Msg 156, Level 15, State 1, Line 527
    Incorrect syntax near the keyword 'KEY'.
    Server: Msg 156, Level 15, State 1, Line 634
    Incorrect syntax near the keyword 'KEY'.
    Server: Msg 156, Level 15, State 1, Line 723
    Incorrect syntax near the keyword 'KEY'.

    Lines 1 through 19 say the following:
    CREATE TABLE LegalGuardians (
    LegalGuardianID int(11) NOT NULL,
    LGFName varchar(50) default NULL,
    LGMName varchar(50) default NULL,
    LGLName varchar(50) default NULL,
    LGAddr1 varchar(50) default NULL,
    LGAddr2 varchar(50) default NULL,
    LGAddr3 varchar(50) default NULL,
    LGCity varchar(50) default NULL,
    LGState varchar(50) default NULL,
    LGZipCode varchar(50) NOT NULL default '',
    LGTelephone varchar(50) default NULL,
    LGRelationship varchar(50) default NULL,
    ClientID int(11) NOT NULL default '0',
    PRIMARY KEY (LegalGuardianID),
    KEY ClientID (ClientID),
    KEY LegalGuardianID (LegalGuardianID),
    KEY LGZipCode (LGZipCode)
    )

    Thanks.

    Thiago Silva
    web developer
    tafs7 Guest

  3. #3

    Default Re: newbie question

    Hi,

    There is no such thing as PRIMARY KEY in SQL Server. It is called IDENTITY
    (for more information. look in Books Online).


    --
    Regards,
    Kristofer Gafvert
    http://www.ilopia.com - My personal Web Site, with information about things
    i find interesting, for example Windows Server 2003.
    Reply to newsgroup only. Remove NEWS if you must reply by email, but please
    do not.


    "tafs7" <com> wrote in message
    news:google.com... 


    Kristofer Guest

  4. #4

    Default Re: newbie question

    Thiago,

    The CREATE TABLE syntax is wrong.First, there is no need to mention the
    width with the INT datatype.Are you trying to make a composite primary key
    on the combination of LegalGuardianID,ClientID and LGZipCode?The code below
    assumes that. Since you have not mentioned any default value, NULL will be
    accepted by default.

    CREATE TABLE LegalGuardians (
    LegalGuardianID int NOT NULL,
    LGFName varchar(50) NULL,
    LGMName varchar(50) NULL,
    LGLName varchar(50) NULL,
    LGAddr1 varchar(50) NULL,
    LGAddr2 varchar(50) NULL,
    LGAddr3 varchar(50) NULL,
    LGCity varchar(50) NULL,
    LGState varchar(50) NULL,
    LGZipCode varchar(50) NOT NULL default '',
    LGTelephone varchar(50) NULL,
    LGRelationship varchar(50) NULL,
    ClientID int NOT NULL default '0',
    CONSTRAINT PK_IDS PRIMARY KEY (LegalGuardianID,ClientID,LGZipCode)
    )

    Refer BooksOnLine for the proper syntax on various t-sql commands.


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "tafs7" <com> wrote in message
    news:google.com... 


    Dinesh.T.K Guest

  5. #5

    Default Re: newbie question

    Yes,

    Busy day and i was thinking about something else when i wrote it, so i mixed
    it up with AUTO_INCREMENT (mysql), which is the same as IDENTITY in SQL
    Server.

    Sorry!

    --
    Regards,
    Kristofer Gafvert
    http://www.ilopia.com - My personal Web Site, with information about things
    i find interesting, for example Windows Server 2003.
    Reply to newsgroup only. Remove NEWS if you must reply by email, but please
    do not.


    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:phx.gbl... 
    IDENTITY [/ref]
    IDENTITY 
    > things 
    > please 
    > >
    > >[/ref]
    >
    >[/ref]


    Kristofer 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. A newbie with a newbie question
    By dusty_davis@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 5
    Last Post: October 14th, 07:54 AM
  3. a newbie question(network question)
    By hardemr in forum MySQL
    Replies: 2
    Last Post: February 2nd, 06:08 PM
  4. Replies: 5
    Last Post: December 3rd, 10:44 AM
  5. Pen Tool Use Question. (Embarrassingly Newbie Question)
    By Bozo Schmozo in forum Macromedia Flash
    Replies: 0
    Last Post: November 12th, 10:00 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