You almost had it right, your FINAL "AND" was blowing your syntax. You are
not really aggregating anything so you also do not need the GROUP BY
clause, Your ORDER BY clause should keep all of the hotels listed for you
.
SELECT *
FROM stammdaten
INNER JOIN relation_services ON stammdaten.id_PK =
relation_services.hotelid
INNER JOIN relation_sparetime ON stammdaten.id_PK =
relation_sparetime.hotelid
WHERE
stammdaten.location LIKE 'Bern' AND
relation_services.serviceid IN (5, 3, 7 ) AND
relation_sparetime.sparetimeid IN (2, 5)
ORDER BY stammdaten.name
That will return all of the data from all of the columns from all of the
tables in this query where the rows meet your criteria.
What you may have wanted instead was a single list of hotels with a
comma-separated list of services and a comma separated list of activities.
(this IS aggregating information so I will need the GROUP BY in this
query)
SELECT s1.name, s1.address, s1.location, GROUP_CONCAT(sv.servicesname) as
services, GROUP_CONCAT(sp.sparetimename) as activities
FROM stammdaten s
INNER JOIN (
SELECT s_1.id_PK
FROM stammdaten s_1
INNER JOIN relation_services rsv_1
ON s.id_PK = rsv.hotelid
AND rsv_1.sparetimeid IN (2, 5)
INNER JOIN relation_sparetime rsp_1
ON s.id_PK = rsp.hotelid
AND rsp_1.serviceid IN (5, 3, 7 )
) as s2
ON s2.pk_ID = s.pk_ID
INNER JOIN relation_services rsv
ON s.id_PK = rsv.hotelid
INNER JOIN relation_sparetime rsp
ON s.id_PK = rsp.hotelid
INNER JOIN services sv
ON sv.id_PK = rsv.serviceid
INNER JOIN sparetime sp
ON sp.id_PK = rsp.sparetimeid
WHERE s.location LIKE 'Bern'
GROUP BY s.name, s.address, s.location
ORDER BY stammdaten.name
The inner (SELECT ...) actually finds the id_PK values of all hotels that
match your criteria while the outer select uses that list to gather all of
the other information for the query. In older versions of MySQL you would
have needed to perform this query in two separate stages. The first stage
would be to create a temporary table containing the results of the inner
query with the second step JOINing that information back to the outer
query from above. The whole thing would look like:
CREATE TEMPORARY TABLE tmpStammen
SELECT s_1.id_PK
FROM stammdaten s_1
INNER JOIN relation_services rsv_1
ON s.id_PK = rsv.hotelid
AND rsv_1.sparetimeid IN (2, 5)
INNER JOIN relation_sparetime rsp_1
ON s.id_PK = rsp.hotelid
AND rsp_1.serviceid IN (5, 3, 7 )
SELECT s1.name, s1.address, s1.location, GROUP_CONCAT(sv.servicesname) as
services, GROUP_CONCAT(sp.sparetimename) as activities
FROM stammdaten s
INNER JOIN tmpStammen s2
ON s2.pk_ID = s.pk_ID
INNER JOIN relation_services rsv
ON s.id_PK = rsv.hotelid
INNER JOIN relation_sparetime rsp
ON s.id_PK = rsp.hotelid
INNER JOIN services sv
ON sv.id_PK = rsv.serviceid
INNER JOIN sparetime sp
ON sp.id_PK = rsp.sparetimeid
WHERE s.location LIKE 'Bern'
GROUP BY s.name, s.address, s.location
ORDER BY stammdaten.name
DROP TABLE tmpStammen
This does the same thing as the previous query except it _explicitly_
creates and drops the temporary table containing the id_PK column
information.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Salzgeber Olivier <[EMAIL PROTECTED]> wrote on 07/15/2004
03:22:14 AM:
> Hello everyone
> I hope this is the right list for my question if not please let me know.
>
> First I've got to say that I'm not an experienced "query-builder" so
maybe
> the answer to my problem is very easy. But I have no idea how I could
solve
> this problem and all my research (Internet and Forums) did not help me
:(
>
> I am working on a hotel database.
> Every hotel has some general data like name, address,... and can provide
one
> or many services. And a service can be for 0 or many hotels. So we have
a
> many to many link.
> A hotel can also provide one or many spare time activity's and this
> activity's can be for 0 or many hotels. So another many to many link.
>
> I've created a link-table for activity's and services in which i link
the
> hotels to the category by ID.
>
> So here are my tables:
> - stammdaten (id_PK, name, description, address, location...)
> - services (id_PK, servicesname)
> - relation_services (hotelid, serviceid)
> - sparetime (id_PK, sparetimename)
> - relation_sparetime (hotelid, sparetimeid)
>
> I need to create a search Query to find all the hotels which have
specific
> services and sparetime activities.
> For example:
> Search for hotels where location is "Bern" and the hotel has sparetime
> activity 2 and 5 AND services 5 and 3 and 7
> So only hotels which match all the expressions should appear as results.
>
> I've tried it with the following Query but this doesn't work as it
should.
>
>
****************************************************************************
> *
> SELECT *
> FROM stammdaten
> INNER JOIN relation_services ON stammdaten.id_PK =
relation_services.hotelid
> INNER JOIN relation_sparetime ON stammdaten.id_PK =
> relation_sparetime.hotelid
> WHERE
> stammdaten.location LIKE 'Bern' AND
> relation_services.serviceid IN (5, 3, 7 ) AND
> relation_sparetime.sparetimeid IN (2, 5) AND
> GROUP BY stammdaten.name
> ORDER BY stammdaten.name
>
****************************************************************************
> *
>
> I'm trying to get this working for more than a week now but couldn't
find a
> solution.
>
> Maybe this isn't possible in a single query ?
> But I have no idea how to get this working in another way.
>
> Hope somebody here can help me with this.
>
> O.Salzgeber
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
