CODEWITHSUNDEEP
mysqlsqldatabase
Muchtar
Muchtar

Reputation: 122

MySQL select the condition only if row in column is duplicated

I want to select all LID which fulfill the selected LID. I have a table named LNZ for example like this:

+------------+
|    LNZ     |
+-----+------+
| NID |  LID |
+-----+------+
|  1  |    1 |
|  1  |    2 |
|  1  |    3 |
|  2  |    1 |
|  2  |    3 |
|  3  |    1 |
|  4  |    1 |
|  4  |    2 |
+-----+-------+

What I want is, to select all entries which contains the NID 1 and 4 in this case. But it could be more than two NID selected.

For the NID 1 and 4 the output should be:

+------------+
|    LNZ     |
+-----+------+
| NID |  LID |
+-----+------+
|  1  |    1 |
|  1  |    2 |
|  4  |    1 |
|  4  |    2 |
+-----+-------+

If I do this 

SELECT  NID, LID
FROM    lnz
WHERE   NID = 1 OR NID = 4;

I get this wrong result:

+------------+
|    LNZ     |
+-----+------+
| NID |  LID |
+-----+------+
|  1  |    1 |
|  1  |    2 |
|  1  |    3 |
|  4  |    1 |
|  4  |    2 |
+-----+-------+

It works with following SQL statement, but it isn't variable with the required NID 

SELECT T1.NID, T1.LID
FROM LNZ AS T1
JOIN LNZ AS T2 ON T1.LID = T2.LID
WHERE T1.NID = 1 AND T2.NID = 4
   OR T2.NID = 1 AND T1.NID = 4;

My question now is... How can I change this statement, to make it variable with the number of selected NID?

Feel free to ask if you don't know what I mean.

Upvotes: 0

Views: 592

Answers (3)

Oto Shavadze
Oto Shavadze

Reputation: 42863

@NemanjaPerovic offered nice solution, this solution looks like it, difference is that you can count NID's here, instead of language/application level. Something like this:

select * from LNZ 
where NID in(1,2,4)
and
lid in(
    select lid from LNZ 
    where NID in(1,2,4)  
    group by lid
    having count(*) = (select count(distinct NID) from LNZ where NID in(1,2,4) )
)

Upvotes: 0

StanislavL
StanislavL

Reputation: 57421

SELECT  T1.NID, T1.LID
FROM    LNZ AS T1
    JOIN (SELECT  LID
          FROM    lnz
          WHERE   NID = 1 OR NID = 4
          group by lid
          having count(*)>1) sub ON t1.lid=sub.lid
WHERE   (T1.NID = 1 OR T1.NID = 4)

Upvotes: 0

NP3
NP3

Reputation: 1264

If you try 

SELECT * FROM LNZ
WHERE NID IN (1,2,4)
AND LID IN (
    select LID from LNZ
    WHERE NID IN (1,2,4)
    GROUP BY LID
    HAVING COUNT(*) = 3
)

where in your language that you use to build the query you dynamically set the (1,2,4) to the values of NID you want and you set the count (based on the number of NIDs you want, in my example it is 3), it should work

Upvotes: 2

Related Questions