counting similar field from different tables

[Elliott]

Hi,

I am really stumped thinking about how to perform a type of count.

It is the type "People who booked this trip, also booked these trips"

I have a table for 4 different trips. One of the fields is the customer_id.

Somehow i need to check the current table for all the unique customer_id's, and then see if that customer is in each of the other trip tables.

So i would want......

<Trip 1 Page>

People who booked this trip also booked:
Trip 3: 23
Trip 4: 18
Trip 2: 6

Etc etc

So it show how many matching unique customer_id's there are in relation to the current Trip page.

I am a bit crap with queries an such, and any help would be much appreciated. Need to get it done b4 tomorrow ideally 🙂

Thanks

Elliott

[kiss_the_robot]

Still working on that amusement park booking system, eh?

It sounds like you need to perform a "join" query to get what you're looking for - I'm assuming that you're using a mySQL db, so first:

"Somehow i need to check the current table for all the unique customer_id's..."

OK. A person has just booked a trip and you need to see how many other unique customers also booked the same trip. These customers exist across different tables. Let's say you have the name of the ride they just booked stored in a variable named $this_ride, and you've got "customer_id" fields in two tables, one called "current_table" and another called "former table"...

Query:

$query1 = " SELECT COUNT(DISTINCT customer_id) FROM current_table AS c, former_table AS d WHERE c.customer_id = d.customer_id AND d.current_ride = '$this_ride' ";

The COUNT function returns the number of all NON-NULL values in a recordset, while DISTINCT weeds out any duplicates.

Is this what you're going for?