Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    38

    Unanswered: SQL Query problem

    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

  2. #2
    Join Date
    Nov 2004
    Posts
    9
    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

  3. #3
    Join Date
    Apr 2004
    Posts
    38
    Well, i'm using PostgreSQL and i couldnt merge tables with INNER JOIN.Could you tell me how to write its query please?

    Analyzer

  4. #4
    Join Date
    Oct 2004
    Posts
    13

    Except

    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

  5. #5
    Join Date
    Apr 2004
    Posts
    38
    Hmm, ok thank you very much for your help.I combined except+union and solved.Thank you!

    Analyzer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •