talkmagic.co.uk

Hi mates!

Since I figured some of you on this forum are into programming and stuff, maybe some of you guys could help me out with a problem.

I've got some kind of this DB (simplified):

TABLE beers
id, name, country, brewery

TABLE breweries
id, name, brewery

Off course in the table beers the brewery-attribute is referred to the id of breweries (I hope you know what I mean...)

Now I need a script/sql-statement which can delete all 'unused' breweries. For when example I remove one beer and the brewery is left but not used by any other beer.
How can I check which breweries are unused and thus remove them?

I hope anybody can help.
Thanks in advance!

I think it'd be something along the lines of

SELECT breweries
DELETE brewery WHERE beer IS NULL

It's been so long since I did and SQL, though. What happens if you subsequently get a beer in for that brewery, though? Might as well leave the brewery in and filter it out with your SELECT statements.

SELECT * FROM breweries WHERE breweries.brewery_id NOT IN (SELECT brewery_id FROM beer)

Will get you all the breweries that are not assigned to any beer...you'll need at least mysql 4.1 for this select to work...

Gonna try that, I'll get back to you in a few minutes

A thanks mate that worked.

I knew there was a way to do it, since I am an IT-student going to my 3rd year now we have learned this but I just couldn't figure it out.

Thanks!

point wrote:SELECT * FROM breweries WHERE breweries.brewery_id NOT IN (SELECT brewery_id FROM beer)

A thanks mate that worked.

I knew there was a way to do it, since I am an IT-student going to my 3rd year now we have learned this but I just couldn't figure it out.

Thanks!

You're welcome

Oh while you're at it, could you explain me what the OPTIMIZE TABLE command does in for humans understandable language...

edit: never mind I found it.

Thanks again

are you guyes talking in smart language just to poke fun at those of us who dont understand?

Blapsing_Beard wrote:are you guyes talking in smart language just to poke fun at those of us who dont understand?

Nah. It's SQL. See here for the full horror: http://en.wikipedia.org/wiki/SQL

could you explain me what the OPTIMIZE TABLE command does

As I understand, Optimize Table will reclaim space and defragment a file

1. Reclaim space that is taken up with deleted records. So if you do a lot of deleting then Optimize Table is a useful command to run.

2. Reclaim space from modified records that have variable length rows (ie varchar).

3. It will also resequence an index - particularly useful if you read throuh the entire file a lot.

Hey Jelmo,

Is this part of your course work or do you drink so much beer you are having to catalogue what you have in stock? hehehe

Leighton you funny guy haha

No I'll give a little bit of background information.
I built this website where people can rate beers and discuss them, but also look op information.

Unfortunately it is in Dutch but still if you want to take look go ahead: http://bierlab.hipkip.net
It is still in the test-phase.

But thanks for all your replies.

OK mates I've got a new problem now.
People on my site can vote for beers, now to display the rank-list I use this Query:

Code: Select all

SELECT avg(punten.punt) as gem, bieren.id as bierid, bieren.naam as biernaam, merken.naam as merknaam FROM punten, merken, bieren WHERE bieren.merk = merken.id AND bieren.id = punten.bier GROUP BY punten.bier ORDER BY `gem`

Now how can I extend this query so it puts a beer which has more votes higher than one with fewer votes.
For example if Beer A has 2 votes, both 8 and Beer B has 5 votes, all 8 - Beer B ranks higher than Beer A.

My table looks like this:
punten
id, bier, gebruiker, punt

(bier means beer, gebruiker means user but that one shouldn't matter)

Thanks in advance

Jelmo wrote:OK mates I've got a new problem now.
People on my site can vote for beers, now to display the rank-list I use this Query:

Code: Select all

SELECT avg(punten.punt) as gem, bieren.id as bierid, bieren.naam as biernaam, merken.naam as merknaam FROM punten, merken, bieren WHERE bieren.merk = merken.id AND bieren.id = punten.bier GROUP BY punten.bier ORDER BY `gem`

Now how can I extend this query so it puts a beer which has more votes higher than one with fewer votes.
For example if Beer A has 2 votes, both 8 and Beer B has 5 votes, all 8 - Beer B ranks higher than Beer A.

My table looks like this:
punten
id, bier, gebruiker, punt

(bier means beer, gebruiker means user but that one shouldn't matter)

Thanks in advance

Code: Select all

SELECT avg(punten.punt) as gem, bieren.id as bierid, bieren.naam as biernaam, merken.naam as merknaam FROM punten, merken, bieren WHERE bieren.merk = merken.id AND bieren.id = punten.bier GROUP BY punten.bier ORDER BY gem,**rank**

You can have multiple order statements separated by commas, and the list will be ordered that way.