CODEWITHSUNDEEP
sql
shorif2000
shorif2000

Reputation: 2654

sql select 1 item from list

i want to select a column from a table which can have another column reference many times.

select t1.name 
from ccp.ENTITIES t1

Non
Albania
Australia
China
Czech Republic
Egypt
Germany
Greece
Group
Hungary
India
Ireland
Italy
Luxembourg
Malaysia
Malta
Netherlands
Portugal
Romania
Spain
Turkey
UK
US

this will give me a list of names of which i want 1 row from another table

v_networks_by_lm this table holds records with column t1.name and network. i want the column network only once for each item in the list. v_networks_by_lmcan hold many t1.name

entity  name   
a   Spain
b   Spain
c   Spain
d   Spain
e   Spain
f   Spain
g   Spain
h   Germany
i   Germany
j   Germany
k   Germany
l   Germany
m   Germany
n   Germany
o   Germany
p   UK
q   Germany
r   Spain
s   Spain
t   Portugal
u   Portugal
v   Portugal
q   Portugal

from the above data which is in v_networks_by_lm i only want name returned once with any value of entity. but i want to pick the name from ENTITIES as it can be dynamic

Upvotes: 2

Views: 1826

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

I think aggregation does what you want:

SELECT MAX(n.network) as network, e.name
FROM ccp.ENTITIES e JOIN
     ccp.v_networks_by_lm n
     ON n.name = e.name
GROUP BY e.name;

Upvotes: 3

user1694674
user1694674

Reputation: 126

Sounds like you want a subquery to get the single instance of name from the table, and then you do the join against entities.

Select sub.one_of_entity_values, sub.name 
from ccp.entities e 
inner join (
    select max(entity) as one_of_entity_values, name
    from v_networks_by_lm
    group by name) sub on e.name  = sub.name

Upvotes: 1

Related Questions