0

How to SELECT rows from multiple tables JOINing on specific key?

I have several tables states(year) with same structure. I want to make a VIEW selecting only rows from states(year) with propertyid contained in table properties under specific user:

CREATE OR REPLACE VIEW selected_states AS
SELECT *, 2017 AS year FROM states2017
UNION ALL SELECT *, 2018 AS year FROM states2018
AS s
JOIN properties p ON s.propertyID = p.propertyID WHERE p.userID=1

getting an error

The used SELECT statements have a different number of columns

SQLfiddle: http://sqlfiddle.com/#!9/aca397c

John Peeterson
  • 71
  • 1
  • 12
  • Why do you want a VIEW? – Strawberry May 30 '18 at 10:50
  • I want that VIEW to be used in another query later. – John Peeterson May 30 '18 at 10:51
  • I would discourage this. MySQL is very poor at optimizing views. – Strawberry May 30 '18 at 10:53
  • Anyway, whatever kind of SELECT you want to use, never use `SELECT *`. Instead, always name all the columns you actually want returned - even if that's all of them. – Strawberry May 30 '18 at 10:55
  • 1
    Additionally, any time you find yourself with enumerated columns, alarm bells should start ringing. Just have a states table, with a year (or any kind of timestamp) column. – Strawberry May 30 '18 at 10:58
  • It's a very, very bad idea to have one table per year. (The only exception to this: You have a certain set of years, that will never ever change, so you will never add or remove a year.) Change this to one `states` table with a year column, just as Strawberry suggests. – Thorsten Kettner May 30 '18 at 14:16

1 Answers1

0

The structure of 

SELECT *, 2017 AS year FROM states2017

and 

SELECT *, 2018 AS year FROM states2018

are not the same. The last column of the first table is 2017 while that of the second is 2018 so they can't be united.

To fix that create a common alias for the years in both tables.

CREATE OR REPLACE VIEW selected_states AS
SELECT s.*, p.userID FROM
(SELECT states2017.*, '2017' `Year` FROM states2017
UNION ALL 
SELECT states2018.*, '2018' `Year` FROM states2018) s
JOIN properties p ON s.propertyID = p.propertyID WHERE p.userID=1;

See a DEMO on SQL Fiddle.

cdaiga
  • 4,861
  • 3
  • 22
  • 42
  • Getting error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS year FROM states2017 UNION ALL SELECT states2018.*, '2018' `Year` AS year FRO' at line 1. See http://sqlfiddle.com/#!9/aca397c/2 – John Peeterson May 30 '18 at 11:45
  • Thanks, marking your response partially correct as regards part starting with "To fix that...". For future visitors, disregard notion that The structure of `SELECT *, 2017 AS year FROM states2017` and `SELECT *, 2018 AS year FROM states2018` are not the same - this is not true, these are the same - see http://sqlfiddle.com/#!9/aca397c/7 (there is no difference if you add apostrophes and delete AS operator from my code). – John Peeterson May 31 '18 at 19:46
  • Can you come up with a query containing no FROM subqueries? Although your solution works on SQL Fiddle, it does not on my webserver due to lower MySQL version throwing error `View's SELECT contains a subquery in the FROM clause` (as discussed on https://stackoverflow.com/questions/8428641/views-select-contains-a-subquery-in-the-from-clause) – John Peeterson May 31 '18 at 20:03