sort same field from different tables

Question

I have somes tables (around 20) with the same structure and I'm trying to sort them with a php script and insert them in a new table with the cheapest price in cheapest1, then cheapest2 for more expensive... and the most expensive in column cheapest20:

table A: id
name
price

table B: id
name
price

table X: id
name
price

tableResult: id
name
cheapest1
price1
cheapest2
price2
...
cheapestX
priceX

My code so far is:

(SELECT id, price, name FROM tableA WHERE id = $id)
UNION
(SELECT id, price, name FROM tableB WHERE id = $id)
ORDER BY price ASC

I have been looking for different solutions but it takes too long to SELECT for 15000 rows so I guess there is another way to do it. I haven't looked for the update query yet, I need to fix the select in the first time.

Any suggestion?

EDIT: clarified question, with more tables

EDIT2: solution

I finally got it right. This is the query to select the cheapest: I select each id and I browse:

(SELECT price AS P1, name, id FROM tableA WHERE id = ?) UNION (SELECT price AS P1, name, id FROM tableB WHERE id = ?) UNION (SELECT price AS P1, name, id FROM tableC WHERE id = ?) ORDER BY P1 ASC

Then I Insert in the new table as glglgl suggested:

('INSERT INTO table (id, name, Position, price) VALUES (?, ?, ?, ?) ');

score 2 · Answer 1 · answered Jun 01 '12 at 21:30

I did this on Oracle, but syntax should be very similar for MySQL (the select should work without any changes at all).

CREATE TABLE tableA (NAME VARCHAR2(100), price FLOAT);
CREATE TABLE tableB (NAME VARCHAR2(100), price FLOAT);

INSERT INTO tableA VALUES ('a',14.23);
INSERT INTO tableA VALUES ('b',15.23);
INSERT INTO tableA VALUES ('b',16.23);

INSERT INTO tableB VALUES ('a',12.23);
INSERT INTO tableB VALUES ('a',13.23);
INSERT INTO tableB VALUES ('b',9.23);

SELECT name
     , max(mxprice) mxprice
     , min(mnprice) mnprice
FROM
(
  SELECT name
       , max(price) mxprice
       , min(price) mnprice
    FROM tableA
    GROUP BY NAME
  UNION ALL
  SELECT name
       , max(price) mxprice
       , min(price) mnprice
    FROM tableB
    GROUP BY NAME
) a
GROUP BY NAME

Result:

    NAME    MXPRICE MNPRICE
1   a       14.23   12.23
2   b       16.23   9.23

That's exaclty what I want but this works only with 2 tables (min/max). I should have 20 tables at the end so my purpose was to order them from the cheapest to the most expensive for example. — remyremy, Jun 02 '12 at 09:16

score 2 · Accepted Answer · edited May 23 '17 at 12:11

2

If you have control over the final structure of the tables: Don't do that. Instead, use only one table and add a field for indicating which purpose it serves.

The target table is not structured well either. Instead, you should use

tableResult:
 id
 name
 cheapestorder
 cheapest
 price

which makes all easier.

Thus, instead of having one row containing

id=10, name=foo, cheapest1=a, cheapestprice1=10, cheapest2=b, cheapestprice2=13,

you have several rows

id=10, name=foo, cheapestorder=1, cheapest=a, cheapestprice=10
id=10, name=foo, cheapestorder=2, cheapest=b, cheapestprice=13

(This process is called "normalization" in database theory.)

Putting all input tables into one simplifies dcp's query:

SELECT name,
       max(mxprice) mxprice,
       min(mnprice) mnprice
FROM
(
  SELECT name,
       max(price) mxprice,
       min(price) mnprice
    FROM tableABC
    GROUP BY NAME, tbltag
) a
GROUP BY NAME

or maybe even just

SELECT name,
       max(price) mxprice,
       min(price) mnprice
    FROM tableABC
    GROUP BY NAME

.

edited May 23 '17 at 12:11

Community

1
1

answered Jun 02 '12 at 10:36

glglgl

89,107
13
149
217

Indeed, I can create the table I need, I have total control. What should cheapestorder contain? How do I get it? Because I need info about each "ranking" of each table for each row. – remyremy Jun 02 '12 at 11:56
@glglgl - Nice job of stealing my query. – dcp Jun 02 '12 at 20:38
@dcp yes - but under changed preconditions. – glglgl Jun 02 '12 at 21:23
@glglgl - It's bad sportsmanship and poor manners, especially since you didn't even bother upvoting the original answer you stole the query from. But whatever. If you are so dog-eat-dog about rep points, you can have them. Geez. I thought this community was about respect. – dcp Jun 02 '12 at 23:09
@dcp I think you didn't get the whole point about my answer: it was the point of normalization and aggregation of input and output tables which you didn't even think about. – glglgl Jun 03 '12 at 06:06
@glglgl - The question was edited after I posted my answer, and I wasn't online when that happened. So you took the liberty of stealing my query, and then adjusting it to the edited question. And yes, I would have thought of normalization, but original question had only 2 tables in it. Had I had a chance to see the edited question, I could have revised my answer based on the edited question. But as I said, you can have the freaking rep points. What a jerk! It's people like you that make me want to just quit answering questions and helping people. – dcp Jun 03 '12 at 11:38
You seem to forget: It is not up to me which answer the OP accepts. So I can nothing do about the points. And if I take your query as a template and **improve it** - what is the problem? I even left you a courtesy, so what's up? I think it is time to calm down again. – glglgl Jun 03 '12 at 12:11
1

@glglgl - Fair enough. as I said I don't care about the rep points, my main goal when I answer a question is to help someone solve a problem. I didn't see the reference to my query you added in your edited answer, so I appreciate that, and I'm fine with it now. Thanks. – dcp Jun 03 '12 at 12:15

sort same field from different tables

2 Answers2