Databases
StackOverflow2013
Postsdbo
POPostgreSQL select statement
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPostgreSQL select statement
    primarykey
    data
    text
    <p>Given the following tables:</p> <pre><code> shows: title | basic_ticket_price ----------------+-------------------- Inception | $3.50 Romeo &amp; Juliet | $2.00 performance: perf_date | perf_time | title ------------+-----------+---------------- 2012-08-14 | 00:08:00 | Inception 2012-08-12 | 00:12:00 | Romeo &amp; Juliet booking: ticket_no | perf_date | perf_time | row_no | person_id -----------+------------+-----------+--------+----------- 1 | 2012-08-14 | 00:08:00 | P01 | 1 2 | 2012-08-12 | 00:12:00 | O05 | 4 3 | 2012-08-12 | 00:12:00 | A01 | 2 </code></pre> <p>And an additional table: seat which contains a list of seats numbered like that of row_no in booking with an area name.</p> <p>Having grouped the booked seats using this statement:</p> <pre><code>select count(row_no) AS row_no, area_name from seat where exists (select row_no from booking where booking.row_no = seat.row_no) group by area_name; </code></pre> <p>which produces:</p> <pre><code> row_no | area_name --------+-------------- 1 | rear stalls 2 | front stalls </code></pre> <p>How can I now use the counted rows and area_name to write a single SQL statement to produce a list showing the names of shows, dates and times of performances, and the number of booked seats in each area?</p> <p>I've tried this:</p> <pre><code> select s.title, perf_date, perf_time, count(row_no) AS row_no, area_name from shows s, performance, seat where exists (select row_no from booking where booking.row_no = seat.row_no) group by area_name,s.title,performance.perf_date,performance.perf_time; </code></pre> <p>But it shows repeated rows:</p> <pre><code> title | perf_date | perf_time | row_no | area_name ----------------+------------+-----------+--------+-------------- Romeo &amp; Juliet | 2012-08-12 | 00:12:00 | 1 | rear stalls Romeo &amp; Juliet | 2012-08-14 | 00:08:00 | 2 | front stalls Inception | 2012-08-12 | 00:12:00 | 1 | rear stalls Inception | 2012-08-14 | 00:08:00 | 2 | front stalls Inception | 2012-08-14 | 00:08:00 | 1 | rear stalls Inception | 2012-08-12 | 00:12:00 | 2 | front stalls Romeo &amp; Juliet | 2012-08-14 | 00:08:00 | 1 | rear stalls Romeo &amp; Juliet | 2012-08-12 | 00:12:00 | 2 | front stalls (8 rows) </code></pre> <p>Any help with solving this would be appreciated.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMilen A. Radev
    1. USBen Crazed Up Euden
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPostgreSQL select statement
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAlso: you are missing a join condition in your second statement. It will produce a cartesion join between shows, performance and seat. Youd'd better rewrite that using `JOIN ...`
      singulars
      1. POPostgreSQL select statement
      1. USa_horse_with_no_name
    2. COplease accept my apologies, i am using psql but tagged it also mysql as i have found the majority of statements to be similar espescially the select statements.
      singulars
      1. POPostgreSQL select statement
      1. USBen Crazed Up Euden
    3. COYou should use a serial as primary key. What if you get a second room for showing films? You can't let two films happen at the same time, because they are identified by perf_date and perf_time. Or what to do, when a performance is scheduled? You have to modify multiple tables, which is a bad thing. And a minor suggestion: I wouldn't repeat the table title in column names. Else you end with perf.perf_title - perf.title would be good enough.
      singulars
      1. POPostgreSQL select statement
      1. USuser unknown
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload