1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Whats wrong with this simple query?

Discussion in 'MySQL' started by greatlogix, Jul 30, 2008.

0
  1. #1
    SELECT * FROM sw_credit s, tblcustomer c, WHERE s.credit > 0 AND s.customer_id=c.id ORDER BY s.cid DESC


    Error Code : 1064
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE s.credit > 0 AND s.customer_id=c.id ORDER BY s.cid' at line 1

    Please help me to correct this query.
     
    greatlogix, Jul 30, 2008 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    SELECT * FROM sw_credit s, tblcustomer c,

    needs to be
    SELECT * FROM sw_credit s, tblcustomer c

    The extra comma was killing it.
     
    jestep, Jul 30, 2008 IP
    greatlogix likes this.
  3. greatlogix

    greatlogix Active Member

    Messages:
    664
    Likes Received:
    13
    Best Answers:
    1
    Trophy Points:
    85
    #3
    Silly me :eek: it was a quick fix

    Thanks. Rep added :)
     
    greatlogix, Jul 30, 2008 IP
  4. andrewgjohnson

    andrewgjohnson Active Member

    Messages:
    180
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #4
    The error is because you have an extra comma after "FROM sw_credit s, tblcustomer c"

    You should be aware that "SELECT *" are rarely the optimal way to go. Consider only SELECTing the columns you need for maximum speed.
     
    andrewgjohnson, Jul 30, 2008 IP
  5. king_cobra

    king_cobra Peon

    Messages:
    373
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    0
    #5
    I wouldn't say that speed is the factor which shd decide against SELECT *. The factor should be space. SELECT * takes up more data, the bulk of which is not necessary.
     
    king_cobra, Jul 31, 2008 IP
  6. andrewgjohnson

    andrewgjohnson Active Member

    Messages:
    180
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #6
    Ummm... are you kidding? Space where? The server's RAM for the 5 seconds it stores it?
     
    andrewgjohnson, Jul 31, 2008 IP
  7. Social.Network

    Social.Network Member

    Messages:
    517
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    35
    #7
    I should translate, so there is no confusion. Invoking an unqualified SELECT * returns all columns whether the client application needs the data or not. This approach is not recommended, explicitly name columns in SELECT statements.
     
    Social.Network, Jul 31, 2008 IP
  8. king_cobra

    king_cobra Peon

    Messages:
    373
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    0
    #8
    consider the scenario..

    a table has id, name, demographic details like address and stuff, essays written by the member and some other data..

    a query needs to be written to pull all the names in the table.

    if u use SELECT *, you will be collecting 1000 folds the data which you actually require. So the real optimization happens not in savings in terms of time, but in terms of data (space). server's ram or laptop's ram.. doesn't matter.. if we store data which is not necessary there the space available for other applications will be limited and thus impacting the overall response time..
     
    king_cobra, Jul 31, 2008 IP
  9. garrettheel

    garrettheel Peon

    Messages:
    341
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #9
    ^ I agree. If you've got a lot of people on your site, explicitly naming the rows you want returned will make it immensely quicker then if you just go "SELECT *"
     
    garrettheel, Aug 1, 2008 IP
  10. andrewgjohnson

    andrewgjohnson Active Member

    Messages:
    180
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #10
    The laptop's RAM? The query isn't sent to to the end users, it is executed on the server and then the *RESULTS* may or may not be sent to the user.

    You honestly don't know what you're talking about...
     
    andrewgjohnson, Aug 1, 2008 IP
  11. king_cobra

    king_cobra Peon

    Messages:
    373
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    0
    #11
    "server's ram or laptop's ram" was just an expression :rolleyes:..
     
    king_cobra, Aug 1, 2008 IP