Over complicating view statement in postgresql

Question

This is my view, I'm trying to output 3 columns called 'flightid', 'cancelledseats' and 'reservedseats'. But when I execute it, it executes too much data. I only want one row not 15.

CREATE VIEW check_flight_status AS
SELECT
    (SELECT flightid FROM FLIGHTBOOKING WHERE flightid=111),
    (SELECT COUNT(flightID) FROM FLIGHTBOOKING
    WHERE status='c' AND flightid=111) AS cancelledseats, 
    (SELECT COUNT(flightid) FROM FLIGHTBOOKING 
    WHERE status='r' AND flightid=111) AS reservedseats
FROM flightbooking
INNER JOIN flight
ON flightbooking.flightid=flight.flightid;

(I'm not able to show you my tables without taking a screenshot because you'll not be able to understand it in formatted text as the layout messes up. However, I did try)

Flight table

Flightbooking table

The expected output is to have 3 columns (flightid, cancelledseats and reservedseats) and one row with data 111,1,0.

EDIT

I've just fixed my problem!

CREATE VIEW check_flight_status AS
SELECT
    (SELECT flightid FROM FLIGHTBOOKING WHERE flightid=111),
    (SELECT COUNT(flightID) FROM FLIGHTBOOKING
    WHERE status='c' AND flightid=111) AS cancelledseats, 
    (SELECT COUNT(flightid) FROM FLIGHTBOOKING 
    WHERE status='r' AND flightid=111) AS reservedseats
FROM flightbooking
INNER JOIN flight
ON flightbooking.flightid=flight.flightid
WHERE flight.flightid=111;

By adding WHERE flight.flightid=111; to the end, it only outputs one row. However, do you believe I'm over complicating my view? Could it be done simpler?

edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots — user330315
– user330315, Commented Apr 11, 2017 at 21:03
as @a_horse_with_no_name mentions posting sample data and expected output would help greatly. The structure of the tables would also help as to be honest unsure why you currently use the subqueries, they seem unnecessary in this instance. — Peter
– Peter, Commented Apr 11, 2017 at 21:41
Also: (SELECT flightid FROM FLIGHTBOOKING WHERE flightid=111) is useless and can be replaced with 111 — user330315
– user330315, Commented Apr 11, 2017 at 21:42
Please read meta.stackoverflow.com/questions/285551/… and the accepted answer — user330315
– user330315, Commented Apr 11, 2017 at 21:42
@J.Doe I had already provided a solution indicating this function of the WHERE clause in the problem before you added your edit. Also, I had already indicated that, based on information you provided, the view is vastly overcomplicated and provided a simpler solution. Would you please accept unless there is something wrong with my response? — rg6
– rg6, Commented Apr 15, 2017 at 14:39

rg6 · Accepted Answer · 2017-04-12 00:42:09Z

As has been mentioned in the comments, the query includes elements of unnecessary complexity, and without more information about your schema, it's difficult to be sure what the problem is and what you desire here. I would suggest, however, as a first stab, that your problem with extra rows may be that you're not actually filtering results in the overall query with a WHERE clause, despite the filtration you get in your subqueries.

Try the following:

CREATE VIEW check_flight_status AS
SELECT
    flightid,
    COUNT(*) FILTER (WHERE status = 'c') AS cancelledseats,
    COUNT(*) FILTER (WHERE status = 'r') AS reservedseats
FROM
    flight
    INNER JOIN flightbooking USING (flightid)
WHERE
    flightid = 111;

Note that:

COUNT(*) is fine here. I'm assuming flightid is a primary key and cannot be NULL.
The convenient USING syntax that simplifies joins that use fields with the same name.
The use of filters in the select clause, which are one of many ways to get at the data that you want without subqueries.
The COUNT aggregates should reduce your row count to one, and the WHERE clause will limit the effect of the counts to a particular flight.

Again, hard to be sure of the query you need without a schema or information about the relevant tables, and it's hard to be sure what output you desire without a better description of your problem.

In general, if you need only one row of a set, you can add LIMIT 1 to your query, but you should not do this without a properly structured query and specific intent, and I think this is not a case where LIMIT 1 is appropriate.

Collectives™ on Stack Overflow

Over complicating view statement in postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related