Foreign Key references some

alexgeek · Jan 30th, 2008, 22:21

Is there anyway to have a column reference the amount of rows of another table based on a where?
Something like:

create table tbl (
[columns here]
amount int(100) REFERENCES othertbl(COUNT(*) FROM othertbl GROUP BY column)
)

It's hard to explain what I mean, but that example should help.
Thanks.

JustinStudios · Jan 31st, 2008, 05:05

Alex, I actually did this with PHP... Not 100% sure if it is possible with just a SQL statement.

What I did was I selected the count from the table I wanted and then saved that to a variable, then I added that to the corresponding row in another table. So I used 2 SQL statements to do it really, just used PHP to hold the variable.

alexgeek · Jan 31st, 2008, 15:38

Yeah the point was that I didn't want to have to query twice.
If there's no way to do this on the mysql side I'll have to resort to two queries.

JustinStudios · Jan 31st, 2008, 17:05

The only way that I could fathom it could be done in one query is if you used a Select Into and somehow got it to return the count instead of the data. I've searched the internet for the answer and haven't found anything yet though. I'll keep playing around with it and if I find a solution I'll post it back.

alexgeek · Jan 31st, 2008, 17:14

I guess I could just do select * from tbl, othertbl or some sort of left join anyway.