Topic: Help merging 3 tables using lowest price

[a_bains]

Hello,

I have 3 tables consisting of products from suppliers, I am trying to merge all my supplier tables into one table. In the merged products table every product should be unique (all having different mfgpartno). Some suppliers carry the same product having the same mfgpartno as another supplier. The merged products table needs to only have the product containing the lowest price of all the 3 tables. In example, I need the entire row of the product with the lowest price between all 3 tables in the merged table. Here is an example based on the table below, the product with mfgpartno HA-MODEMCOM from table #3 would end up in the merged products table because it has the lowest price of all.

Code: [Select]

Table #1
+----------------------+--------------+--------------+-------------------------------+-------+------+-------+
| mfgpartno            | manufacturer | vendorpartno | description                   | price | msrp | stock |
+----------------------+--------------+--------------+-------------------------------+-------+------+-------+
| HA-MODEMCOM          | Asrock       |       HA-MOD | ASROCK AMR MODEM/COM PORT     |   6.3 |    0 | 0     |
| DUAL LOW PROFILE BRA | Asus         |       DUAL-L | LOW PROFILE BRACKET SUPPORT   |   9.6 |    0 | A     |
| AS/IO-PLATE          | Asus         |       IO-PLA | ASUS UNIVERSAL IO PLATE       |  12.6 |    0 | A     |
| 13-010041000         | Touch        |       K8N-DR | ASUS CPU RETENTION MODULE FOR |  16.1 |    0 | 0     |
| 319110               | Touch        |       SILVER | ARCTIC SILVER 5 HEAT COMPOUND |  28.6 |    0 | A     |
+----------------------+--------------+--------------+-------------------------------+-------+------+-------+

Table #2
+----------------------+--------------+--------------+-------------------------------+-------+------+-------+
| mfgpartno            | manufacturer | vendorpartno | description                   | price | msrp | stock |
+----------------------+--------------+--------------+-------------------------------+-------+------+-------+
| HA-MODEMCOM          | Asrock       |       HA-MOD | ASROCK AMR MODEM/COM PORT     |   9.6 |    0 | 0     |
| DUAL LOW PROFILE BRA | Asus         |       DUAL-L | LOW PROFILE BRACKET SUPPORT   |  43.6 |    0 | A     |
| AS/IO-PLATE          | Asus         |       IO-PLA | ASUS UNIVERSAL IO PLATE       |  51.6 |    0 | A     |
| 13-010041000         | Touch        |       K8N-DR | ASUS CPU RETENTION MODULE FOR |  26.1 |    0 | 0     |
| 319110               | Touch        |       SILVER | ARCTIC SILVER 5 HEAT COMPOUND |  18.6 |    0 | A     |
+----------------------+--------------+--------------+-------------------------------+-------+------+-------+

Table #3
+----------------------+--------------+--------------+-------------------------------+-------+------+-------+
| mfgpartno            | manufacturer | vendorpartno | description                   | price | msrp | stock |
+----------------------+--------------+--------------+-------------------------------+-------+------+-------+
| HA-MODEMCOM          | Asrock       |       HA-MOD | ASROCK AMR MODEM/COM PORT     |   4.2 |    0 | 0     |
| DUAL LOW PROFILE BRA | Asus         |       DUAL-L | LOW PROFILE BRACKET SUPPORT   |  63.6 |    0 | A     |
| AS/IO-PLATE          | Asus         |       IO-PLA | ASUS UNIVERSAL IO PLATE       |  41.6 |    0 | A     |
| 13-010041000         | Touch        |       K8N-DR | ASUS CPU RETENTION MODULE FOR |  26.1 |    0 | 0     |
| 319110               | Touch        |       SILVER | ARCTIC SILVER 5 HEAT COMPOUND |  38.6 |    0 | A     |
+----------------------+--------------+--------------+-------------------------------+-------+------+-------+


[dreamwest]

It wont work you need a prefix to seperate the row names eg table 1,2 & 3 all have the same row names

Then:

Code: [Select]

SELECT * FROM table1 as a, table2 as b, table3 as c ORDER BY c.price desc