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

    Join Date
    Dec 2012
    Posts
    5
    Rep Power
    0

    Question How to write a SQL request less?


    Hello from Russia! I have a code like this:
    sql Code:
    SELECT `cabinet`.`name_test`, `answers`.`answer_id`, (
    SELECT `answer_id` FROM `answers` WHERE `user_id` = '353281' AND `answers`.`test_id` = `cabinet`.`test_id`
    ) AS answer_2 FROM `cabinet` INNER JOIN `answers`
     WHERE `user_id` = '184918649' AND `friend_id` = '353281' AND `answers`.`test_id` = `cabinet`.`test_id`

    How not to write twice: `answers`.`test_id` = `cabinet`.`test_id`?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    could you please do a SHOW CREATE TABLE for each table

    also, in your own words, please, what is the query supposed to do or trying to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    5
    Rep Power
    0
    Select from the table, responses of the first and second users. If the other user has not responded to one of the tests, in this case is written NULL.
    Is it possible to write `answers`.`test_id` = `cabinet`.`test_id` once? My code queries the table twice. Is it possible to query a table only once?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by Dimaz
    Is it possible to query a table only once?
    sure, but that depends on what you're trying to accomplish

    i don't understand "first and second users"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    5
    Rep Power
    0
    For `user_id` = '184918649' and `user_id` = '353281', get a list of response IDs. Tests are the same.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by Dimaz
    For `user_id` = '184918649' and `user_id` = '353281', get a list of response IDs.
    Code:
    SELECT answer_id 
      FROM answers 
     WHERE user_id IN ( '184918649' , '353281' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    5
    Rep Power
    0
    sql Code:
    SELECT `cabinet`.`name_test`, `answers`.`answer_id`, `answers`.`answer_id` AS answer2
     FROM `cabinet` INNER JOIN `answers` ON `answers`.`test_id` = `cabinet`.`test_id`
     WHERE `user_id` IN('184918649' AND `friend_id` = '353281', '353281')

    name_test answer_id answer2
    Test1_______111_______111
    Test2_______222_______222
    I need this:
    name_test answer_id answer2
    Test1_______111_______NULL
    Test2_______222_______211
    Сondition for user1: `user_id` = '184918649' AND `friend_id` = '353281'
    Сondition for user2: `user_id` = '353281'. friend_id need not.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by Dimaz
    Code:
     WHERE `user_id` IN('184918649' AND `friend_id` = '353281', '353281')
    that's clearly invalid sql

    and you still need to explain what you're actually trying to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    5
    Rep Power
    0
    See my first post. I want to do the same, but not to write twice `answers`.`test_id` = `cabinet`.`test_id`.

IMN logo majestic logo threadwatch logo seochat tools logo