Hi I have a table Emname prefe

[anilkutty]

Hi
I have a table

Emname preference Location
David 1 Canada
David 2 Italy
Michael 1 USA

Michael 2 Japan

what iam trying to achive is

empname preferenc1 preference2

David Canada Italy
Michael USA Japan

without creating any tables

can someone help?

 

[rac2]

Easy if you have two preferences for everyone.

Here are two ways to write such a query.

SELECT p1.Emname, p1.location, p2.location

FROM myLocationPreference p1
JOIN
( SELECT Emname, location
  FROM myLocationPreference 
  WHERE  preference = 2 ) p2

ON p1.Emname = p2.Emname

WHERE preference = 1



SELECT p1.Emname, p1.location, p2.location

FROM myLocationPreference p1
JOIN myLocationPreference p2 ON p1.Emname = p2.Emname AND p2.preference = 2

WHERE p1.preference = 1

This can be extended to any fixed number of preferences.

BUT, if different people have different numbers of preferences then you cannot write a query that will work for everyone. You will need to write a procedure in that case.