-1

I am new to SQL programming and I am having the following problem.

I have 3 tables :

  1. tblMaschine (id PK, type as char(5))
  2. tblUse (id PK, typeOfUse varchar(255))
  3. tblmaschineUse (mid, uid both PK and each as FK to the above tables)

This is a many to many relationship. I have inserted this data in the tables:

tblmaschine

id|type
--+-----
1 |M1
2 |M2
3 |M3

tbluse

id|typeOfUse
--+---------
1 |U1
2 |U2

and

tblmaschineUse

id|type
--+-----
1 |1
1 |2
2 |1
3 |2

I want to query to find the maschine type from tblmaschine which has the both types of use in table maschineUse.

I am using this query but it returns nothing:

select m.type 
from tblmaschine as m
inner join tblmaschineUse as mu on m.id = mu.mid
inner join use as u on u.id = mu.uid
where u.typeOfUse = 'U1' and u.typeOfUse = 'U2';

What am I doing wrong?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ATsiri
  • 21
  • 2
  • On a side note: I suggest you change the column names in `tblmaschineUse`. Obviously what you call `ID` is not the table's ID, as there are duplicates. It's the tblmaschine ID, so call it `id_maschine` or the like. And the column name `type` doesn't even indicate we are dealing with an ID here. And you shouldn't mix `use` and `type`; if the table is named `use`, stay with that name and call the column something like `id_use` instead. – Thorsten Kettner May 24 '20 at 09:40
  • As to your query: Keep in mind that the where clause looks at one row at a time. That one row can never have a `typeOfUse = 'U1'` and `typeOfUse = 'U2'`, because `typeOfUse` is a single value in that row that can either be U1 or U2 or something else. You could join `tbluse` twice in order to look up two different rows, but in your case I'd rather use aggregation or `EXISTS` as explained in GMB's answer (or `IN` for that matter). – Thorsten Kettner May 24 '20 at 09:46
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Explain what you expected instead from the given code & why. "it returns nothing" is not helpful. – philipxy May 24 '20 at 10:50
  • [Select values that meet different conditions on different rows?](https://stackoverflow.com/q/477006/3404097) This is a faq. Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy May 24 '20 at 10:55

1 Answers1

1

An option uses aggregation:

select m.id, m.type
from tblmaschine m
inner join tblmaschineUse mu on mu.id = m.id
where mu.type in (1, 2)
group by m.id, m.type
having count(*) = 2

This assumes no duplicates (id, type) in tblmaschineUse (otherwise, you need having count(distinct type) = 2.

If you want to filter on the name of the type, you need another join:

select m.id, m.type
from tblmaschine m
inner join tblmaschineUse mu on mu.id = m.id
inner join tbluse u on u.id = mu.type
where u.typeOfUse in ('U1', 'U2')
having count(*) = 2

You could also use two exists subqueries:

select m.*
from tblmaschine m
where 
    exists(select 1 from tblmaschineUse mu inner join tbluse u on u.id = mu.type where u.typeOfUse = 'U1' where mu.id = m.id)
    and exists (select 1 from tblmaschineUse mu inner join tbluse u on u.id = mu.type where u.typeOfUse = 'U2' where mu.id = m.id)
GMB
  • 216,147
  • 25
  • 84
  • 135