There is a table of the form

> Id | gId | cId | pId > 1 | 0 | 10 | 1 > 2 | 2 | 0 | 1 > 3 | 3 | 0 | 1 > 4 | 4 | 0 | 2 > 5 | 0 | 11 | 2 > 6 | 4 | 0 | 2

What kind of query can we get the pId value from it if gId and cId are given, for example, gId: 2 or 3 or 4 or 5 or 6, and cId 10, you need to get pId 1, or gId: 2 or 3 or 4 or 5 at the output 6, and cId 11, at the output you need to get pId 2

  • is the condition written right? >> get the pId value if gId is set AND cId >> and >> the first one does not return anything, because there are no records containing cId and gId at the same time. Go ahead. >> gId: 2 or 3 or 4 or 5 or 6, and cId 10, you need to get pId 1 at the output. If there are no records containing cId and gId at the same time, then we get rubbish. - teanYCH
  • nothing is clear. if pId is determined by cId, then why the condition on gId? - Yura Ivanov


4 answers 4

If I correctly understood the condition then:

 select pId from table t join (select gId from table where gId in (2,3,4,5)) as t1 on t.gId = t1.gId join (select cId from table where cId = 11) as t2 on t.cId = t2.cId
  • yes here it is possible without subqueries. The author said that "there are no records containing cId and gId at the same time", therefore two elementary conditions are possible - either one or the other. - teanYCH
  • The request did not return anything. The request receives data gId: 2 or 3 or 4 or 5 or 6, and cId 10 as a result you need to get pId: 1 (these are the first 3 columns). - Sharp - eyed
  • Now it returns pId = 1 select pid from test where gId in (2,3,4,5,6) intersect select pid from test where cId = 10 Well, and then the matter of technology) - null
  • Strange, gives error # 1064 - You have an error in your SQL syntax; MySQL server version for the right syntax to use near 'intersect SELECT - Sharp - Sighted
  • Well, as if in the labels to the question it was not indicated what MySQL needed, so I wrote on the fact that it is closer to me))) but now I offer this option: select distinct pId from test where gId in (2,3,4,5,6 ) and pId in (select pId from test where cId = 10) - null 2:22 pm
 SELECT pId FROM table WHERE cId='11' AND gId IN (2,3,4,5,6) или SELECT pId FROM table WHERE cId='11' OR gId IN (2,3,4,5,6)
  • one
    Both requests are not correct, the first one will not return anything, because there are no records containing cId and gId at the same time, the second one will return all records in general ... - Sharp- eyed
 SELECT pId FROM table WHERE (cId = '11' and gId = '0') or (cId = '0' and gId IN (2,3,4,5,6)

Of course, if I correctly understood the condition and compared this knowledge with the answers and comments ...

  • Invalid request, it is completely analogous to 2 requests from the answer above. It is necessary to choose pId from the table where cId = 11 and gId IN (2,3,4,5,6), but since there are no whole pairs, there is a difficulty with sampling. - Sharp - eyed
  • in fact, it is not entirely similar. The first condition is satisfied if gId is empty, and the second condition is satisfied if cId is empty. In the answer @slowpokewarrior it is not. Ps. You are keen after all - you should have noticed PPS. and generally need to put the question correctly. then there will be no mess in the answers. - teanYCH
  • @ Sharp-sighted, do not agritsya and build from themselves a newfag. The question was not clear to me alone. People were able to figure it out only after some time, what as the BE says ... PS. Your bydloknoment even managed to dislay - teanYCH

Come on? The answer, marked with the correct will not return what you want to specify.

There was a similar question, and there I gave my answer, it is easy to remake it for this question: How to make about the following condition in ('AUDI' and 'FIAT')