CODEWITHSUNDEEP
phpmysqlsqljoinsubquery
polymorph
polymorph

Reputation: 79

SQL Select Using Different Parameters from the Same Table

Concept is simple: Get a field from table1 based on a certain id and a field from table2 based on a different id from table1 that represents the highest the record with the highest value. All one query, two tables.

I have tried, joins, sub-queries, joins AND sub-queries and a mixture of everything to no avail. If anyone is able to accomplish this they will have my deepest respect since I have been trying this for two days now. Here are the steps in pseudo-code:

  1. tables: table1 (field1 and field3) and table2 (field2)
  2. SELECT from table1 field1 and from table2 field2
  3. Get field1 based on table1.id
  4. Get field2 from table2 based on table1.id which has the highest value in field3

Things to know: table2 has a foreign key linking to table 1. I use the information in table1 differently to create reviews and their comments which are basically the same thing and contain the same type of data but used differently. I keep them apart by knowing who is the parent (review) and who is the child (comment). I am trying to get the review data and its description based on which comment has the most views, for example.

Sample query:

SELECT reviews.title, descriptions.description
FROM reviews, descriptions
WHERE reviews.id = 1
AND descriptions.review_id = (
    SELECT id
    FROM reviews
    WHERE views = (
        SELECT MAX(views)
        FROM reviews
        WHERE parent = 1
        -- Parent is the same id used above to select the review
    )
);

I get empty set results in most queries I have tried other queries just give me repeated data in the views field. I know some of you might argue that I should make a different table for my comments but I think this way saves a lot of database space and I have taken this as a challenge. Thank you all in advance.

NOTE: PLEASE READ THE QUESTION - without reading it entirely it will be difficult to understand.

As requested:

table: reviews
id |   title   | views |
2    'My Title'    5
1    'Other Title' 1
3    'Third Title' 3

table: description
id |     description      | review_id |
1     'Good description'       2
2     'Great description'      1
3     'Bad description'        3

Result should be: 'Good description' is selected because it the description linking to the review with the most views. The title is selected based on the passed in.

title     |  description     |
'Other Title' 'Good description'

Upvotes: 0

Views: 1900

Answers (1)

Brandon Johnson
Brandon Johnson

Reputation: 569

Ok this works in SqlServer not sure about MySQL, but perhaps it will help you get on the right track.

Select Top 1 Parent.Title, Descriptions.Description
From Reviews Parent
    inner join Reviews Child
        on Parent.Id = Child.Parent
    inner join Descriptions
        on Child.Id = Descriptions.review_id
Where Parent.Id = 1
Order By Child.[Views] desc

Fiddle: http://sqlfiddle.com/#!6/89a76/1

Psuedo Code without Table Names as Requested

Select Take the Top 1 Parent.Title, Descriptions.Description
From Table1 (The Parent)
  inner join Table1 Back onto itself (The Child)
      linking The parents Id to the Child's Parent
  inner join Table2 onto the Child
      Linking Child's Id to the Descriptions' review_id
Where the parents Id is 1 (or the Passed in value)
Order By The number of views the child had desc

Upvotes: 1

Related Questions