SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Variable Issues - ERROR: Unknown column in 'field list'

    Hi,

    I would like to optimise the below query allowing me to use the value returned from the SUM(bookings) in extract one further on in the query which can be seen in extract two. What would be the best way to approach this?

    At present I am presented with the error: "ERROR: Unknown column 'qjump_bookings' in 'field list'"

    Many thanks in advance,

    Richard

    Extract 1

    Code MySQL:
    SELECT
    	SUM( bookings ) as qjump_bookings
    FROM
    	(
    SELECT
    	COUNT( bookings_offline.tid ) AS bookings
    FROM
    	bookings_offline,
    	events
    WHERE
    	events.id = bookings_offline.eid AND
    	bookings_offline.ticket_type LIKE '%%qjump' AND
    	bookings_offline.eid = events.id AND
    	bookings_offline.status = 'paid' AND
    	events.id = %1\$d
    UNION
    	ALL
    SELECT
    	COUNT( bookings_online.tid ) AS bookings
    FROM
    	bookings_online,
    	events
    WHERE
    	events.id = bookings_online.eid AND
    	bookings_online.ticket_type LIKE '%%qjump' AND
    	bookings_online.eid = events.id AND
    	bookings_online.status = 'paid' AND
    	events.id =  %1\$d ) as u

    Extract 2

    Code MySQL:
    SELECT
    	ROUND((events.qjump_pc /100)* venues.capacity) AS qjump_available
    FROM
    	venues ,
    	events
    WHERE
    	events.venue = venues.id AND
    	events.id =  %1\$d ) WHEN qjump_bookings IS NOT NULL THEN

    Full SQL:

    Code MySQL:
    SELECT
    	( CASE WHEN ( 
    SELECT
    	SUM( bookings ) as qjump_bookings
    FROM
    	(
    SELECT
    	COUNT( bookings_offline.tid ) AS bookings
    FROM
    	bookings_offline,
    	events
    WHERE
    	events.id = bookings_offline.eid AND
    	bookings_offline.ticket_type LIKE '%%qjump' AND
    	bookings_offline.eid = events.id AND
    	bookings_offline.status = 'paid' AND
    	events.id = %1\$d
    UNION
    	ALL
    SELECT
    	COUNT( bookings_online.tid ) AS bookings
    FROM
    	bookings_online,
    	events
    WHERE
    	events.id = bookings_online.eid AND
    	bookings_online.ticket_type LIKE '%%qjump' AND
    	bookings_online.eid = events.id AND
    	bookings_online.status = 'paid' AND
    	events.id =  %1\$d ) as u) IS NULL THEN (
    SELECT
    	ROUND((events.qjump_pc /100)* venues.capacity) AS qjump_available
    FROM
    	venues ,
    	events
    WHERE
    	events.venue = venues.id AND
    	events.id =  %1\$d ) WHEN qjump_bookings IS NOT NULL THEN ( CASE WHEN ( 
    		qjump_bookings
     > ((events.qjump_pc /100)* (
    SELECT
    	venues.capacity
    FROM
    	events ,
    	venues
    WHERE
    	events.venue = venues.id AND
    	events.id =   %1\$d ))) THEN 'Q-Jump Tickets Soldout' END ) END ) AS qjump_remaining

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you explain in words what these extracts are supposed to accomplish?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    The first extract counts the number of rows in the tables bookings_online and bookings_offline returning the total number as qjump_bookings. The second extract looks at this value and when it is not null selects data from another table.

    If you have any further questions please don't hesitate to get in touch.

    Many thanks in advance for your help.

    Richard

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try this --
    Code:
    SELECT a.qjump_available
         , b.qjump_bookings
         , a.qjump_available - b.qjump_bookings
             AS qjump_remaining
    FROM (
           SELECT ROUND(events.qjump_pc/100 *
                          venues.capacity) AS qjump_available
             FROM events
           INNER
             JOIN venues
               ON venues.id = events.venue
            WHERE events.id =  %1\$d )
           ) AS a
    CROSS
      JOIN (
           SELECT SUM( bookings ) as qjump_bookings
             FROM (
                  SELECT COUNT( bookings_offline.tid ) AS bookings
                    FROM events
                  LEFT OUTER
                    JOIN bookings_offline
                      ON bookings_offline.eid = events.id
                     AND bookings_offline.ticket_type LIKE '%%qjump' 
                     AND bookings_offline.status = 'paid' 
                   WHERE events.id = %1\$d
                  UNION ALL
                  SELECT COUNT( bookings_online.tid ) AS bookings
                    FROM events
                  LEFT OUTER
                    JOIN bookings_online
                      ON bookings_online.eid = events.id
                     AND bookings_online.ticket_type LIKE '%%qjump' 
                     AND bookings_online.status = 'paid' 
                   WHERE events.id = %1\$d
                  ) AS u
           ) AS b
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    try this --
    Code:
    SELECT a.qjump_available
         , b.qjump_bookings
         , a.qjump_available - b.qjump_bookings
             AS qjump_remaining
    FROM (
           SELECT ROUND(events.qjump_pc/100 *
                          venues.capacity) AS qjump_available
             FROM events
           INNER
             JOIN venues
               ON venues.id = events.venue
            WHERE events.id =  %1\$d )
           ) AS a
    CROSS
      JOIN (
           SELECT SUM( bookings ) as qjump_bookings
             FROM (
                  SELECT COUNT( bookings_offline.tid ) AS bookings
                    FROM events
                  LEFT OUTER
                    JOIN bookings_offline
                      ON bookings_offline.eid = events.id
                     AND bookings_offline.ticket_type LIKE '%%qjump' 
                     AND bookings_offline.status = 'paid' 
                   WHERE events.id = %1\$d
                  UNION ALL
                  SELECT COUNT( bookings_online.tid ) AS bookings
                    FROM events
                  LEFT OUTER
                    JOIN bookings_online
                      ON bookings_online.eid = events.id
                     AND bookings_online.ticket_type LIKE '%%qjump' 
                     AND bookings_online.status = 'paid' 
                   WHERE events.id = %1\$d
                  ) AS u
           ) AS b
    Many thanks for your help, I am presented with the following error:

    ERROR: Every derived table must have its own alias

    Is there also a more efficient way of implementing the following query on the end of the above other than:

    Code MySQL:
    CASE WHEN ( b.qjump_bookings > 
    	((events.qjump_pc /100)* (
    SELECT
    	venues.capacity
    FROM
    	events ,
    	venues
    WHERE
    	events.venue = venues.id AND
    	events.id = 1 ))) THEN 'Q-Jump Tickets Soldout' END ) AS qjump_remaining

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i believe the derived tables in the query i wrote all have names

    maybe you changed it around a little?

    also, i think you will see that there is nothing left to add on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    The only thing changed in the query was events.id = %1\$d to events.id = 1.

    Below is a full error log:

    Code MySQL:
    SQL query: Documentation
     
    SELECT a.qjump_available , b.qjump_bookings , a.qjump_available - b.qjump_bookings AS qjump_remaining FROM ( SELECT ROUND(events.qjump_pc/100 * venues.capacity) AS qjump_available FROM events INNER JOIN venues ON venues.id = events.venue WHERE events.id = 1 ) ) AS a CROSS JOIN ( SELECT SUM( bookings ) as qjump_bookings FROM ( SELECT COUNT( bookings_offline.tid ) AS bookings FROM events LEFT OUTER JOIN bookings_offline ON bookings_offline.eid = events.id AND bookings_offline.ticket_type LIKE '%%qjump' AND bookings_offline.status = 'paid' WHERE events.id = 1 UNION ALL SELECT COUNT( bookings_online.tid ) AS bookings FROM events LEFT OUTER JOIN b[...]
     
    MySQL said: Documentation
    #1248 - Every derived table must have its own alias

    Also looking at the query I can not see where the following is included?

    Code MySQL:
    CASE WHEN ( b.qjump_bookings >
        ((events.qjump_pc /100)* (
    SELECT
        venues.capacity
    FROM
        events ,
        venues
    WHERE
        events.venue = venues.id AND
        events.id = 1 ))) THEN 'Q-Jump Tickets Soldout' END ) AS qjump_remaining

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i can't see the end of that query, so i don't know if it's complete

    it would be incomplete if you left off the ) AS u ) AS b

    where did you test it?

    the CASE is not included, because qjump_remaining is calculated differently
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the full query used which is still returning the error:

    Nowhere can I see that if

    total bookings > (events.qjump_pc /100)*venue capacity it returns soldout?

    Many thanks once again,

    Richard
    ---

    SELECT a.qjump_available
    , b.qjump_bookings
    , a.qjump_available - b.qjump_bookings
    AS qjump_remaining
    FROM (
    SELECT ROUND(events.qjump_pc/100 *
    venues.capacity) AS qjump_available
    FROM events
    INNER
    JOIN venues
    ON venues.id = events.venue
    WHERE events.id = 1 )
    ) AS a
    CROSS
    JOIN (
    SELECT SUM( bookings ) as qjump_bookings
    FROM (
    SELECT COUNT( bookings_offline.tid ) AS bookings
    FROM events
    LEFT OUTER
    JOIN bookings_offline
    ON bookings_offline.eid = events.id
    AND bookings_offline.ticket_type LIKE '%%qjump'
    AND bookings_offline.status = 'paid'
    WHERE events.id = 1
    UNION ALL
    SELECT COUNT( bookings_online.tid ) AS bookings
    FROM events
    LEFT OUTER
    JOIN bookings_online
    ON bookings_online.eid = events.id
    AND bookings_online.ticket_type LIKE '%%qjump'
    AND bookings_online.status = 'paid'
    WHERE events.id = 1
    ) AS u
    ) AS b

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    oh, man, my bad!!

    remove the extra parenthesis here --
    Code:
            WHERE events.id =  %1\$d )
           ) AS a
    then change this --
    Code:
    SELECT a.qjump_available
    , b.qjump_bookings
    , a.qjump_available - b.qjump_bookings
    AS qjump_remaining
    to this --
    Code:
    SELECT a.qjump_available
    , b.qjump_bookings
    , a.qjump_available - b.qjump_bookings
    AS qjump_remaining
    , CASE WHEN a.qjump_available < b.qjump_bookings
    THEN 'Sold Out' END AS Message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All working many thanks once again for your help I really appreciate it!

    Working code for reference below:

    SELECT a.qjump_available
    , b.qjump_bookings
    , a.qjump_available - b.qjump_bookings
    AS qjump_remaining
    , CASE WHEN a.qjump_available < b.qjump_bookings
    THEN 'Sold Out' END AS Message
    FROM (
    SELECT ROUND(events.qjump_pc/100 *
    venues.capacity) AS qjump_available
    FROM events
    INNER
    JOIN venues
    ON venues.id = events.venue
    WHERE events.id = 1
    ) AS a
    CROSS
    JOIN (
    SELECT SUM( bookings ) as qjump_bookings
    FROM (
    SELECT COUNT( bookings_offline.tid ) AS bookings
    FROM events
    LEFT OUTER
    JOIN bookings_offline
    ON bookings_offline.eid = events.id
    AND bookings_offline.ticket_type LIKE '%%qjump'
    AND bookings_offline.status = 'paid'
    WHERE events.id = 1
    UNION ALL
    SELECT COUNT( bookings_online.tid ) AS bookings
    FROM events
    LEFT OUTER
    JOIN bookings_online
    ON bookings_online.eid = events.id
    AND bookings_online.ticket_type LIKE '%%qjump'
    AND bookings_online.status = 'paid'
    WHERE events.id = 1
    ) AS u
    ) AS b

  12. #12
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another small question..

    I am looking at also running the same query but looking up WHERE bookings_online.ticket_type LIKE 'vip%%' and returning vip_available, vip_bookings and vip_remaining aswell as qjump_available, qjump_bookings and qjump_remaining I assume this is possible in the same query?

    Many thanks,

    Richard

  13. #13
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can anyone provide any guidance in regards to merging the two queries into one?

    Many thanks in advance.

    Richard

  14. #14
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Below is the SQL I am trying to use to lookup both bookings in the same query. However, I am presented with the error:

    ERROR: Operand should contain 1 column(s)

    Code:
    SELECT
    	(
    SELECT
    	a.qjump_available ,
    	b.qjump_sold ,
    	a.qjump_available - b.qjump_sold AS qjump_remaining ,
    	CASE WHEN a.qjump_available < b.qjump_sold THEN 'Sold Out' END AS qjump_error
    FROM
    	(
    SELECT
    	ROUND(events.qjump_pc/100 * venues.capacity) AS qjump_available
    FROM
    	events
    		INNER JOIN
    		venues
    		ON
    		venues.id = events.venue
    WHERE
    	events.id = 1 ) AS a
    		CROSS JOIN
    		(
    SELECT
    	SUM( bookings ) as qjump_sold
    FROM
    	(
    SELECT
    	COUNT( bookings_offline.tid ) AS bookings
    FROM
    	events
    		LEFT OUTER JOIN
    		bookings_offline
    		ON
    		bookings_offline.eid = events.id AND
    	bookings_offline.ticket_type LIKE '&#37;%qjump' AND
    	bookings_offline.status = 'paid'
    WHERE
    	events.id = 1
    UNION
    	ALL
    SELECT
    	COUNT( bookings_online.tid ) AS bookings
    FROM
    	events
    		LEFT OUTER JOIN
    		bookings_online
    		ON
    		bookings_online.eid = events.id AND
    	bookings_online.ticket_type LIKE '%%qjump' AND
    	bookings_online.status = 'paid'
    WHERE
    	events.id = 1 ) AS u ) AS b) as qjump,
    	(
    SELECT
    	a.vip_available ,
    	b.vip_sold ,
    	a.vip_available - b.vip_sold AS vip_remaining ,
    	CASE WHEN a.vip_available < b.vip_sold THEN 'Sold Out' END AS vip_error
    FROM
    	(
    SELECT
    	ROUND(events.vip_pc/100 * venues.capacity) AS vip_available
    FROM
    	events
    		INNER JOIN
    		venues
    		ON
    		venues.id = events.venue
    WHERE
    	events.id = 1 ) AS a
    		CROSS JOIN
    		(
    SELECT
    	SUM( bookings ) as vip_sold
    FROM
    	(
    SELECT
    	COUNT( bookings_offline.tid ) AS bookings
    FROM
    	events
    		LEFT OUTER JOIN
    		bookings_offline
    		ON
    		bookings_offline.eid = events.id AND
    	bookings_offline.ticket_type LIKE 'vip%%' AND
    	bookings_offline.status = 'paid'
    WHERE
    	events.id = 1
    UNION
    	ALL
    SELECT
    	COUNT( bookings_online.tid ) AS bookings
    FROM
    	events
    		LEFT OUTER JOIN
    		bookings_online
    		ON
    		bookings_online.eid = events.id AND
    	bookings_online.ticket_type LIKE 'vip%%' AND
    	bookings_online.status = 'paid'
    WHERE
    	events.id = 1 ) AS u ) AS b) as vip,
    	events.id AS id ,
    	events.activate_sales,
    	events.fullname ,
    	events.lineup,
    	events.description,
    	events.start ,
    	events.end ,
    	events.std_price ,
    	events.vip_price ,
    	events.qjump_price ,
    	venues.name AS venue_name ,
    	venues.id AS venue_id ,
    	capacity ,
    	city ,
    	postcode
    FROM
    	events,
    	venues,
    	tickets
    WHERE
    	events.id = 1 AND
    	events.public = 'yes' AND
    	events.venue = venues.id AND
    	tickets.eid = events.id
    GROUP BY
    	events.id
    Last edited by r937; Feb 25, 2009 at 13:22. Reason: wrapped code tags around code

  15. #15
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Working SQL:

    Code MySQL:
    SELECT
    	capacity.available as capacity,
    	bookings.sold as total_sold,
    	capacity.available - bookings.sold AS total_remaining,
    	qjump.available as qjump_available ,
    	qjump_bookings.sold as qjump_sold ,
    	qjump.available - qjump_bookings.sold AS qjump_remaining ,
    	vip.available as vip_available, 
    	vip_bookings.sold as vip_sold,
    	vip.available - vip_bookings.sold AS vip_remaining ,
     
    	CASE WHEN capacity.available < bookings.sold THEN 'Sold Out' END AS error,
    	CASE WHEN qjump.available < qjump_bookings.sold THEN 'Sold Out' END AS qjump_error,
    	CASE WHEN vip.available < vip_bookings.sold THEN 'Sold Out' END AS vip_error
    FROM
    	(
    SELECT
    	ROUND(events.qjump_pc/100 * venues.capacity) AS available
    FROM
    	events
    		INNER JOIN
    		venues
    		ON
    		venues.id = events.venue
    WHERE
    	events.id = 1 ) AS qjump, (
    SELECT
    	ROUND(events.vip_pc/100 * venues.capacity) AS available
    FROM
    	events
    		INNER JOIN
    		venues
    		ON
    		venues.id = events.venue
    WHERE
    	events.id = 1 ) AS vip, 
     
    	(
    SELECT
    	ROUND(venues.capacity) AS available
    FROM
    	events
    		INNER JOIN
    		venues
    		ON
    		venues.id = events.venue
    WHERE
    	events.id = 1 ) AS capacity
     
    		CROSS JOIN
     
     
     
     
    	(
    SELECT
    	SUM( bookings ) as sold
    FROM
    	(
    SELECT
    	COUNT( bookings_offline.tid ) AS bookings
    FROM
    	events
    		LEFT OUTER JOIN
    		bookings_offline
    		ON
    		bookings_offline.eid = events.id AND
     
    	bookings_offline.status = 'paid'
    WHERE
    	events.id = 1
    UNION
    	ALL
    SELECT
    	COUNT( bookings_online.tid ) AS bookings
    FROM
    	events
    		LEFT OUTER JOIN
    		bookings_online
    		ON
    		bookings_online.eid = events.id AND
    	bookings_online.status = 'paid'
    WHERE
    	events.id = 1 ) AS u ) AS bookings,
     
     
     
     
    		(
    SELECT
    	SUM( bookings ) as sold
    FROM
    	(
    SELECT
    	COUNT( bookings_offline.tid ) AS bookings
    FROM
    	events
    		LEFT OUTER JOIN
    		bookings_offline
    		ON
    		bookings_offline.eid = events.id AND
    	bookings_offline.ticket_type LIKE '%%qjump' AND
    	bookings_offline.status = 'paid'
    WHERE
    	events.id = 1
    UNION
    	ALL
    SELECT
    	COUNT( bookings_online.tid ) AS bookings
    FROM
    	events
    		LEFT OUTER JOIN
    		bookings_online
    		ON
    		bookings_online.eid = events.id AND
    	bookings_online.ticket_type LIKE '%%qjump' AND
    	bookings_online.status = 'paid'
    WHERE
    	events.id = 1 ) AS u ) AS qjump_bookings,
     
     
    	(
    SELECT
    	SUM( bookings ) as sold
    FROM
    	(
    SELECT
    	COUNT( bookings_offline.tid ) AS bookings
    FROM
    	events
    		LEFT OUTER JOIN
    		bookings_offline
    		ON
    		bookings_offline.eid = events.id AND
    	bookings_offline.ticket_type LIKE 'vip%%' AND
    	bookings_offline.status = 'paid'
    WHERE
    	events.id = 1
    UNION
    	ALL
    SELECT
    	COUNT( bookings_online.tid ) AS bookings
    FROM
    	events
    		LEFT OUTER JOIN
    		bookings_online
    		ON
    		bookings_online.eid = events.id AND
    	bookings_online.ticket_type LIKE 'vip%%' AND
    	bookings_online.status = 'paid'
    WHERE
    	events.id = 1 ) AS u ) AS vip_bookings

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    r1ch, i don't know what to say, that ~looks~ more complicated than it should be, but hey, the #1 criterion for SQL is that it works

    good job putting that together, too, but i'd like to make one suggestion

    you're using "comma joins" in your FROM clause

    please try to get out of that habit
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •