[SOLVED] subquery on join not really working

[anatak]

hello I am trying to select data from three tables.

I am doing this with a left join inner join and subquerys.

I am sure the problem is with the subquery

Maybe this should be done with a join on a join but I have no idea how.

 

table 1 picture

id (unique)

picturepath

 

row1: 1 path1

row2: 2 path2

 

table 2 picture_comment

id (unique)

pictureId (foreign key in picture)

languageid (foreign key in language)

comment

 

row1: 1 1 1 english comment picture 1

row2: 2 1 2 french comment picture 1

row3: 3 2 1 english comment picture 2

 

table 3 language

id

language

 

row1: 1 english

row2: 2 french

 

one picture can have comments in one or more languages (one to many relation)

one comment has one language (one to one relation)

 

english is the default language

 

I am trying to select the pictureid, the comment in the default language (english), the comment in the user defined language if it exist, and the language

 

here is a query that works

IF the comment is available in both languages but not if the comment is available in only the default language.

The query also works if the user language = default language and the comment is only available in the default language.

 

SELECT picture. * , t3.LanguageLan_Language AS userlang_lang, t4.LanguageLan_Language AS defaultlang_lang, t1.Id AS userlang_picture_text_id, t2.Id AS defaultlang_picture_text_id, t1.Comment AS userlang_comment, t2.Comment AS defaultlang_comment
FROM picture, language AS t3, language AS t4
LEFT JOIN picture_text AS t1 ON t1.PictureId = picture.PictureId AND t1.LanguageId =1
INNER JOIN picture_text AS t2 ON t2.PictureId = picture.PictureId AND t2.LanguageId =1
WHERE picture.ContentId =1 AND (

SELECT t3.LanguageId = t1.LanguageId
) AND (

SELECT t4.LanguageId = t2.LanguageId
) 

 

any help is greatly appreciated

anatak

[fenway]

That really depends on how you want your results back -- all in one row, multiple records, etc.

[anatak]

fenway

sorry to not mention this

I want the result to be in one row.

 

thanks

anatak

[fenway]

But where is the user-defined language specified?

[anatak]

Fenway

the user language is specified in a session variable.

 

the problem is when the comment of the picture is not available in the user defined language.

 

that is why I want to select the default language (fixed to 1 ) and the user defined language (depending on the session var but it can be also 1) of the comment.

 

I am not sure if I making myself clear. (kinda difficult without schedule)

 

picture has a one to many relation with picture_comment

picture_comment has a one to one relation with language

 

 

here is the sql as it is build in the php code

$TableName01='picture';
	$TableName02='picture_text';
	$TableName03='language';

	$QuerySelectContentPicture="SELECT $TableName01.*, 
								t3.LanguageLan_Language as userlang_lang, 
								t4.LanguageLan_Language as defaultlang_lang, 
								t1.Id as userlang_picture_text_id, 
								t2.Id as defaultlang_picture_text_id, 
								t1.Comment as userlang_comment, 
								t2.Comment as defaultlang_comment 
								FROM 
								$TableName01, 
								$TableName03 as t3, 
								$TableName03 as t4 
								LEFT JOIN 
								$TableName02 as t1 
								ON t1.PictureId = $TableName01.PictureId AND t1.LanguageId = $_SESSION[userlanguage] 
								INNER JOIN 
								$TableName02 as t2 
								ON t2.PictureId = $TableName01.PictureId AND t2.LanguageId = $default_language 
								WHERE $TableName01.ContentId = $row01[id] AND
								(SELECT t3.LanguageId = t1.LanguageId) AND
								(SELECT t4.LanguageId = t2.LanguageId)
								;";

 

anatak

[anatak]

Fenway,

 

I am a complete idiot.

I was kinda obsessed with the select based on the id from the picture_comment that I did not think about the obvious solution.

If I use the session var (user defined language) and the variable with the fixed default language the query will always work.

 

I have to thank you for asking how the user defined language was specified.

here is the query that works.

	$TableName01='picture';
	$TableName02='picture_text';
	$TableName03='language';

	$QuerySelectContentPicture="SELECT $TableName01.*, 
								t3.LanguageLan_Language as userlang_lang, 
								t4.LanguageLan_Language as defaultlang_lang, 
								t1.Id as userlang_picture_text_id, 
								t2.Id as defaultlang_picture_text_id, 
								t1.Comment as userlang_comment, 
								t2.Comment as defaultlang_comment 
								FROM 
								$TableName01, 
								$TableName03 as t3, 
								$TableName03 as t4 
								LEFT JOIN 
								$TableName02 as t1 
								ON t1.PictureId = $TableName01.PictureId AND t1.LanguageId = $_SESSION[userlanguage] 
								INNER JOIN 
								$TableName02 as t2 
								ON t2.PictureId = $TableName01.PictureId AND t2.LanguageId = $default_language 
								WHERE $TableName01.ContentId = $row01[id] AND
								(SELECT t3.LanguageId = $_SESSION[userlanguage]) AND
								(SELECT t4.LanguageId = $default_language)
								;";

 

But I still want to know if it would be possible to do it the way I first wanted to do

 

Thanks again for asking the question that gave me my answer.

 

How can I put this topic on Solved ?

I don't see any button anymore.

 

anatak

[fenway]

Have check with the admins about the solved button... I'll mark it for you for now.  Glad you got it working, but I'm not sure what you mean by "the first way"... ?

[anatak]

Hello Fenway,

 

Thanks for closing the topic

in the way I solved it now I actually know the key between picture_comment and language (the languageId) before I start writing the query as it is a variable in the website.

 

what I was looking for in the first way was a join on a join where the first join might return nothing.

 

picture_comment is joined on picture (left join for default language, inner join for user defined language)

 

Is it possible to join language on picture_comment ?

 

anatak

[fenway]

Sure, you can join those tables... but you wanted only a single row back, so you'd need to "tell it" somehow which language IDs to "join" in, or it wouldn't work the way you intend (hence my original question).