MYSQL: Retrieve one row with data from JOINED tables with multiple matching rows

[pechar]

Hi guys,

I'm not sure this is possible in one SQL query:
I have a table called TB_EMAIL and another called TB_OTHER_BLOB.
TB_EMAIL contains details like: to, from, subject, body and since I will be reusing headers and footers I aslo store header and footer reference to TB_OTHER_BLOB in TB_EMAIL.

What I need is a row (given its id) with all columns (except the references to TB_OTHER_BLOB) from TB_EMAIL and two extra columns one being email_header and the other email_footer which are in two different rows in TB_OTHER_BLOB

What I came up with is:

SELECT     tb_email.email_to_email,
    tb_email.email_from_email, 
    tb_email.email_from_display,
    tb_email.email_subject,
    tb_email.email_body,
    tb_other_blob.blob_text     
 
FROM    tb_email 
INNER JOIN tb_other_blob ON
      tb_email.email_header_blob = tb_other_blob.blob_name OR
      tb_email.email_footer_blob = tb_other_blob.blob_name

This will return identical rows except for the last column which obviously differs.
What can I do to retrieve the same results in one row with both blob_text columns added to the remaining columns.

I dont know if I explained myself well enough
Please let me know if you have any problems understanding I'll gladly try to explain in more detail

Any help is greatly appreciated
Thanks
Luk

[Atli]

You could try something like:

SELECT 
  a.Col1, a.Col2 
  b1.text AS 'header_text',
  b2.text AS 'footer_text'
FROM tblA AS a
INNER JOIN tblB AS b1
  ON b1.fk = a.pk
  AND b1.name = a.header_name
INNER JOIN tblB AS b2
  ON b2.fk = a.pk
  AND b2.name = a.footer_name
 

It should JOIN the tblB table twice, but as two separate tables.

[pechar]

You could try something like:

SELECT 
  a.Col1, a.Col2 
  b1.text AS 'header_text',
  b2.text AS 'footer_text'
FROM tblA AS a
INNER JOIN tblB AS b1
  ON b1.fk = a.pk
  AND b1.name = a.header_name
INNER JOIN tblB AS b2
  ON b2.fk = a.pk
  AND b2.name = a.footer_name
 

It should JOIN the tblB table twice, but as two separate tables.

Now you are a real life saver!!
Thank a lot it worked perfectly. Why didn't I think of this its so simple.

Thanks again
Luk