SQL HELP

[wiskoththa]

mata tables 2ka duplicate values identify karaganna query ekak denawada..



eg:

tbl1
------

ID     Phone#
-----------------------
1      777666555
2      777665544
3      777666555
4      777665544

tbl2
------

ID     Phone#
-----------------------
5      777666555
6      777888888
7      777999999
8      777888888

query eken result eka me widihata mata enna ona

Phone#            ID           tbl name
--------------------------------------------
777666555      1             tbl1
777666555      3             tbl1
777666555      5             tbl2
777665544      2             tbl1
777665544      4             tbl1
777888888      6             tbl2
777888888      8             tbl2

help ekak denna danna kenek

 

[wiskoththa]

help

 

[mjayanatha]

mata tables 2ka duplicate values identify karaganna query ekak denawada..



eg:

tbl1
------

ID     Phone#
-----------------------
1      777666555
2      777665544
3      777666555
4      777665544

tbl2
------

ID     Phone#
-----------------------
5      777666555
6      777888888
7      777999999
8      777888888

query eken result eka me widihata mata enna ona

Phone#            ID           tbl name
--------------------------------------------
777666555      1             tbl1
777666555      3             tbl1
777666555      5             tbl2
777665544      2             tbl1
777665544      4             tbl1
777888888      6             tbl2
777888888      8             tbl2

help ekak denna danna kenek

"SELECT TBL1.*, TBL2.* from TBL1 INNER JOIN TBL2 on TBL1.Phone# = TBL2.Phone#"

 

[wiskoththa]

"SELECT TBL1.*, TBL2.* from TBL1 INNER JOIN TBL2 on TBL1.Phone# = TBL2.Phone#"

but machan meke ekama table eke tiyena duplicates identify wenne nene

 

[wiskoththa]

bmp

 

[OCP]

select ID,PHONE#,'tbl1' as "TABLE NAME"
from tbl1
UNION

 

[OCP]

ALL
select ID,PHONE#,'tbl2' as "TABLE NAME"
from tbl2

 

[OCP]

Mama dapu part dekama ekata dala query karala balapan

 

[mjayanatha]

but machan meke ekama table eke tiyena duplicates identify wenne nene

hari machn thawa query ekak liyapan..order by ekak dala ....

 

[wiskoththa]

Mama dapu part dekama ekata dala query karala balapan

try kala machan, mata ona widihata duplicate values witarak result ekata enne nene.. okkoma data tika enawa

 

[wiskoththa]

bump

tnx

hari machn thawa query ekak liyapan..order by ekak dala ....

pls machan complete query eka dapanko, mata eka hariyata terenne ne

 

[Ayeshlive]

umbe table structure ekanam hariyata therune naha machan. onenam oka PHP walin karannath puluwan. row ekak array ekak widihata fetch karala primary key eken arrays tika hadaganin (dups hoyanna one ewa). Eeta passe array_intersect($array1, $array2, $array3,...) dammama arrays okkogema thiyena values witharak thiyena array ekak ganna puluwan. habai performance ahtin nam patta slow. Puluwantharam SQL walinma karanna balapan.

 

[Sahan shashika]

"Distinct" keyword eka use karanna

 

[Ayeshlive]

mehemath puluwan

SELECT id, phone FROM tbl1
UNION
SELECT id, phone FROM tbl2
ORDER BY id

 

[wiskoththa]

"Distinct" keyword eka use karanna

mehemath puluwan

SELECT id, phone FROM tbl1
UNION
SELECT id, phone FROM tbl2
ORDER BY id

machan mata ona karana result eke tables 2ma tiyena duplicate values identify karanawa.. man seperate colors walin dala tiyenne mage example eken identify wenna ona duplicate values...

Phone#            ID           tbl name
--------------------------------------------
[COLOR="Indigo"]777666555      1             tbl1
777666555      3             tbl1
777666555      5             tbl2[/COLOR]
[COLOR="DarkGreen"]777665544      2             tbl1
777665544      4             tbl1[/COLOR]
[COLOR="DarkRed"]777888888      6             tbl2
777888888      8             tbl2[/COLOR]

 

[♥-Aurora-♥]

create table #T
(
 id int,
 phone varchar(10),
 tblName varchar(10)
);

insert into #T
select *, 'tbl1' as tblName
from t1
union
select *, 'tbl2' as tblName
from t2

select * from #T
where phone in (select phone from #T group by phone having COUNT(*) > 1)
order by phone

drop table #T
go

ඔයාට ඕනෙ කරන උත්තරේ නම් ඕකෙන් එනවා. කරන්න ඕනෙ වැඩේට හරි යයි ද මන්දා..
අවුලක් තියේ නම් අහන්න.

 

[Bole121]

create table #T
(
 id int,
 phone varchar(10),
 tblName varchar(10)
);

insert into #T
select *, 'tbl1' as tblName
from t1
union
select *, 'tbl2' as tblName
from t2

select * from #T
where phone in (select phone from #T group by phone having COUNT(*) > 1)
order by phone

drop table #T
go

ඔයාට ඕනෙ කරන උත්තරේ නම් ඕකෙන් එනවා. කරන්න ඕනෙ වැඩේට හරි යයි ද මන්දා..
අවුලක් තියේ නම් අහන්න.

ඔව් ඔව් මේක හරි වගේ මචන්. පොඩ්ඩක් බලපන්.