File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Better sql statement?

 
Brian Percival
Ranch Hand
Posts: 163
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I know the subject doesn't help much, bear with it please.

Here is hte scenario.

I have a table mytable with five userID fields user1,user2,user3 etc.

I have the actual users table with fname and lname (firstname,lastname)

what is the best query to bring the user names for a given record in MYTABLE ?

Should I do a left join five times??? ( sometimes user1,user2,user3 etc can all be the same id values. ) Or should I do five single queries to get the user names?

I am using MSSQL

Thanks for your time.

Brian
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33713
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian,
Part of the difficult is that the SQL is illustrating a "code smell." That user id column really should be a separate table. That table myTable would have a primary key that is used in the user id table.

Assuming you can't change the schema, the best SQL query depends on the full SQL query. For example if we are querying by first name, the five joins would be good because that is the most restrictive filter.

If the most restrictive filter is on myTable, you could do a union and one big join.
select cols from
(select cols from myTable where ...) t1,
(select cols from myTable where ...) t2,
...
(select cols from myTable where ...) t5,
userTable u
where userid = u.user

It also depends on how expensive a join with the users table is. If it is a small table, it is unlikely to make a difference which approach you need.
 
Brian Percival
Ranch Hand
Posts: 163
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a bunch. I was trying to avoid that one big join But other option is multiple queries.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic