Re: Can anyone figure out this MySQL query?

From: Theo Peterbroers <peterbroers_at_rhbcml.leidenuniv.nl>
Date: Wed, 30 Aug 2000 09:52:48 GMT
Message-ID: <8oilhl$4hr$1_at_highway.leidenuniv.nl>


In article <39acc88f.73770416_at_news.arnes.si>, 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?
>
>Many Thanks
> Chris
Simple, if you are only interested in a case with two customers: SELECT a.*
FROM Invoices as a, Invoices as b
WHERE a.CustomerNo in (1,2)
  AND b.CustomerNo in (1,2)
  AND a.CustomerNo <> b.CustomerNo
  AND a.HotelNo = b.HotelNo

The trick is to use two aliases (a and b) for the same table.

It works OK for "any two customers" if you change SELECT to SELECT DISTINCT SELECT DISTINCT a.*
FROM Invoices as a, Invoices as b

WHERE a.CustomerNo in (1,2,3)
  AND b.CustomerNo in (1,2,3)
  AND a.CustomerNo <> b.CustomerNo
  AND a.HotelNo = b.HotelNo

Without distinct each invoice would appear twice in the result.

This approach gets awkward if extended to selections such as "select invoices where at least six out of ten customers stayed in the same hotel", due to the great number of aliases needed. Using three queries appears to offer a more universal way:

QueryCustomers
SELECT *
FROM Invoices
WHERE CustomerNo IN (1,2,3, ..... n)

QueryHotels
SELECT HotelNo, count (*)
FROM QueryCustomers
GROUP BY HotelNo
HAVING count (*) > 5

QueryInvoices
SELECT *
FROM QueryCustomers as c, QueryHotels as h WHERE h.HotelNo = c.HotelNo Received on Wed Aug 30 2000 - 11:52:48 CEST

Original text of this message