create a relation bewtween 2 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

create a relation bewtween 2 tables

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-05-18 : 18:24:13

hi there i have to tables

and i want to create a inner join between them

table called cars

idcar make
1 audi
2 bmw
3 ferrari

table called "Questions"

idquestion idcar date (mm/dd/year) city
20 2 01/01/2012 miami
45 3 01/30/2012 london
302 2 05/16/2012 milan
312 3 05/18/2012 ibiza

i want to do a inner join between the 2 tables question.idcar=cars.idcar but only with the max(idquestion)

thats the result that i want

car.idcar car.make question.idquestion question.city
2 bmw 302 milan
3 ferrari 312 ibiza

thanks for reading

and many many thanks for your help

kind regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-18 : 23:06:48

[code]
select idcar, make, idquestion, city
from
(
select c.idcar, c.make, q.idquestion, q.city,
rn = row_number() over (partition by c.idcar order by q.date desc)
from cars c inner join questions q on c.idcar = q.idcar
) d
where d.rn = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-19 : 07:15:52

Another way but bit complex


SELECT  C.CAR,C.ID,Q3_IDQUESTION,Q3_CITY FROM #CAR C INNER JOIN 
  (SELECT 
          Q.IDCAR AS Q3_IDCAR
         ,Q1.IDQUESTION AS Q3_IDQUESTION
         ,Q1.CITY AS Q3_CITY FROM #QUESTION Q1
  INNER JOIN(SELECT IDCAR,MAX(IDQUESTION) IDQUESTION FROM #QUESTION GROUP BY IDCAR) Q
  ON Q.IDQUESTION = Q1.IDQUESTION)Q3 ON C.ID = Q3.Q3_IDCAR

Vijay is here to learn something from you guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources