Not sure if it will be possible to show what I require due to the structure of the tables.
But essentially, if I have a result which looks like:
LodgeID, Lodge, Activity
1, Lodge A, Activity A
1, Lodge A, Activity B
Is it possible to show that as:
LodgeID, Lodge, Activity 1, Activity 2
1, Lodge A, Activity A, Activity B
what you’re thinking of is called a “pivot” and the closest you will come, in case you’re using MySQL, is the GROUP_CONCAT function, but that will produce a single column of all concatenated activities, rather than separate columns for each one
you should really be doing pivots, as well as any other ~cosmetic~ re-arranging of query results, in your application language, e.g. php
Thanks - this is something you helped out with in the past.
The just was that there are tables storing information about lodges, and also tour operators. Both lodges and tour operators can have multiple keywords associated with them which are stored in separate related tables. And by using the count function its working so that we can show which tour operators are the best matches for each lodge based on how many keywords are associated with both the lodge and the tour operator.
The problem here is that the site owner has asked for a report listing lodges, and two different values from their keywords. So ‘Accommodation Type’ and ‘Country’ are keyword categories, and could have keyword including ‘Tented Camp (>20 tents)’ or ‘Tented Camp (<20 tents)’ for Accommodation Type, or ‘Kenya’ or ‘Tanzania’ for Country.
So the objective here is to show a list of all Tented Camps (>20 tents), which also shows the Country each is in.
So actually the value ‘Tented Camps (> 20 tents)’ doesn’t need to appear in the list, so long as the list did just contain the lodges with that keyword value.
(Note that Activity is just what I historically had my keywords named, so in this example a country can be an Activity. It would probably be better renamed ‘Keyword’.)
I can have a query looking like:
SELECT lodges.LodgeID
, Lodge
, Activity
, Subscription
, subscription_end_date
, CategoryID
FROM lodges
INNER JOIN lodge_activities_it
ON lodges.LodgeID = lodge_activities_it.LodgeID
INNER JOIN lodge_activities
ON lodge_activities_it.ActivityID = lodge_activities.ActivityID
INNER JOIN lodge_activity_categories
ON lodge_activities.Category_ID = lodge_activity_categories.CategoryID
LEFT JOIN product_likes ON lodges.LodgeID = product_likes.LodgeIDPL
WHERE CategoryID = 15
GROUP BY LodgeID
Will show the country in the list (CategoryID 15 = ‘Countries’)
So the aim, if possible, is to have a query like this:
SELECT lodges.LodgeID
, Lodge
, Activity
, Subscription
, subscription_end_date
, CategoryID
FROM lodges
INNER JOIN lodge_activities_it
ON lodges.LodgeID = lodge_activities_it.LodgeID
INNER JOIN lodge_activities
ON lodge_activities_it.ActivityID = lodge_activities.ActivityID
INNER JOIN lodge_activity_categories
ON lodge_activities.Category_ID = lodge_activity_categories.CategoryID
LEFT JOIN product_likes ON lodges.LodgeID = product_likes.LodgeIDPL
WHERE lodge_activities.ActivityID = 92
GROUP BY LodgeID
Where lodge_activities.ActivityID 92 = ‘Tented Camp (< 20 tents)’ but also somehow includes the country (i.e. where CategoryID = 15).
Not sure if that helps any more - I’ve tried to explain it as best I can, but not sure if it amounts to anything more than my original more simplistic example.
you’re right, that’s much worse
i see you using GROUP BY without any aggregation functions, and now i need to go lie down for a while
Sorry - it did have a COUNT function, but I stripped it out, but had left in the GROUP BY - should have left them both, or taken them both out.
In full, that query is:
SELECT lodges.LodgeID
, Lodge
, Activity
, Subscription
, subscription_end_date
, CategoryID
, (SELECT Count(DISTINCT UserIDPL, LodgeIDPL)
FROM product_likes
WHERE LodgeIDPL = lodges.LodgeID)
AS connections
FROM lodges
INNER JOIN lodge_activities_it
ON lodges.LodgeID = lodge_activities_it.LodgeID
INNER JOIN lodge_activities
ON lodge_activities_it.ActivityID = lodge_activities.ActivityID
INNER JOIN lodge_activity_categories
ON lodge_activities.Category_ID = lodge_activity_categories.CategoryID
LEFT JOIN product_likes ON lodges.LodgeID = product_likes.LodgeIDPL
WHERE lodge_activities.ActivityID = 92
GROUP BY LodgeID
The logic to create separate columns from each lodge activity belongs in the application language not the query.
if you leave them in, then the fact that Activity and CategoryID are not aggregated is going to give me another headache
OK - all I know is if I reduce it to the bare minimum fields, the following:
SELECT
lodges.Lodge
, lodge_activities.Activity
FROM lodges
INNER JOIN lodge_activities_it
ON lodges.LodgeID = lodge_activities_it.LodgeID
INNER JOIN lodge_activities
ON lodge_activities_it.ActivityID = lodge_activities.ActivityID
INNER JOIN lodge_activity_categories
ON lodge_activities.Category_ID = lodge_activity_categories.CategoryID
Gives me:
Lodge, Activity
Lodge A, Activity 1
Lodge A, Activity 2
Lodge B, Activity 1
Lodge B, Activity 3
Which, if possible, I would like to display as:
Lodge, Activity A, Activity B
Lodge A, Activity 1, Activity 2
Lodge B, Activity 1, Activity 3
What application language are you using?
I’m using PHP - so I’m looking online at that too, although this is all new territory for me. My working knowledge of PHP is pretty limited to SQL related things like echoing recordset values, or storing values in sessions, that sort of thing.
I often say to clients that some things look difficult, but are two minute jobs, and some things that look simple, are actually pretty complex (especially if, as is the case here, you don’t know how to do something!) This is definitely one of the later, for me at least.
ok, well the first step to solving your problem is to create a collection/array of lodges. Each lodge (associative array) should have an “activities” sub array that contains the lodges activities.
Thanks - arrays are a bit beyond me. I’ve posted in the PHP section, and will have a look online too.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.