MySQL Complex Queries -



MySQL Complex Queries -

hey i've been killing myself trying figure out how these queries. can help me out. these tables have currently.

booking

hotel_no guest_no date_from date_to room_no

guest

guest_no guest_name city address zip_code

hotel

hotel_no hotel_name city address zip_code star

room

room_no hotel_no room_type cost

and these queries need do.

-list guests have bookings (past , present) in same hotel.

-create view vip-guest lists guests have reservations 4 star hotels or 4 star hotels

-among vips find invitee largest total remain (in term of number of days). express query view , without view

can help me out?

this should started. post on stackoverflow, need come specific questions or errors or problems. query posted in comments top.... question in itself: "i have these tables, 1 specific goal (question/result set), , tried query... gives me result or gives me error."

booking: hotel_no, guest_no, date_from, date_to, room_no guest: guest_no, guest_name, city, address, zip_code hotel: hotel_no, hotel_name, city, address, zip_code, star room: room_no, hotel_no, room_type, cost

all guests , bookings...

-- guests: select * guest; -- bookings: select * booking; select * invitee bring together booking on guest.guest_no = booking.guest_no; -- same as... select * guest, booking guest.guest_no = booking.guest_no; -- and... comments query missing grouping clause select guest_no, guest_name, count(*) booking_count invitee bring together booking on guest.guest_no = booking.guest_no grouping guest_no, guest_name; select guest_no, guest_name, count(distinct hotel_no) hotel_count invitee bring together booking on guest.guest_no = booking.guest_no grouping guest_no, guest_name having count(distinct hotel_no) = 1;

and count(distinct hotel_no) because... might have 3 bookings @ hotel , 1 @ hotel b. basic bring together give me 4 rows person. don't care how many bookings. care how many hotels. want count distinct occurrences of hotel_no per person (there's grouping by) instead of every row.

guests stars...

-- have invitee , hotel joined. bc hotel has stars. -- booking has hotel_no. so... can utilize lastly query , -- bring together in hotel star information. in -- want set filter number of stars -- looking =4 or >=4 or that. -- might want check out distinct list of names -- instead of row each booking.

number of days stayed...

-- utilize sec query. -- datediff(date_to, date_from) days_stay gives length of remain -- don't know view is. -- top length go 2 ways... either order , limit if there -- 1 person top length (let's 10 days). if there many people -- have stayed 10 days, you'll need max on days_stay , either bring together -- in or utilize in nested select.

this assumes there single highest length of stay. 1 person stayed 10 days.

select guest_no, guest_name, datediff(date_to, date_from) days_stayed vip_guest bring together booking on vip_guest.guest_no = booking.guest_no order datediff(date_to, date_from) desc limit 1,1

this should work many... (i'm not testing these... kind of looking @ it)

select distinct guest_no, guest_name, datediff(date_to, date_from) max_stay vip_guest bring together booking on vip_guest.guest_no = booking.guest_no datediff(date_to, date_from) = ( select max(datediff(date_to, date_from)) days_stayed booking )

the nested query gets maximum remain length of everyone. vip_guest , bookings joined give invitee , date imfo. bookings every vip_guest. want filter downwards remain lengths == max remain length. in case person had multiple 10 day stays (my arbitrary max remain length)... utilize distinct.

now... thats point nested query. don't know in view. possible none of max vip guests had remain long max remain length. in case, query homecoming nothing.

mysql

Comments

Post a Comment

Popular posts from this blog

Delphi change the assembly code of a running process -

Delphi change the assembly code of a running process - i'm trying alter address 00741fa5 has push test.009e721c . alter push test.009e71c8 . procedure callback; asm force $9e71c8 end; procedure tform2.btn1click(sender: tobject); var ppid :dword; pprocess : cardinal; begin getwindowthreadprocessid(findwindow(nil,pchar('test')), @ppid); pprocess := openprocess(process_all_access, false, ppid); injectasm(pprocess, $741fa5, 10, integer(@callback), 0); end; here injectasm function (with error checking) function injectasm(process: longword; injectaddress, injectsize, codeaddress, codesize : integer): pointer; var csize, rsize : integer; replaced : array of byte; jmp : array [0..4] of byte; jmpaddress : integer; nopv : byte; : integer; nbr: ulong_ptr; begin //injectsize must equal or greater 5, because need space our //(far) jmp wwxxyyzz instruction //if there's no space, inject in place of few instructions if injectsize ...
Read more

json - Hibernate and Jackson (java.lang.IllegalStateException: Cannot call sendError() after the response has been committed) -

json - Hibernate and Jackson (java.lang.IllegalStateException: Cannot call sendError() after the response has been committed) - hello have problem hibernate , jackson. have 2 pojos @entity @table(name = "users") public class user implements serializable { static final long serialversionuid = 4235637833262722256l; @id @generatedvalue private int id; @column(name = "creation_date") private date creationdate; @column(name = "first_name") private string firstname; @column(name = "last_name") private string lastname; @version @column(name = "version") private int version; @column(name = "e_mail") private string email; @column(name = "enabled") private boolean enabled; @column(name = "login") private string login; @column(name = "password") private string password; @jsonmanagedreference("role") @onetomany(mappedby = "user", cascade = cascadetype.all,fetch = fetchtyp...
Read more

C++ 11 "class" keyword -

C++ 11 "class" keyword - i've started using c++ 11 little bit more , have few questions on special uses of class keyword. know it's used declare class, there's 2 instances have seen don't understand: method<class t>(); and class class_name *var; why precede typename keyword class in first example, , keyword pointer in sec example? that known elaborated type specifier , necessary when class name "shadowed" or "hidden" , need explicit. class t { }; // love of god don't t t; t t2; if compiler smart, give these warnings: main.cpp:15:5: error: must utilize 'class' tag refer type 't' in scope t t2; ^ class main.cpp:14:7: note: class 't' hidden non-type declaration of 't' here t t; ^ if class not defined, deed forwards declaration. for example: template <typename t> void method() { using type = typename t::value_type; } method...
Read more