sql help

[rh71]

have a whole bunch of names and I need 2 version numbers via joined table.

TABLE1
nameID (key column), name (column)
1, Alan
2, Barry

TABLE2
nameID (key column), myID (column), version (column)
1, 144, 1.223
1, 180, 2.678
1, 269, 2.999
2, 144, 2.333
2, 180, 2.678
2, 269, 2.999

Desired resulting columns are all names, and versions for only the myIDs of 180 and 269
(name versionfor180 versionfor269)
Alan 2.678 2.999
Barry 2.678 2.999

Basically I need each name in 1 row, with both ID versions in that same row.

Thx.

 

[BoberFett]

If you simply need these two columns for a one off query, I'd say just join TABLE2 twice.

select t1.name, t2a.version as version180, t2b.version as version269
from table1 t1
join table2 t2a on t1.nameid = t2.nameid and t2.myid = 180
join table2 t2b on t1.nameid = t2.nameid and t2.myid = 269

However if there's a possibility that you'd need a variable number of myid columns, you're getting into something way more than basic SQL can handle. SQL isn't good at rotating data that way, you'd need specific analytical functionality and that depends on what database you're using.

 

[LokutusofBorg]

If you're using SQL Server it has PIVOT functionality that makes variable number of columns pretty easy.

 

[rh71]

thanks, i'll have to look into the PIVOT thing but the joining twice thing works. There's actually a 3rd table on a different server I have to join to create 1 neat report, but it's not working out too well, so this will do.