Multiple sql queries to single query using inner join

Question

i have this java code:

ArrayList<MessageMap> ids = getNewMail(UserID, type, maxIdMessage);
Message[] message = new Message[ids.size()];
if(ids.size()>0){
   ResultSet rs;
   int j =0;
   for(MappaMessaggi i : ids){
      pstmt = conn.prepareStatement("SELECT * FROM Messaggi WHERE MessageID = ?");
      pstmt.setInt(1, i.getMessageID());
      rs = pstmt.executeQuery();
      rs.next();
      .
      .
      .
   }

where getNewMail is:

public synchronized ArrayList<MessageMap> getNewMail(int UserID,int type,int max){
   ArrayList<MessageMap> map = new ArrayList<MessageMap>();
   try {
      pstmt = conn.prepareStatement("SELECT * FROM MessageMap WHERE UserID = ? AND TipoID = ? AND MessageID > ?");
      .
      .//fill arraylist with resultSet 
      .
}

I know that is possible to do the same with just one query but i don't know how.. can someone open my eyes? :) thanks!!!

EDIT: i try:

SELECT i.*
FROM Messaggi AS i
INNER JOIN MessageMap AS p i.MessageID = p.MessageID
WHERE p.MessageID = 1 AND p.UserID = 1 AND p.TipoID = 2

BUT I RETRIEVE:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'i.MessageID = p.MessageID WHERE p.MessageID = 1 AND p.UserID = 1 AND p.TipoID = ' at line 3

Mahmoud Gamal · Accepted Answer · 2012-06-28 15:24:45Z

1

Try this:

SELECT i.*, p.*
FROM Messaggi i
INNER JOIN MessageMap p ON i.MessageID = p.MessageID
WHERE i.MessageID = ? AND p.UserID = ? AND p.TipoID = ?

edited Jun 28, 2012 at 15:24

answered Jun 28, 2012 at 15:09

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mahmoud Gamal Over a year ago

@JackTurky, Sorry I forgot the ON, see my edit. It should be INNER JOIN MessageMap p ON i.MessageID = p.MessageID instead of INNER JOIN MessageMap p i.MessageID = p.MessageID.

Luiggi Mendoza · Accepted Answer · 2012-06-28 15:08:05Z

1

Based in your question and queries, the simple solution I find could be this query (maybe there are another columns that relate your tables)

SELECT A.*
FROM MessageMap A
    INNER JOIN Message B on A.MessageId = B.MessageId
WHERE
    A.UserId = ?
    AND A.TipoID = ?
    AND A.MessageId = ?

Also, please specify if you need those 3 parameters or you could not send any of them.

answered Jun 28, 2012 at 15:08

Luiggi Mendoza

85.9k16 gold badges159 silver badges354 bronze badges

1 Comment

MatBailie Over a year ago

Probably just Messaggi.* to be honest.

Collectives™ on Stack Overflow

Multiple sql queries to single query using inner join

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related