CODEWITHSUNDEEP
sqlsql-serversubqueryunion
Tomnibus
Tomnibus

Reputation: 3

SQL Server : create a view with Union with data from first query

My SQL is quite rusty, so much that I have not created a view before and I am not entirely sure how to do what I need. Perhaps I need a stored procedure. Here is the deal.

We have a a database of ticket history (purchases). We want to filter on a certain SKU, but we want all line items from each ticket that has that SKU. For isntance, Someone buys a shirt and a hat. I want to filter on the shirt to find everyone who wants a shirt but display the entire ticket showing the shirt and the hat.

I thought my query would be something like this but I don't think it would work.

select 
    ticket_id, post_date, qty_sold, total_price, sales_total
from 
    ticket_history 
where 
    sku = 'xxxx'

Union

select 
    sku as trans_sku, qty_sold as trans_qty_sold, desc as trans_desc, total_price as trans_total_price
from 
    ticket_history 
where 
    ticket_id = <the ticket id in first query>

Perhaps a sub-select is what is needed but I'm not too understanding of how to do that either.

Any suggestions would be great.

Upvotes: 0

Views: 90

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

I am not sure what you are trying to do here and whether UNION is what you are looking for or not.

In your query the columns are different and doesn't matched between the two queries. Any way, you can use a Common table Expression so that you can reuse the subquery, this should solve your problem:

WITH FirstQuery
AS
(
   select
     ticket_id,
     post_date, 
     qty_sold,
     total_price, 
     sales_total
   from ticket_history 
  where sku = 'xxxx'
)
SELECT * 
FROM FirstQuery
UNION
SELECT 
  ... -- You should select the same number of columns
  ... -- and with the same data types to match the first columns
from ticket_history 
where ticket_id IN(SELECT ticket_id FROM FirstQuery);

Here the FirstQuery acts like a subquery, but here you can reuse it later like what we did and use it in the where clause.

But, again the columns you selected in the first query:

ticket_id,
 post_date, 
 qty_sold,
 total_price, 
 sales_total

are different than the columns you selected in the second query:

sku as trans_sku, 
qty_sold as trans_qty_sold, 
desc as trans_desc, 
total_price as trans_total_price

These columns should be matched (the count of them and data types). Otherwise you will got an error.

Things to note about UNION:

  • the columns count should be the same between the two queries.
  • The columns' names are driven from the first query.

Upvotes: 1

jarlh
jarlh

Reputation: 44746

When doing a UNION, the selected columns must match between the two select's. (Same number of columns, and matching data types.)

Maybe you want a self join instead?

select th1.ticket_id, th1.post_date, th1.qty_sold, th1.total_price, th1.sales_total,
       th2.sku as trans_sku, th2.qty_sold as trans_qty_sold,
       th2.desc as trans_desc, th2.total_price as trans_total_price
from ticket_history th1
  left join ticket_history th2 on th2.ticket_id = th1.ticket_id
where th1.sku = 'xxxx'

LEFT JOIN to get th1 rows even if there are no matching th2 row.

Upvotes: 0

Related Questions