Simple Join Question...

[Guest_imported]

Here is my problem: I must find the trunks in a phone switch that have the same number. The trunks' table has the following columns (I'm showing just the ones that I need):

switch trunk_number trunk_name
------ ------------ ----------

ABC 100 xxx

Then, I did the query:

SELECT a.switch, a.trunk_number, a.trunk_name, b.trunk_name
FROM trunk a, trunk b
WHERE a.switch = b.switch, a.trunk_number = b.trunk_number, a.trunk_name <> b.trunk_name

It worked, but I got the result:

switch trunk_number a.trunk_name b.trunk_name
------ ------------ ------------ ------------

ABC 100 xxx yyy
ABC 100 yyy xxx

The second line is useless, so half of the result can always be ignored. Thus, my question is: how can I make a more effcient query?

 

[blom0344]

Hello MAC_BR,

Try:

SELECT distinct a.switch, a.trunk_number
FROM trunk a, trunk b
WHERE a.switch = b.switch, a.trunk_number = b.trunk_number, a.trunk_name <> b.trunk_name T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[angiole]

Of course, the second line might be useful if you have 3 trunks, ex:
(1) xxx-yyy,
(2) yyy-zzz,
(3) zzz-www.

There are two options here,
count the number of distinct trunks per swith and trunk number, as follows:

select switch,
trunk_number,
count(distinct(trunk_name))
from trunk
group by
switch,
trunk_number
having count(distinct(trunk_name)) > 1

Or if you require a list of trunk names, use the above as a subselect for the list query as follows:

select *
from trunk a
where (a.switch,a.trunk_number) =
(select switch,
trunk_number
from trunk
group by
switch,
trunk_number
having count(distinct(trunk_name)) > 1)

Cheers






AA 8~)

 

[Guest_imported]

Thanks for the help, people. Your query is much more faster, angiole, but the problem of wasted lines persists. And I need to verify the case where exists just two trunks with the same number.

So, instead of a result:

switch   trunk_number   trunk_name
------   ------------   ----------

ABC      100            xxx
ABC      100            yyy

I want this:

switch   trunk_number   trunk_name_1   trunk_name_2
------   ------------   ------------   ------------

ABC      100            xxx            yyy

 

[angiole]

No Problem, again you're assuming you'll only ever have a maximum of 2 trunk names per branch.

If this is true, we can create the result you want pretty easily.

AA 8~)

 

[Guest_imported]

Yes, it's true angiole. I'm interested to discover just two trunks with the same number. In fact, I'm making a &quot;duplicated trunks detector&quot;.

 

[angiole]

The following will list what you need:

select switch
,trunk_number
,min(trunk_name)
,max(trunk_name)
from trunks
group by switch
,trunk_number
having min(trunk_name) <> max(trunk_name)

This will whether your trunk_name is alpha or numeric

Cheers AA 8~)

 

[Guest_imported]

Thank you very much angiole!!!
I didn't know that I could use min() and max() with strings. Your solution is simple and elegant. I have learned a lot about SQL today...

 

[angiole]

Adios AA 8~)

 

[Guest_imported]

This is what you want:
&quot;I must find the trunks in a phone switch that have the same number&quot;
Assuming the &quot;trunks&quot; (as you stated it) are uniquely identified by trunk number this is how to do it.

select switch,
trunk_name
trunk_number
from trunk
group by
switch,
trunk_name
trunk_number
having count(*) > 1