aspose file tools*
The moose likes JDBC and the fly likes Better sql statement? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Better sql statement?" Watch "Better sql statement?" New topic
Author

Better sql statement?

Brian Percival
Ranch Hand

Joined: Jun 23, 2004
Posts: 163
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30506
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Brian Percival
Ranch Hand

Joined: Jun 23, 2004
Posts: 163
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
 
subject: Better sql statement?