CODEWITHSUNDEEP
mysqljoinunion
HWD
HWD

Reputation: 1629

MySQL Results From Seperate Tables And No Common Fields

I have two tables with completely different columns. For example:

A:                      B:
+-----+-------+------+  +------+------+-------+-------+
| id  | price | name |  | key | cost  | title | color |
+-----+-------+------+  +-----+-------+-------+-------+
| 123 | 10.00 | Boat |  | 456 | 12.00 | Ship  | red   |
+-----+-------+------+  +-----+-------+-------+-------+
| 124 | 8.00  | Car  |  | 457 | 5.00  | Truck | blue  |
+-----+-------+------+  +-----+-------+-------+-------+

I'm wondering if there is a way to return a single result set that maintains their separate data columns, but casts a common value to order the results by. For example, ordering by cost/price: 

[1] key = 456  cost  = 12.00  title = Ship   color = red
[2] id  = 123  price = 10.00  name  = Boat
[3] id  = 124  price = 8.00   name  = Car
[4] key = 457  cost  = 5.00   title = Truck  color = blue

Since I don't want to JOIN, and there aren't any common columns for a UNION, is there anyway to do something like this?

EDIT:

I should note that the tables in my actual scenario contain many more columns. With a Union, it sounds like I would have to explicitly map those columns to each other, which isn't exactly ideal given the number of columns...

Upvotes: 0

Views: 38

Answers (2)

ehugzies
ehugzies

Reputation: 11

All the fields are actually common Key/ID and price/cost are both numbers and title/name are both strings.

I would think the only solution here is to use the UNION operator and alias the column names to match one another and using a more generic alias name rather than cost/price, title/name.

Upvotes: 0

spencer7593
spencer7593

Reputation: 108410

To get a single resultset returned, we can use a UNION ALL set operation of two queries. The number of columns, and the datatypes of the columns need to match. We can use any expression, including literals.

SELECT v.*
  FROM ( SELECT 'key = '    AS col1
              , b.key       AS col2
              , 'cost = '   AS col3
              , b.cost      AS col4
              , 'title = '  AS col5
              , b.title     AS col6
              , 'color ='   AS col7
              , b.color     AS col8 
           FROM B b
          UNION ALL
         SELECT 'id = '     AS col1
              , a.id        AS col2
              , 'price = '  AS col3
              , a.price     AS col4
              , 'name = '   AS col5
              , a.name      AS col6
              , ''          AS col7
              , ''          AS col8
           FROM A a
       ) v
   ORDER BY v.col4 DESC

Frequently with this type of UNION ALL result, we're interested in which query returned the row, so including an additional column as a discriminator is a common pattern.

I this example, we could use col1, since that will contain 'key =' for rows from B, and 'id =' for rows from A.

Note that for the "missing" columns in A, we just used included a placeholder literal in the SELECT list to return a value for col7 and col8, so the number of columns and datatypes would match.

Upvotes: 1

Related Questions