File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases 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 and Relational Databases
Bookmark "Better sql statement?" Watch "Better sql statement?" New topic

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.

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, 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:
subject: Better sql statement?
It's not a secret anymore!