#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2005
    Posts
    6
    Rep Power
    0

    How To Select Max (What Is Wrong In My Query)


    Hello I'm trying to select max price, i keep getting 0 results when i should be getting some. Any ideas on what I did wrong?

    The query should query a list of sites, find all of their ticket types and then display the most expensive ticket type only and the price of that ticket.

    Thank you for any help! I'm very new to SQL,

    Code:
    select node_code, name, tt.code, price, effective_date, district_code
    from node n, tickettype_price_set tps, tickettype_price tp, ticket_type tt, site s
    where n.id = site_id
    and s.id = n.id
    and node_type = 'backoffice'
    and tps.tickettype_price_set_id = tp.tickettype_price_set_id
    and tt.id = tickettype_id
    and tt.deleted = 0
    and node_name not like '%CLOSED%'
    --and tt.code in (0189)
    --and tt.name like '%ADRCM%'
    --and node_code in ()
    			
    			
    and price = (select max(price) 
    			from tickettype_price, node
    			where n.id = site_id
    			and s.id = n.id)
    			
    		
    		
    			
    order by node_code, name
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,031
    Rep Power
    4210
    hello Johnathan

    please indicate which table each of the columns belongs to that is marked in red ...
    Code:
    select node_code, name, tt.code, price, effective_date, district_code
    from node n, tickettype_price_set tps, tickettype_price tp, ticket_type tt, site s
    where n.id = site_id
    and s.id = n.id
    and node_type  = 'backoffice'
    and tps.tickettype_price_set_id = tp.tickettype_price_set_id
    and tt.id = tickettype_id
    and tt.deleted = 0
    and node_name  not like '%CLOSED%'
    and price  = (select max(price) 
    			from tickettype_price, node
    			where n.id = site_id
    			and s.id = n.id)		
    order by node_code, name
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo