Dear All,

I'm fighting with some queries but i can not find a solution for my SQL query.Here it is :

I have two tables :

select * from table1 where (brand = "Sony")

select * from table2 where (name = 'john')

For example first query returns 5 records and second query returns 6 records.I want if
table1.surname = table2.surname
and
table1.number = table2.number
and
table1.telno = table2.telno

then i want a query that returns records from table2 except if the table1 has records too.For example :

Table 1
---------
* Fields
id, number, name, surname, brand, telno
* Values
1, 6, John, Deere, Sony, 5551042
2, 5, John, Seere, Hitachi, 5551000
3, 4, John, Weere, Hyundai, 555241
4, 1, Jack, Teere, Samsung, 555241
5, 1, Jack, Reere, Philips, 555241
6, 2, Jack, Leere, Sharp, 555241

Table 2
---------
* Fields
id, number, name, surname, brand, telno
* Values
4, 1, Mickey, Teere, Samsung, 555241
5, 1, Pluto, Reere, Philips, 555241

table1.surname = table2.surname
and
table1.number = table2.number
and
table1.telno = table2.telno

then i want a result that :

Result
---------
1, 6, John, Deere, Sony, 5551042
2, 5, John, Seere, Hitachi, 5551000
3, 4, John, Weere, Hyundai, 555241
4, 1, Mickey, Teere, Samsung, 555241
5, 1, Pluto, Reere, Philips, 555241
6, 2, Jack, Leere, Sharp, 555241

How can i do this with SQL?

Analyzer

I don't get why you can't merge the tables?

If you using mySQL you can use a MERGE type (Check the manual). They may be over solutions in other database managers, otherwise you could just add the tables together.

Table 1
---------
* Fields
id, number, name, surname, brand, telno
* Values
1, 6, John, Deere, Sony, 5551042
2, 5, John, Seere, Hitachi, 5551000
3, 4, John, Weere, Hyundai, 555241
Table 1
---------
* Fields
id, number, name, surname, brand, telno
* Values
1, 6, John, Deere, Sony, 5551042
2, 5, John, Seere, Hitachi, 5551000
3, 4, John, Weere, Hyundai, 555241
4, 1, Mickey, Teere, Samsung, 555241
5, 1, Pluto, Reere, Philips, 555241
6, 2, Jack, Leere, Sharp, 555241

Well, i'm using PostgreSQL and i couldnt merge tables with INNER JOIN.Could you tell me how to write its query please?

Analyzer

Hi there,
It is strange indeed not to merge both table's.
But anyway here is a solution:
In standard ansii SQL you can use EXCEPT in stead of
UNION.
Except shows only those records of table 1 that doesn't exist in table 2.

SELECT *
FROM table 1
EXCEPT
SELECT *
FROM table 2;

You can do the trick twise and combine the output using a UNION.

succes
Robert

Hmm, ok thank you very much for your help.I combined except+union and solved.Thank you!

Analyzer