select values from the 2 different table

[perhapscwk]

Sample tables (company_id RK companies.id):

Companies
id company
1 …
2 …
3 …

Locations
id company_id location revision
1 1 … 1
2 2 … 1
3 1 … 2
4 1 … 3
5 2 … 2
6 1 … 4

How to write an SQL query to select values from the following columns (companies.company, locations.location) where locations.revision value will be the highest for each company

I try
Select distinct companies.company, locations.location from Companies, Locations where companies.id=Locations.company_id order by locations.revision

but it not works..

How? thanks.

[coolsti]

This will work if you have a new enough mysql that allows subqueries:

select Companies.id, Locations.location, revision 
from Companies inner join Locations on Companies.id=Locations.id 
inner join 
(select C1.id, max(C2.revision) as maxrevision 
from Companies C1 inner join Locations C2 on C1.id=C2.id group by C1.id) T2
on Companies.id=T2.id and revision=maxrevision
 

Try to understand how this works, it is very useful. What you do is join your two tables, but then you get a row for every possible value of revision. So you need a "group by" (not a distinct). But the problem is, when you do the grouping, you want to extract the row that corresponds to the maximum revision for each company, not just the first row that appears in the join.

So to do this, you create the table T2 that appears in the subquery. Here, T2 consists of the companies "id" field, and the max(revision) which I give the alias maxrevision. In this subquery, table T2 is "grouped by" the company ID, and this causes the table to have rows which give the maximum value for the revision for each company ID.

So now you just need to join the first two joined tables with the table T2 created in the subquery, using the two equalities "Companies.id=T2.id" and "revision=maxrevision" because you only want the rows in your result that correspond to the maximum value of revision for each company.

[perhapscwk]

ThaNKS SO MUCH. thanks.

This will work if you have a new enough mysql that allows subqueries:

select Companies.id, Locations.location, revision 
from Companies inner join Locations on Companies.id=Locations.id 
inner join 
(select C1.id, max(C2.revision) as maxrevision 
from Companies C1 inner join Locations C2 on C1.id=C2.id group by C1.id) T2
on Companies.id=T2.id and revision=maxrevision
 

Try to understand how this works, it is very useful. What you do is join your two tables, but then you get a row for every possible value of revision. So you need a "group by" (not a distinct). But the problem is, when you do the grouping, you want to extract the row that corresponds to the maximum revision for each company, not just the first row that appears in the join.

So to do this, you create the table T2 that appears in the subquery. Here, T2 consists of the companies "id" field, and the max(revision) which I give the alias maxrevision. In this subquery, table T2 is "grouped by" the company ID, and this causes the table to have rows which give the maximum value for the revision for each company ID.

So now you just need to join the first two joined tables with the table T2 created in the subquery, using the two equalities "Companies.id=T2.id" and "revision=maxrevision" because you only want the rows in your result that correspond to the maximum value of revision for each company.

[perhapscwk]

thanks.

But your query will return 3 column, how to just get
2 column? Companies.id, Locations.location

[coolsti]

Hi,

yes, just try to understand what is going on, then you can modify this query structure to many possible situations.

The query is composed basically of two parts. The first part does the select as you wish to see the results, meaning it gives you the columns that you wish to see:

select Companies.id, Locations.location, revision
from Companies inner join Locations on Companies.id=Locations.id

and the second part of the query is building up this extra table, which I call T2 here, in order to allow you to select only the rows from the first part of the query that match your desired criteria, in this case "for each company, the row with the maximum revision":

inner join
(select C1.id, max(C2.revision) as maxrevision
from Companies C1 inner join Locations C2 on C1.id=C2.id group by C1.id) T2
on Companies.id=T2.id and revision=maxrevision

So if you want to have only two columns in your result instead of three, modify the first part of the query, to this:

select Companies.id, Locations.location
from Companies inner join Locations on Companies.id=Locations.id

Here I assumed you wish to have only the company ID and the location in your results table. You can add or remove columns in this part of the query as you desire.