database select statement

[jmp5167]

Hello,
I am new to sql and am trying to write an sql statement that ties in several databases into one select call. The idea of the call is to take pieces from each table depending on the user who makes the db call. therefore my function accepts a username argument which gets appended on to the end of the select statement.

[CODE]String sql = "SELECT "+groupsTable+".id,"
+groupsTable+".name AS groupname,"
+"groupowners.username AS groupusername,"
+"groupmembers.username,"
+contactsTable+".id AS contactid,"
+contactsTable+".nickname,"
+contactsTable+".email,"
+contactsTable+".phone"
+" FROM "+groupsTable
//first, join to the users table to get the username of the person who owns the group
+" INNER JOIN "+usersTable+" AS groupowners ON groupowners.id="+groupsTable+".userid"
//now, join to the contacts and users table (via the groupMembers table) to get the members of the group
+" INNER JOIN "+groupMembersTable+" ON "+groupsTable+".id="+groupMembersTable+".groupid"
+" INNER JOIN "+contactsTable+" ON "+groupMembersTable+".contactid="+contactsTable+".id"
+" INNER JOIN "+usersTable+" AS groupmembers ON groupmembers.id="+contactsTable+".contactuserid"
+" WHERE groupowners.username=?;";
[CODE]

for some reason this is not getting me the information I want

any help is much appreciated

[Old Pedant]

So did you dump out the final version of the string???

The whole concept there bothers me, specifying the table names dynamically. Makes me wonder about the DB design.

And *HOW* are you inserting the username parameter?? With a command parameter? Makes me wonder why you bother, given that you are building up all the rest of the SQL from strings.

[jmp5167]

[CODE]com.mysql.jdbc.ServerPreparedStatement[186] - SELECT mssarah_groups.id,mssarah_groups.name AS groupname,groupowners.username AS groupusername,groupmembers.username,mssarah_contacts.id AS contactid,mssarah_contacts.nickname,mssarah_contacts.email,mssarah_contacts.phone FROM mssarah_groups INNER JOIN mssarah_users AS groupowners ON groupowners.id=mssarah_groups.userid INNER JOIN mssarah_groupmembers ON mssarah_groups.id=mssarah_groupmembers.groupid INNER JOIN mssarah_contacts ON mssarah_groupmembers.contactid=mssarah_contacts.id INNER JOIN mssarah_users AS groupmembers ON groupmembers.id=mssarah_contacts.contactuserid WHERE groupowners.username='max';[CODE]

Is this what you mean by the final version of the string?

and yes the username gets inserted as a parameter. What would a better way of doing this be

[Old Pedant]

Looks fine to me. If it's not getting you the "information you want" then the SQL itself must be wrong.

The part that bothered me was your needing to put in "mssarah_" all over the place. It's bad db design to have one table for every "group" or whatever "mssarah" represents. Either you should be using separate databases, for security, or you should be using one table for multiple "groups" with a delimiting field.

But that shouldn't affect the correctness (or lack thereof) of the SQL.

Time to just start analyzing the SQL in detail. Maybe do partial queries to understand the data you are getting.