CODEWITHSUNDEEP
mysqlsqldatabase
Shai Zarzewski
Shai Zarzewski

Reputation: 1698

select statement with where condition (select all or some)

I have to following tables:

images:

imageID    userId    Translated    theImage
--------------------------------------------
    1         2         1           image1
    2         3         0           image2
    3         3         0           image3 
    4         3         0           image4
    5         3         1           image5

and translationChains:

imageID    sourceLang    targetLang
------------------------------------
   1           2            3
   2           4            1
   3           5            1
   4           4            2
   5           1            4

now I have two options: I want either to choose all the images the wasn't translated for a specific user in any language or to choose all the images for this user that wasn't translated in a specific language.

I have the following query for the first option:

"Select images.imageid, images.theimage, images.translationRating 
From images where images.userID=? And images.translated =0"

and for the second option I have:

"Select images.imageid, images.theimage, images.translationRating 
From images, translationchains  
where images.userID=? And images.translated =0 and
translationchains.imageId = images.imageid
and translationchains.targetLang = ? "

for example if i'll use the first query with user 3 I want the result to be:

2         3         0           image2
3         3         0           image3 
4         3         0           image4

and for the second query with user 3 and targerLang = 1 I want the result to be:

    2         3         0           image2
    3         3         0           image3

I want to combine this two queries into one query that will work in all cases (according to the parameters that i'll send)

how can I do it?

I was trying to send as the second parameter (translationchains.targetLang = ?) the string " " (empty string) so it will ignore this condition but it didn't work

Upvotes: 0

Views: 186

Answers (3)

echo_Me
echo_Me

Reputation: 37233

you may try this

FIRST query

      Select * From images where userID=3 And translated =0

DEMO HERE

OUTPUT:

  imageID   userId  Translated  theImage 
   2          3          0      image2
   3          3          0      image3
   4          3          0      image4

SECOND query 

   Select i.imageID, userId, Translated, theImage 
   From images i
   inner join translationchains  t
   on t.imageId = i.imageid
   where i.userID=3 And i.translated =0 
   and t.targetLang = 1

DEMO HERE

OUTPUT:

 imageID    userId  Translated  theImage 
    2         3         0       image2
    3         3         0       image3

THE COMBINE:

 Select i.imageID, userId, Translated, theImage 
 From images i
 inner join translationchains  t
 on t.imageId = i.imageid
 where i.userID=3 And i.translated =0 
 and t.targetLang in (select targetLang from translationChains  )
                                ^^^^^-------replace this by 1 or 2 or 3 or let it like that for all

    //-- if you want change targetLang just replace it by any number
    //--like that (select 1 from translationChains  )

DEMO HERE

OBS: you could also do it in php when you select the language then you put

   $sql .= "AND t.targetLang = the_id_selected_of_language ";

Upvotes: 1

Buzz LIghtyear
Buzz LIghtyear

Reputation: 490

Try the below query

SELECT a.imageid, a.theimage , a.translationRating
FROM images a , translationchains b
WHERE a.imageId = b.imageid
AND a.translated = 0
GROUP BY b.targetLang

The above query will be group the result based on the target language

Upvotes: 0

Amit
Amit

Reputation: 1385

Try below query

SELECT images.imageid, images.theimage, images.translationRating 
FROM images
INNER JOIN translationchains ON translationchains.imageId = images.imageid
WHERE images.userID=? 
AND (
    (images.translated =1 AND translationchains.targetLang = ?) 
    OR (images.translated =0)
)

Upvotes: 0

Related Questions