Hi... I have tried all night to figure out how to create a query that will take two tables and result in rows which contain all of the data from both tables, but merge rows where a certain column in one table matches a certain column from the other.

As an example:

table one has 4 columns
id, descript, qty, code

there are four rows of data in it -- all different id numbers

Table two has 2 columns
id, qty

there are 2 rows of data in it -- one has the same id as an item in table one, the other has an id that is not in table one.

I want the result to be columns which contain all the column names from both tables -- 5 rows total... (the matching ids should merge into one row) and the places where there are no matches have empty columns where data was not found.

The best I have been able to do is get 4 rows where the one id match merged (Left Join) and the one row in table two which had an id that did not match table one did not get listed.

can anyone suggest a solution?

thanks

GF

    you will need an OUTER JOIN for this (the left join only does half the job) - and make sure to give aliases to rows with the same name (qty) - except the row over that you join. hope that helps!
    sid

      I originally tried to use an OUTER JOIN but I don't know if the syntax I was using was wrong or what... I could not get it to work (from a syntax example I found in one of my books)... so I went to mysql.com and tried to find it... and they only seem to show LEFT and RIGHT JOIN... mentioning "OUTER" only as a description of the LEFT and RIGHT.

      is OUTER JOIN an old mysql command that was replaced by LEFT and RIGHT? or do you know the syntax or a link to a page where i can find it?

      Thanks

      GF

        A join won't get you five rows no matter how you phrase it. What you want is probably an ugly UNION, I didn't test this but it might work on MySql 4.x (not on 3.2x):

        -- first select the 4 rows from table1
        -- UNION that with the one row from table2 not in table1

        select t1.id, t1.descript, t1.qty, t1.code
        from table1 t1

        UNION
        select t2.id, NULL, t2.qty, NULL
        from table2 t2 left join table1 t1
        on t2.id = t1.id
        where t1.id is null

          Unfortunatly I think my hosting company is using 3.2.

          is there any alternative or do i have to get them to upgrade?

            3.2x doesn't support UNION. You could do it with two queries (each half of the UNION I posted). Or you can do it in php script, just read everything from both tables separately and use the id value as the key into an associative array. Duplicates will disappear because they will have the same key value, so you'll end up with an array containing all the unique values.

              Write a Reply...