Booking system SQL

[Anon]

Nasty query question this.

My booking system looks to a table selections, where it finds out the ticket id (tid) concert id (cid) and then looks to tickets to find out a price.

What I want to incorporate is the ability to add a second choice ticket, so that in selections there is a tid2 field.

My problem is grabbing the price of the 2 ticket choices(one row selections=two rows tickets) in one SQL. I suspect it's a SELECT x AS x type statement, but I can't get it right.

"SELECT tid, price, cid FROM selections, concerts, venues, tickets, season WHERE selections.cart_id = '$cart_id'
AND selections.cid = concerts.cid AND concerts.vid = venues.vid
AND tickets.tid = selections.tid ORDER BY selections.cid, selections.tid"

Any help appreciated!

[[deleted]]

You can join one table twice using the AS operator:

SELECT *
FROM table1,
table2 AS tableA,
table2 AS tableB
WHERE table1.field = tableA.field
AND table2.field2 = tableB.field