query from first table, using second table relation

[biofly]

Hi Friends,

 

I have two tables, second table has relation

 

Table1:

----------

Id

----------

A01

A02

A03

A04

A05

 

Table2

-----------

sid    pid

----------

A01  A01

A02  A01

 

 

If I use Query table1.id = 'A01', I need to have A01 and A02.

If I use Query table1.id = 'A02', I need to have A01 and A02.

but If I use Query table1.id = 'A03' , need to have only A03

 

It means A01 and A02 are duplicates. I want to extract duplicates.

 

Can you help me to get SQL query?

 

Thanks,

Ashok

[fenway]

If I use Query table1.id = 'A01', I need to have A01 and A02.

If I use Query table1.id = 'A02', I need to have A01 and A02.

but If I use Query table1.id = 'A03' , need to have only A03

 

It means A01 and A02 are duplicates. I want to extract duplicates.

I don't understand.

[biofly]

Thanks for the quick response.

 

If I use....

SELECT table1.id FROM table1,table2 WHERE table1.id = 'A01'; (this not a complete SQL query)

The answer should be A01 and A02 from Table1

 

If I use....

SELECT table1.id FROM table1 WHERE table1.id = 'A02';

The answer should be A01 and A02 from Table1

 

But If I use....

SELECT table1.id FROM table1 WHERE table1.id = 'A03';

The answer should be A03 only from Table1 because in my second table, table2 has two columns, I have the following table relation...

 

 

Table2

-----------

sid    pid

----------

A01  A01

A02  A01

 

 

I am new to SQL, I will appreciate your help.

 

Thanks again

Ashok

[biofly]

I am using the following query ...

 

 

SELECT * FROM table1.id WHERE table1.id IN

(SELECT table2.sid FROM table1,table2 WHERE table1.id='A01'  AND (table2.pid = table1.id OR table2.Sid = table1.id));

 

 

 

This is working fine for my first 2 queries ie

 

If I use....

SELECT table1.id FROM table1,table2 WHERE table1.id = 'A01'; (this not a complete SQL query)

The answer should be A01 and A02 from Table1

 

If I use....

SELECT table1.id FROM table1 WHERE table1.id = 'A02';

The answer should be A01 and A02 from Table1

 

 

but for third one is not working..ie

SELECT table1.id FROM table1 WHERE table1.id = 'A03';

The answer should be A03 only from Table1

 

[fenway]

I'm sorry, I still don't understand what all of this represents.

[biofly]

SELECT * FROM table1.id WHERE table1.id IN

(SELECT table2.sid FROM table1,table2 WHERE table1.id='A01'  AND (table2.pid = table1.id OR table2.Sid = table1.id));

 

 

In the above query if I use

....WHERE table1.id='A01'... the output is 'A01' and 'A02', (this is OK for me, I also need the following output also)

 

If I use   

....WHERE table1.id='A03'...No output, In this case I need to have 'A03' output.

 

Thanks

Ashok

[biofly]

This is working correctly, but I want to use only query input ie  ......WHERE  table1.id = 'A01' .....

 

SELECT  table1.id

FROM    table1

WHERE  table1.id = 'A01'

UNION

SELECT  table2.pid

FROM    table2

WHERE  table2.pid = 'A01'

UNION

SELECT  table2.sid

FROM    table2

WHERE  table2.sid = 'A01'

 

[zenag]

can u pls show the full table structure and required output format 

[fenway]

can u pls show the full table structure and required output format 

Actually ,that's not helpful at all.

 

The problem is that I don't know what this means:

If I use   

....WHERE table1.id='A03'...No output, In this case I need to have 'A03' output.