• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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: 41860
908
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.
 
What's a year in metric? Do you know this metric stuff tiny ad?
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic