MySQL Select help required
-
Could someone pont me in the right direction here cos I am pulling my hair out and I have little to start with lol I am working with 3 tables User Table: Auto_ID User_Name Profile Table: Auto_ID User_ID (This is taken from the Auto_ID in the User Table e.g. 20) Prof_Img Comments Table: Auto_ID Message_To (This is taken from the Auto_ID in the User Table e.g. 41) Message_From (This is taken from the Auto_ID in the User Table e.g. 20) Message I need to be able to display the Prof_Img for who the message was _to and _from I just can't figure how to get the individual picture references from the same table in the same query. I can get one of the pictures but not both, am probably not thinking about it correctly, well obviously I am or it would work lol Oh and I need the User_Name to go with each relevant picture If someone could give me a little guidance please it would save my hair line lol Many thanks in advance Regards Ray
-
Could someone pont me in the right direction here cos I am pulling my hair out and I have little to start with lol I am working with 3 tables User Table: Auto_ID User_Name Profile Table: Auto_ID User_ID (This is taken from the Auto_ID in the User Table e.g. 20) Prof_Img Comments Table: Auto_ID Message_To (This is taken from the Auto_ID in the User Table e.g. 41) Message_From (This is taken from the Auto_ID in the User Table e.g. 20) Message I need to be able to display the Prof_Img for who the message was _to and _from I just can't figure how to get the individual picture references from the same table in the same query. I can get one of the pictures but not both, am probably not thinking about it correctly, well obviously I am or it would work lol Oh and I need the User_Name to go with each relevant picture If someone could give me a little guidance please it would save my hair line lol Many thanks in advance Regards Ray
OK, you need two joins to the same tables (once for from, once for to; and both once for name, once for image); this will only work when you use aliases to discriminate the identical tables. This will not be correct, but indicates the technique; try something like this:
SELECT FU.Name, TU.Name FROM Comments
JOIN User as FU ON Comments.Message_From=FU.Auto_ID
JOIN User as TU ON Comments.Message_To=TU.Auto_ID
WHERE Comments.Auto_ID=MessageIDwhich, once correct, you can extend to also handle the images. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
OK, you need two joins to the same tables (once for from, once for to; and both once for name, once for image); this will only work when you use aliases to discriminate the identical tables. This will not be correct, but indicates the technique; try something like this:
SELECT FU.Name, TU.Name FROM Comments
JOIN User as FU ON Comments.Message_From=FU.Auto_ID
JOIN User as TU ON Comments.Message_To=TU.Auto_ID
WHERE Comments.Auto_ID=MessageIDwhich, once correct, you can extend to also handle the images. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
I finally got to sit and try your suggestion and this was wonderful help thank you so much. I do have another question about this which I will post a new messgae for shortly. Again thank you very much for your help Regards Ray
-
I finally got to sit and try your suggestion and this was wonderful help thank you so much. I do have another question about this which I will post a new messgae for shortly. Again thank you very much for your help Regards Ray
You're welcome. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.