-3

Two tables P, W are connected by Group foreign key. The bellow inner SQL returns only the table P 'names' which has only one matching 'type' in W table. The outer SQL displays name and type column values.

Basically i need to retrieve only the names(p) who has only one type (W).

The bellow statement is correct, however, i wonder if i can do it without inner SQL or any other better solution.

select p.name, w.type
from p, w
where p.name in
(
    select p.name
    from P , w 
    where p.group = w.group
    group by p.name
    having count(w.type) = 1
)
AND 
p.group= w.group
SBT
  • 5
  • 2
  • 1
    "i wonder if i can do it without inner SQL" Short answer No not possible because you need w.type also and you group by on p.name.. That requires a inner query like this or a join te get the correct data for w.type. – Raymond Nijland Mar 14 '18 at 18:47
  • Another idea would be to create a View for the inner query and then you can SELECT FROM that View. – RoboBear Mar 14 '18 at 18:54
  • Thanks @RaymondNijland, i thought the same, but may be some one has better solution – SBT Mar 14 '18 at 20:22
  • You could start to use better join syntax. Stop using the where clause for joins. – Paul Maxwell Mar 14 '18 at 22:04
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Mar 14 '18 at 22:13

1 Answers1

1

If you have only one type, it is also always the maximum (and minimum) value. So you can simplify the retrieval of this value without an outer query:

select p.name, max(w.type) as type
from p
join w on p.group = w.group
group by p.name
having count(w.type) = 1

In 1:n-relations (e.g. one name belongs to only one group, which you have here since name is the primary key), you usually just need to group by in w; this will simplify your inner query a bit less (it saves one join compared to your original query), but is more generalizable (e.g. to different counts):

select p.name, w.type
from p, w
where p.group in
(
    select w.group
    from w 
    group by w.group
    having count(w.type) = 1
)
AND 
p.group= w.group
Solarflare
  • 10,721
  • 2
  • 18
  • 35
  • This is probably the answer the OP is looking for - however the question is phrased so badly it could be completely wrong. – symcbean Mar 14 '18 at 19:54
  • Thanks. Column 'name' is primary key in table P, not 'group'. A 'group; can be associated with many 'types'. ie, table W can have multiple combinations of 'group + type'. My requirement is to get a 'name' who has single 'group + type'. Columns name(primary key) and group are in table P. Columns group and type are in table W. I hope i explained better here – SBT Mar 14 '18 at 20:32
  • @SBT Thanks for the clarification about the primary key, I removed the uncertainty about the name being unique from the answer. As long as `name` is unique and has only one group (which is the case), the code should work and should return the same result than your query. – Solarflare Mar 14 '18 at 21:04