CODEWITHSUNDEEP
mysqlsql
newbie
newbie

Reputation: 75

SQL muliple SELECT statements in VIEW

I am looking at creating a view from several tables which requires 5 columns. To create each column I need to perform various calculations and exceptions using the WHERE clause. for example

CREATE VIEW test
       AS SELECT date1 as 'YEAR' 
       FROM booking 
       WHERE bookingDate BETWEEN '2010-01-01' and '2014-12-31'

this column works perfectly, but now i want to add a column next to it in the same view with a different WHERE clause. How can I add several SELECT statements where i can specify where boundaries for each column individually and also use different tables to do so?

Sample data:

Table 1 
-------------------
BookingID | date
1001      | 2010-01-23
1002      |  2010-02-23
------------------------

Table 2
-----------------------
ProfitID | BookingID| Profit
44       | 1001     | £250
45       |  1002    | £200
----------------------------

Create VIEW
--------------------------
YEAR | Profit
2010 |  £450

Upvotes: 0

Views: 48

Answers (3)

Manju
Manju

Reputation: 34

CREATE view test

    AS

    SELECT
        tst1.First,tst2.Second
        FROM (SELECT
                  date1 AS 'Year1',ROW_NUMBER() OVER(ORDER BY date1) AS RowNumber
                  FROM Table_1
                  WHERE  bookingDate BETWEEN '2010-01-01' and '2014-12-31'
             ) tst1
            LEFT OUTER JOIN (SELECT
                                 date1 AS 'Year2',ROW_NUMBER() OVER(ORDER BY date1) AS RowNumber
                                 FROM Table_2
                  WHERE  bookingDate BETWEEN '2010-01-01' and '2014-12-31'
                            ) tst2 ON tst1.RowNumber=tst2.RowNumber
    go

Upvotes: 0

Ctznkane525
Ctznkane525

Reputation: 7465

I think you are looking for a GROUP BY clause to get the profit for each year:

CREATE VIEW test
AS SELECT Year(b.date) as 'YEAR', SUM(t.Profit)
   FROM booking b
       INNER JOIN Table2 t on b.BookingId=t.BookingId
   WHERE bookingDate BETWEEN '2010-01-01' and '2014-12-31'
   GROUP BY Year(date)

Upvotes: 2

Manju
Manju

Reputation: 34

Use UNION between your two select statements as below.

CREATE VIEW test
    AS SELECT date1 as 'YEAR' 
       FROM booking 
       WHERE bookingDate BETWEEN '2010-01-01' and '2014-12-31'
    UNION
       SELECT date2 as 'YEAR2' 
       FROM booking2 
       WHERE bookingDate BETWEEN '2010-01-01' and '2014-12-31'

Upvotes: 0

Related Questions