Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013
    Posts
    2

    Unanswered: How do I build this complex sql?

    Hi


    I have 2 tables

    T1

    ID, Name, IMAGE
    1, Peter, pic_111.jpg
    2, Mark, pic222.jpg

    T2
    ID1, ID2, message, timestamp

    If Peter sends Mark a message, Peter is ID1 and mark is ID2
    and if Mark sends Peter a message Mark is ID1 and peter ID2,

    I want to select image from T1 for ID1 and ID2

    All messages from mark to someone or from someone to Mark
    "select ID1,ID2, message from T2 where ID1=2 OR ID2=2" => returns all message send from or to mark

    How do I include picture for ID1 and ID2 to this query?

    "select ID1, ID2, message, image from T1, T2 where ID1=2 OR ID2=2 T1.ID=ID1 will be wrong as I need image for UID1 AND uid2...

    So it will be possible to show picture of person whos end mark a message or who mark sent a message...

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Example 1: Not tesed on MySQL...
    Code:
    SELECT id1 , id2
         , message
         , image
     FROM  t2
     INNER JOIN
           t1
      ON   id1 = 2
       AND id  = id1
      OR   id2 = 2
       AND id  = id2
     WHERE 2 IN (id1 , id2)
    ;

  3. #3
    Join Date
    Mar 2013
    Posts
    2
    Hi

    thanks this returns image for user=2, but I would like to have image for both users user1 and user2

    every T2 row contains id1 sender and id2 receiver..

    And id1 and id2 are mapped to T1 which contains, ID, anme, image...

    And I like to do a select to receive image for both ID1 & ID2 and be able to know which image belongs to ID§ and which belongs to ID2...

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try...

    Example 2: Not tesed on MySQL...
    Code:
    SELECT id1 , id2
         , message
         , t1_1.image AS id1_image
         , t1_2.image AS id2_image
     FROM  t2
     LEFT  OUTER JOIN
           t1 AS t1_1
      ON   t1_1.id  = id1
     LEFT  OUTER JOIN
           t1 AS t1_2
      ON   t1_2.id  = id2
     WHERE 2 IN (id1 , id2)
    ;
    If always there are corresponding T1 for every ID1 and ID2,
    then try
    Example 3: Not tesed on MySQL...
    Code:
    SELECT id1 , id2
         , message
         , t1_1.image AS id1_image
         , t1_2.image AS id2_image
     FROM  t2
     INNER JOIN
           t1 AS t1_1
      ON   t1_1.id  = id1
     INNER JOIN
           t1 AS t1_2
      ON   t1_2.id  = id2
     WHERE 2 IN (id1 , id2)
    ;

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example.

    Example 4: Not tesed on MySQL...
    Code:
    SELECT id1 , id2
         , message
         , MAX(CASE id WHEN id1 THEN image END) AS id1_image
         , MAX(CASE id WHEN id2 THEN image END) AS id2_image
     FROM  t2
     LEFT  OUTER JOIN
    /* or
     INNER JOIN
    */ 
           t1
      ON   id IN (id1 , id2)
     WHERE 2  IN (id1 , id2)
     GROUP BY
           id1 , id2
         , message
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •