Win a copy of Spring Boot in Practice this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Liutauras Vilda
  • Henry Wong
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Al Hobbs
  • Carey Brown
Bartenders:
  • Piet Souris
  • Mikalai Zaikin
  • Himai Minh

Better sql statement?

 
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author & internet detective
Posts: 41185
848
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a bunch. I was trying to avoid that one big join But other option is multiple queries.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic