SQL Select from T2, where something in T1

I have two tables, one containing an ID and the message and th开发者_开发问答e other containing the username and message id's (e.g.:

T2 structure:

CREATE TABLE `messages` (
`messageID` int NOT NULL AUTO_INCREMENT,
`message` varchar(8192) NOT NULL default '',
PRIMARY KEY (`messageID`)
);

T1 structure:

CREATE TABLE messageID(
`username` varchar(10) NOT NULL default '',
`messageID1` int(11) NOT NULL default '0',
`messageID2` int(11) NOT NULL default '0',
`messageID3` int(11) NOT NULL default '0',
`messageID4` int(11) NOT NULL default '0',
`messageID5` int(11) NOT NULL default '0',
`messageID6` int(11) NOT NULL default '0',
`messageID7` int(11) NOT NULL default '0',
`messageID8` int(11) NOT NULL default '0',
`messageID9` int(11) NOT NULL default '0',
`messageID10` int(11) NOT NULL default '0',
PRIMARY KEY (`username`)
);

I would like to select all the "message" fields from messages table, where username has these message ID's (hope that makes sense...). E.g. a user has messageID1 = 1; messageID2 =5; I would like to return "message" from messages where messageID = 1 && messageID=5.

I know how to do it in more then one statement, but I would prefer to only execute one statement (even if its a slow statement)

Ill change the phrasing of the question if I can find a better way to describe what I need.

Thank you.

Your struggling because your database design is flawed.

You are breaking First normal form by having Repeating Groups Across Columns

From the looks of things, one user can have multiple messages.

Users (UserID, Username)

Messages (MessageID, UserID, MsgOrder)

Then you can do

SELECT * FROM Messages 
JOIN Users ON Messages.UserID = Users.UserID 
WHERE Users.Username = `Username`
     AND (MsgOrder = 1 OR MsgOrder = 5)

If the same message can be referenced by many users then you have a many to many relationship and need a junction table.

Users (UserID, Username)

Messages (MessageID, Message)

User_Messages (UserID, MessageID, MsgOrder)

If you cannot change the database schema, they you will be stuck with a loop of some sort, or multiple queries combined with UNION

I am not quite sure if i understand the problem correctly. You may need a loop....

Is this what you want?

int i = 1;
while(i<11){
    String msgQuery = "SELECT message FROM messages WHERE messageID IN (SELECT messageID"+i+"FROM messageID)";
.
.
.
}

Maybe something like this?

SELECT messages.message FROM (
   SELECT username, messageID1 as messageID from messageID
   UNION
   SELECT username, messageID2 as messageID from messageID
   UNION
   SELECT username, messageID3 as messageID from messageID
   UNION
   SELECT username, messageID4 as messageID from messageID
   UNION
   SELECT username, messageID5 as messageID from messageID
   UNION
   SELECT username, messageID6 as messageID from messageID
   UNION
   SELECT username, messageID7 as messageID from messageID
   UNION
   SELECT username, messageID8 as messageID from messageID
   UNION
   SELECT username, messageID9 as messageID from messageID
   UNION
   SELECT username, messageID10 as messageID from messageID
) m JOIN messages ON m.messageID = messages.messageID 
WHERE m.username = ?