Query Multiple Tables

[SharkBait]

I have two tables that reference each other but what I am trying to do is select a row in one table and then subtract a COUNT() from another query to give me the result.

 

Let's see if i can explain this better.

 

Table1

 

id | seats | venue

--+------+--------

1  |  30  |  GM Place

--+------+--------

2  |  34  |  BC Place

--+------+--------

 

Table2

 

id | table1_id  |  customer

--+-----------+-------------

1  | 1            |  Jim Bob

--+-----------+-------------

2  | 1            |  Billy Bob   

--+-----------+-------------

3  | 2            | Sammy D 

--+-----------+--------------

 

So I want to be able to return from Table1 the remaining `seats` by counting up the rows and minusing the ones from table2 based on Table1.id = Table2.table1_id

 

So the result row would return 28 seats left for `GM Place` and 33 seats for `BC Place`

 

[fenway]

select seats - ( select count(*) from table2 where table1_id = id ) from table1

[SharkBait]

Ah ok. I thought I could do a query within a query but wasn't quite sure how to go about doing it.

 

I also made sure I renamed the field because seats - (SELECT COUNT(*) etc is a bit much to put into a variable. lol

 

SELECT *, seats - (SELECT * FROM table2 WHERE table1_id = id) as seats FROM table 1

 

Works nicely thanks!

[fenway]

I assume you meant count(*) in your code snippet.