0

I have a table that has a Name column along with with 3 identifiers that refer to other entries within the same table. I a query that resolves those 3 identifiers into the actual names of the records they are referring to. So far I have only managed the following...

SELECT mt.Name, mt2.Name, mt3.Name, mt4.Name 
FROM MyTable AS mt1
LEFT OUTER JOIN MyTable AS mt2 ON mt2.Id = mt1.RefId1
LEFT OUTER JOIN MyTable AS mt3 ON mt3.Id = mt1.RefId2
LEFT OUTER JOIN MyTable AS mt4 ON mt4.Id = mt1.RefId3

...which works and indeed returns the names of the three references records. Note that in some cases the RefId1/2/3 values might be empty and so not all RefId fields are always used. It works but is not exactly fast and I am sure someone who actually knows SQL can improve this significantly. Any ideas?

Phil Wright
  • 22,580
  • 14
  • 83
  • 137

2 Answers2

1

To improve your query, you can use INNER JOIN instead of LEFT OUTER JOIN. If I understand your comment correctly, either mt2, or mt3, or mt4 will return a value.

So, a valid query just may be written like this. This makes sure that as soon as it finds a match, it stops looking ahead.

SELECT mt.Name, mt2.Name
FROM MyTable mt, MyTable mt2
WHERE mt2.id = mt.RefId1
OR mt2.id = mt.RefId2
OR mt2.id = mt.RefId3

Let me know if this works.

Achrome
  • 7,773
  • 14
  • 36
  • 45
  • All three of the RefId fields could point at a different record and so I could need to get all three names back. – Phil Wright Jan 30 '13 at 05:49
  • Okay, in that case, you can simply replace LEFT OUTER JOIN in your query with INNER JOIN. INNER JOIN works on the concept of intersecting the result sets, so that should do it for you. – Achrome Jan 30 '13 at 05:59
  • Unfortunately inner join omits the rows if any of the intersections is empty, so you will need to add an empty check on all refIds. I am trying to find a workaround for that, and will update the answer, or will reply on a comment once I find something. – Achrome Jan 30 '13 at 06:19
0

Have you tried indexing your table?

user1965813
  • 671
  • 5
  • 16