.net - making sql query to get first id of a tourist and then all of it's surcharges -


i've got these tables in database:

tourist - first table

tourist_id - primary key excursion_id - foreign key name...etc...

extra_charges

extra_charge_id - primmary key   excursion_id - foreign key    extra_charge_description

tourist_extra_charges

tourist_extra_charge_id  extra_charge_id - foreign key  tourist_id - foreign key

reservations reservation_id - primary key

..... tourist_reservations

tourist_reservation_id  reservation_id - foreign key  tourist_id - foreign key

so here example: i've got reservation reservaton_id - 27 reservation has 2 tourists tourist_id - 86 , tourist_id - 87 tourist id 86 has charges extra_charge_id - 7 , and extra_charge_id - 11; tourist id 87 has charge id - 10;

is possible make sql query , name , id of tourist , of charges

sothe output like:

tourist_id: 86, name:john, charges: 7,11

(here query made extra_charge_description of of tourists reservation_id = 27 )

select extra_charges.extra_charge_description,tourist_extra_charges.tourist_id extra_charges           inner join tourist_extra_charges on extra_charges.extra_charge_id = tourist_extra_charges.extra_charge_id   inner join tourist_reservation on tourist_extra_charges.tourist_id = tourist_reservation.tourist_id           inner join  reservations on reservations.reservation_id = tourist_reservation.reservation_idhe e           reservations.reservation_id=27

the problem here have 3 records

so if tink there way in .net first name , charges of name - please advice because i'm new .net

if understood schema correctly, should you're looking for;

select t.tourist_id, t.name,    stuff((     select cast(',' varchar(max)) + cast(tec.extra_charge_id varchar(max))     tourist_extra_charges tec     tec.tourist_id = t.tourist_id     order tec.extra_charge_id     xml path('')     ), 1, 1, '') charges tourist t;

an sqlfiddle test with.


Comments

Post a Comment

Popular posts from this blog

curl - PHP fsockopen help required -

i trying work on cloudflare api , have found in documentation. unfortunately have no idea how can use in fsockopen or through curl. here example of post request. need know how can make request below data post using curl or fsockopen curl https://www.cloudflare.com/api_json.html -d 'a=cache_lvl' \ -d 'tkn=8afbe6dea02407989af4dd4c97bb6e25' \ -d 'email=sample@example.com' \ -d 'z=example.com' \ -d 'v=agg' here link cloudflare https://www.cloudflare.com/docs/client-api.html#s4.2 try one: $ch = curl_init("https://www.cloudflare.com/api_json.html"); curl_setopt($ch, curlopt_returntransfer,1); curl_setopt($ch, curlopt_followlocation, 1); curl_setopt($ch, curlopt_ssl_verifypeer,false); // handling of -d curl parameter here. $param = array( 'a' => 'cache_lvl', 'tkn' => '8afbe6dea02407989af4dd4c97bb6e25', 'email' => 'sample@example.com', 'z' =...
Read more

HTTP/1.0 407 Proxy Authentication Required PHP -

i working in restricted network/limited access internet. since have use proxy make web service call. here php code calling web service , response . $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' => $proxy_host, 'proxy_port' => $proxy_port, 'proxy_login' => $proxy_login, 'proxy_password' => $proxy_password)); response is fatal error: uncaught soapfault exception: [wsdl] soap-error: parsing wsdl: couldn't load ' http://www.xyz.com/abc/wsdls/login.wsdl ' : failed load external entity if copy url error message , paste in browser(with same proxy settings) works. 2nd scenario is, if pass values instead of variables giving me diffarant response $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' ...
Read more

java - More than one row with the given identifier was found: 1, for class: com.model.Diagnosis -

below model, service implementation , error trace of code. model class: @entity @table(name = "ph_diagnosis_history") @jsonidentityinfo(generator = objectidgenerators.propertygenerator.class, property = "id",scope=diagnosis.class) public class diagnosis extends abstractentity { @column(name = "diagnosis_notes", length=255) @length(max = 255, message = iprprop.max_len_255_msg) private string diagnosisnote; @enumerated(enumtype.string) private diagnosistypeenum type; @temporal(temporaltype.date) @column(name = "diagnosis_date") @jsonserialize(using = jsondateserializer.class) private date diagnosisdate; @manytoone @joincolumn(name = "patient_chart_id") private patientchart patientchart; public string getdiagnosisnote() { return diagnosisnote; } public void setdiagnosisnote(string diagnosisnote) { this.diagnosisnote = diagnosisnote; } public d...
Read more