Converting a subquery into a connected table query

Source: Internet
Author: User
Converting a subquery into a connected table query problem SELECT * FROM (SELECT. video_name NAME,. vid,. pos,. pic,. definition, B. app_code,. strength,. ad_mark FROM access_app_video
Left join access_app B ON. app_id = B. id WHERE tag_interest = 2 AND app_id = 1 AND. ad_mark = 1 AND. status = 0 order by abs (tag_sex-16), ABS (tag_figure-10) aa
Order by aa. strength desc limit 0, 12

How to change this subquery to a table connection query?

Reply to discussion (solution)

SELECT a.video_name NAME,a.vid,a.pos,a.pic,a.definition,b.app_code,a.strength,a.ad_mark  FROM access_app_video a LEFT JOIN access_app b ON  WHERE tag_interest=2 AND app_id=1 AND a.ad_mark=1 AND a.status=0   ORDER BY a.strength, ABS(tag_sex-16),ABS(tag_figure-10) DESC  LIMIT 0,12

Thanks to the answer on the second floor, but this write, the search results are different from the original results, the original SQL means to first sort by ABS (tag_sex-16), ABS (tag_figure-10)
Then sort by a. strength. the SQL statement on the second floor changes the sorting rule ..


Can this statement be used to query connected tables?

The final result is in descending order by a. strength, when a. strength phase is simultaneously in ascending order by ABS (tag_sex-16), ABS (tag_figure-10)
Of course, when ABS (tag_sex-16), ABS (tag_figure-10) has multiple groups of the same value, the results will be slightly different

That's why I wrote it like that.


This is the case. I want to use orm to write SQL statements. I always thought that all subqueries can be replaced by join table queries, so I wanted to change subqueries to join table queries, you cannot solve the problem of table connection.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.