Re: Can anyone figure out this MySQL query?
Date: Fri, 01 Sep 2000 08:34:54 +0200
Message-ID: <39AF4E0E.AB185997_at_elbanet.co.at>
do-not_at_spam.me wrote:
>
> Hi;
>
> Below are two tables which I need to query information from. Problem
> is I cannot figure out the correct SQL statement to get the
> information. Unfortunately I cannot change the design of the tables
> due to historic reasons. Anyway, first the tables:
>
> Customers
> +-------------------------+
> |CustomerNo | CustomerName|
+-------------------------|>
> |1 | Bob |
> |2 | George |
> |3 | Fred |
> +-------------------------+
> Invoices
> +--------------------------------+
> |InvoiceNo | HotelNo | CustomerNo|
+--------------------------------+>
> |1 | 1 | 1 |
> |2 | 1 | 2 |
> |3 | 2 | 1 |
> |4 | 2 | 2 |
> |5 | 3 | 3 |
> |6 | 3 | 1 |
> +--------------------------------+
> Now, on to my query: I'd like to SELECT the all records FROM Invoices
> WHERE for example CustomerNo 1 AND CustomerNo 2 stayed at the same
> hotels. In this case it should match InvoiceNo 1,2,3,4 BUT NOT 5,6
> Obviously I'd like to query by CustomerName so I'd like to use an
> inner join for that, but that part is OK. Any ideas?
One possible (and extendable) solution would be (at least in SQL, don't know for MySQL):
SELECT *
FROM Invoices i, customers c
WHERE i.customerno = c.customerno and
i.hotelno in
(SELECT hotelno
FROM Invoices
WHERE CustomerNo IN (1,2)
GROUP BY hotelno
HAVING COUNT(*) >= 2);
The subselect gives you all hotels where at least two of the customers (1,2) have stayed. This can easily be extended to more customers and also to say 6 out of 10.
I hope MySQL allows subselects which use a table that also appears in the main select.
hth,
Heinz
Received on Fri Sep 01 2000 - 08:34:54 CEST