#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    24
    Rep Power
    0

    Mysql query using join


    hey folks,

    i am a frontend developer. i recently came across this query. can anyone help me understand this query.

    [MYSQL]SELECT DISTINCT aln.strain FROM " . ALIGNMENT_TABLE . " aln JOIN " . SET_TABLE . " s ON s.id=aln.setid WHERE aln.ownerid='$ownerid' AND s.deleted=$deleted AND s.organismid=$organismid ORDER BY s.uploaded ASC, aln.strain[/MYSQL]

    thanks in advance.
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    First delete some syntactical string sugar and some reorganization to almost be able to see what the query would look like:
    Code:
    SELECT DISTINCT aln.strain
    FROM ALIGNMENT_TABLE aln
    JOIN SET_TABLE s ON s.id=aln.setid
    WHERE aln.ownerid='$ownerid'
      AND s.deleted=$deleted
      AND s.organismid=$organismid
    ORDER BY
      s.uploaded ASC
      , aln.strain
    So basically:
    1. join ALIGNMENT_TABLE with SET_TABLE
    2. Filter away based on ownerid, deleted and organismid
    3. sort on s.uploaded and aln.strain
    4. return a bunch of rows of aln.strain values

    But since we don't know what type for example the column uploaded is we can't tell you if you are going to get the oldest records (if it's a datetime) or if it's an int that counts how many times a file has been uploaded, etc).

    But always start by removing the syntactic sugar of the frontend programming language so that you actually see the query that is sent to the database (or better yet, run the query and capture it in the general query log for MySQL that way you will truly see what the query looks like, many problems like non initialized variables in where condition etc is found this way).
    /Stefan
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    sr, how can you return only DISTINCT aln.strain and yet ORDER BY s.uploaded?

    my brain's query-understanding-centre just exploded
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Well I didn't say that his query is correct! I just spun a small tale around it to try to explain in layman's eyes what we read directly in the SQL.
    /Stefan
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    24
    Rep Power
    0

    thanks for the reply....


    it seems my query is incorrect beciause i am not getting the desired result. can anyone tell me what am i doing wrong?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by ap88
    it seems my query is incorrect beciause i am not getting the desired result. can anyone tell me what am i doing wrong?
    we don't know what it's returning, nor why it's wrong, nor what you actually want it to return

    perhaps you can explain in more detail what you're trying to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo