What relations between package and flights and transfer and excursion and hotel

Databases
62

Sorry, not to be dense, but I’m not sure what you’re trying to accomplish here. So, in your example above, if you have a package named WallyWallyWashington and it only went to Movenpick, then your query would show AlexaPackage and WallyWallyWashington? What is the purpose of what you’re trying to accomplish here?

63

the purpose is
i need to show the list of hotels with cost price but packages have only
one hotels

64

if i need to show list of hotels with prices it will be no good to show hotels
more than one time
are this is correct

HotelA         PACKAGEA    2000
HOTELB     PACKAGEB   3000
HOTELA    PACKAGEB    5000

here hotel A repeated
this show all packages and related hotels
i need only to show hotels with costs only

65

So are you looking for the hotel costs for a specific package?

Or are you looking for the costs for a specific hotel for various packages?

Or neither?

If it’s one of the first two, you’re going to need to filter the data by a specific value

(HINT: WHERE fieldname = ‘Value’)

66

this is correct but are there are any thing can count if more than hotel per package not display

67

I’m still confused. In your results from post #63, what SHOULD display? It seems like it either should show what you posted, or it should show this because they are for different packages.

HotelA         PACKAGEA    2000
HOTELA    PACKAGEB    5000
68

Not this result i need
I need only to show
HotelA PACKAGEA 2000 only
and i need not show the following
HOTELA PACKAGEB 5000
because PackageB have more than one hotel
and what i ask show packages that have only one hotel

69

Ouch. I’m not sure why in the world you’d ever want to do that, but the only way I can think of to do it would be to basically run the query again as a sub-query to filter it down

INNER JOIN (SELECT h.HotelID
              FROM package AS p
             INNER JOIN packageduration AS pd ON p.packageID = pd.packageID
             INNER JOIN (SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY PackageDurationsId ORDER BY Days) FROM DurationDetails AS dd) AS dd ON pd.PackageDurationsID = dd.PackageDurationsID
             INNER JOIN DayDetails AS dyd ON dd.DetailsDurationID = dyd.DetailsDurationID
             INNER JOIN Hotel AS h ON dyd.HotelID = h.HotelID
             INNER JOIN StartPackage AS sp ON sp.PackageId = p.PackageId
             INNER JOIN HotelPrice AS hp ON h.HotelID = hp.HotelID
             WHERE DATEADD(day, dd.RN - 1, sp.StartDate) BETWEEN hp.FromDate AND hp.ToDate 
             GROUP BY h.HotelID
            HAVING COUNT(*) = 1) AS SQ ON SQ.HotelID = H.HotelID
70

can you tell me please what before inner join
what exactly before this statement
INNER JOIN (SELECT h.HotelID

71

you need to put that in your sql statement, so you’d put it in as another join…

72

thank you for reply
i run it
but it not return any result
0 records returned

73

What happens if you run just

74

this is write only
or inner join
or what

75

Just that. Hopefully it only returns hotelID and counts of one. It might not (I’m looking at it and it might not be)

76

I solved as following

;with cte as(
SELECT h.HotelID, h.HotelName, p.PackageName, sp.StartDate, sp.EndDate, SUM(hp.HotelPrice) AS cost,DENSE_RANK() Over(partition by p.PackageName order by HotelName)+DENSE_RANK() Over(partition by p.PackageName order by HotelName desc)-1 as dr
FROM package AS p
INNER JOIN packageduration AS pd
	ON p.packageID = pd.packageID
INNER JOIN (
	SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY PackageDurationsId ORDER BY Days) 
	FROM DurationDetails AS dd
) AS dd
	ON pd.PackageDurationsID = dd.PackageDurationsID
INNER JOIN DayDetails AS dyd
	ON dd.DetailsDurationID = dyd.DetailsDurationID
INNER JOIN Hotel AS h
	ON dyd.HotelID = h.HotelID
INNER JOIN StartPackage AS sp
	ON sp.PackageId = p.PackageId
INNER JOIN HotelPrice AS hp
	ON h.HotelID = hp.HotelID
WHERE  DATEADD(day, dd.RN - 1, sp.StartDate) BETWEEN hp.FromDate AND hp.ToDate 
GROUP BY h.HotelID, h.HotelName, p.PackageName, sp.StartDate, sp.EndDate)
select *
from cte 
where dr=1
77

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.