Counting matching ids 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......

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

ive tried looking into some kind of loop and DISTINCT, but my inexperience cant put together a workable statement.

Any help please?

vallette

Off the top of my head I think something like this should work:
SELECT COUNT(t1.) AS trip1count, COUNT(t2.) AS trip2count,COUNT(t3.) AS trip3count, COUNT(t4.) AS trip4count
FROM Trip1 t1
INNER JOIN Trip2 t2 ON t1.customerid=t2.customerid
INNER JOIN Trip3 t3 ON t1.customerid=t3.customerid
INNER JOIN Trip2 t4 ON t1.customerid=t4.customerid
WHERE t1.customerid=$customerid

In the future I'd suggest combining all the trips into a single table rather then spreading them across multiple tables. Easier to add new trip types, query the data, and maintain the tables.

Elliott

Thanks, gonna try this later on, ill explain what tables i have etc and see if u think its still correct. 🙂

The tables are not really a trip, but a ridetable. With a row in the table for each seat of the ride. Customers could book like 2 seats on ride a, 3 on ride b, 3 on ride c, and 1 on ride d etc....

ride_a

-time---row_num---seat---booked---customer_id

0900--------1----------a--------1--------------2--------
0900--------1----------b--------1--------------2--------
0900--------1----------c--------0--------------0--------
0900--------1----------d--------0--------------0--------
0900--------2----------a--------1--------------4--------
0900--------2----------b--------1--------------4--------
0900--------2----------c--------1--------------4--------
0900--------2----------d--------1--------------3--------

so there will be 3 other tables like this......
when a seat is booked, its a 1, if not a 0, and the customer_id will be inserted in the customer_id field.

yes u get the picture that i wanna see if people that booked this ride, also booked other rides.

I was thinking it needed DISTINCT, as even though a table may have many instances of a customer_id, i only need to know if a table has that customer_id or not, and not how many times it occurs. (hope u understand me 🙂 )

Also i need to display the results in an order.....most popular first etc.

so like on the ride_a info page, i would like it to say, people who booked this ride, also booked :

ride_b (12)
ride_c (8)
ride_d (3)

where the num in brackets is the amount of matches.

I thank you very much for ya help on this.

Also i had trouble displaying the result, didnt know wether to use mysql_fetch_array, or mysql_num_rows.

Thanks

Elliott

to keep it easier for me to code, is it possible to do each one separately.

like on ride_a page, could i have a select statement for each ride.

🙁 i got no idea at all

need to do it by tomorrow 🙁