[ThunderPoit]

So my programming flavor of the month for most of April and all of May so far has been SQL. I have been reading through Head First SQL from O'Reilly and absolutely love it. Last weekend, I fired up my hobby server and decided to try out an idea I had for an expense tracking database. Heres what I came up with:



It lets me track all the purchases I make, when and where they are made, who from, what was purchased, how much was purchased, and the price per purchase unit. I recently got back from a business trip to North Carolina so I took my receipts and entered them into my new database.

I then played around with a few simple joins and stuff, poking around and figuring out what I could do with it, and I stumbled upon what I thought would be a great way to test what I know about SQL: Write a query that will tell me how much I spent on gas in NC.

Five hours of beating my head against the wall later, I took some advil and went to bed. This was the closest I came to answering my question, but I'm sure I could do better with the query:

SELECT  t.tran_id AS Transaction_Number,  vl.venl_state as State, ti.item_id,  (SELECT (ti.quantity * ti.price)) AS Amount  FROM transactions t    JOIN vendor_location vl  ON t.location_id = vl.location_id  JOIN tran_info ti  ON t.tran_id = ti.tran_idWHERE ti.item_id =   ( SELECT item_id FROM items WHERE description LIKE '%fuel%' ) AND vl.venl_state = 'NC';Result:+--------------------+-------+---------+-----------+| Transaction_Number | State | item_id | Amount    |+--------------------+-------+---------+-----------+|                  9 | NC    |      11 | 27.219380 | |                  8 | NC    |      11 |  7.063051 | +--------------------+-------+---------+-----------+

I still have not been able to figure out how to get my answer without having to do additional math after the query. If anyone is interested in helping me with this, here is what I am specifically looking for:

Total amount(quantity*price) spent on fuel in North Carolina where the total is calculated by finding the sum of all fuel purchase entries in tran_info that came from a vendor in NC.

And just to be safe, no, you are not helping me cheat on my homework for a class somewhere, this is just something that has been driving me insane since I thought of it. The Head First book did a great job of showing me how to do a join with two tables, but they didnt really explain doing a join with three tables or more.

[Brad]

Perhaps I'm missing something, but would this work?

SELECT	SUM(quantity * price)FROM	items i	INNER JOIN tran_info ti ON (i.item_id = ti.item_id)	INNER JOIN transactions t ON (t.tran_id = ti.tran_id)	INNER JOIN vendor_location vl ON (vl.location_id = t.location_id)WHERE	i.description LIKE '%fuel%'	AND vl.venl_state = 'NC'

Or if you want to reuse your existing query, just put what you have into a subquery and SELECT SUM(Amount) from it.

[ThunderPoit]

That's why you're the master and im the noob

WINNAR!+-----------------------+| SUM(quantity * price) |+-----------------------+|             34.282431 | +-----------------------+

[Brad]

I serve to please!

For what it's worth, I've only been writing SQL for about three or four years. I self-taught for my job by reading a few online tutorials and nowadays I'm building tables and queries in my sleep (sadly, this has actually happened a few times). Once you get a solid grasp of the basics and it starts to "click", everything will become super easy.

[ThunderPoit]

wheeeee! lookie what i can do now thanks to what you showed me!

SELECT v.ven_name,vl.venl_address,vl.venl_city,vl.venl_state,vl.ven_zip,i.description,(quantity * price) as totalFROMitems i  NATURAL JOIN tran_info ti  NATURAL JOINtransactions t  NATURAL JOINvendor_location vl NATURAL JOIN vendor vWHERE  vl.venl_state = 'NC' order by t.tran_time;Result:+-----------------+------------------------------+--------------+------------+---------+-----------------------+-----------+| ven_name        | venl_address                 | venl_city    | venl_state | ven_zip | description           | total     |+-----------------+------------------------------+--------------+------------+---------+-----------------------+-----------+| Wendys          | 2801 Boyer St                | Charlotte    | NC         |   28208 | Double Cheese Combo   |  5.290000 | | Wendys          | 2801 Boyer St                | Charlotte    | NC         |   28208 | Tax                   |  0.440000 | | Scotchman       | 7815 Valley Blvd             | Blowing Rock | NC         |   28605 | Doritos               |  3.790000 | | Scotchman       | 7815 Valley Blvd             | Blowing Rock | NC         |   28605 | Cheese Dip            |  3.690000 | | Scotchman       | 7815 Valley Blvd             | Blowing Rock | NC         |   28605 | Tax                   |  0.150000 | | Highlands Grill | 1128 S Main St               | Blowing Rock | NC         |   28605 | Cheese Burger         |  8.200000 | | Highlands Grill | 1128 S Main St               | Blowing Rock | NC         |   28605 | Soda                  |  1.950000 | | Highlands Grill | 1128 S Main St               | Blowing Rock | NC         |   28605 | Tax                   |  0.690000 | | Highlands Grill | 1128 S Main St               | Blowing Rock | NC         |   28605 | Tip                   |  3.000000 | | Canyons         | 8960 Valley Blvd             | Blowing Rock | NC         |   28605 | Sirloin Steak Meal    | 17.500000 | | Canyons         | 8960 Valley Blvd             | Blowing Rock | NC         |   28605 | Soda                  |  1.650000 | | Canyons         | 8960 Valley Blvd             | Blowing Rock | NC         |   28605 | Tax                   |  1.290000 | | Canyons         | 8960 Valley Blvd             | Blowing Rock | NC         |   28605 | Tip                   |  3.560000 | | Canyons         | 8960 Valley Blvd             | Blowing Rock | NC         |   28605 | Bacon Chz Burger      |  7.900000 | | Canyons         | 8960 Valley Blvd             | Blowing Rock | NC         |   28605 | Soda                  |  1.650000 | | Canyons         | 8960 Valley Blvd             | Blowing Rock | NC         |   28605 | Tax                   |  0.650000 | | Canyons         | 8960 Valley Blvd             | Blowing Rock | NC         |   28605 | Tip                   |  2.500000 | | Canyons         | 8960 Valley Blvd             | Blowing Rock | NC         |   28605 | Sirloin Steak Meal    | 17.500000 | | Canyons         | 8960 Valley Blvd             | Blowing Rock | NC         |   28605 | Tax                   |  1.180000 | | Wendys          | 1406 E Main St               | Lincolnton   | NC         |   28092 | Single Cheese Combo   |  4.290000 | | Wendys          | 1406 E Main St               | Lincolnton   | NC         |   28092 | Extra Cheese          |  0.300000 | | Wendys          | 1406 E Main St               | Lincolnton   | NC         |   28092 | Tax                   |  0.310000 | | Petro Express   | 1627 E Main St               | Lincolnton   | NC         |   28092 | Regular Unleaded Fuel |  7.063051 | | Sam's Mart      | 2825 Little Rock             | Charlotte    | NC         |   28214 | Regular Unleaded Fuel | 27.219380 | | Bojangles       | 5501 Josh Birmingham Parkway | Charlotte    | NC         |   28208 | 2PC Dinner            |  5.890000 | | Bojangles       | 5501 Josh Birmingham Parkway | Charlotte    | NC         |   28208 | Soda                  |  1.990000 | | Bojangles       | 5501 Josh Birmingham Parkway | Charlotte    | NC         |   28208 | Tax                   |  0.650000 | +-----------------+------------------------------+--------------+------------+---------+-----------------------+-----------+

Now i just need to get some kind of spiffy form and php script set up so i dont have to key in all this crap by hand :P
Thanks man!

[Banana]

If you happens to be using MySQL, try executing the query with the clause WITH ROLLUP:

SELECT v.ven_name,vl.venl_address,vl.venl_city,vl.venl_state,vl.ven_zip,i.description,(quantity * price) as totalFROMitems i  NATURAL JOIN tran_info ti  NATURAL JOINtransactions t  NATURAL JOINvendor_location vl NATURAL JOIN vendor vWHERE  vl.venl_state = 'NC' order by t.tran_time WITH ROLLUP;

Then you get totals for every categories, and a grand total. Quite cool!

[ThunderPoit]

hmmm, it didnt like the rollup bit, i am using mysql v5.0.41

[Banana]

No, that's my fault- I forgot that you need to have a GROUP BY clause to use ROLLUP option.

Try and play with grouping by customers for the starters.

Manual entry

[Wyatt]

Originally Posted by Brad I serve to please! For what it's worth, I've only been writing SQL for about three or four years. I self-taught for my job by reading a few online tutorials and nowadays I'm building tables and queries in my sleep (sadly, this has actually happened a few times). Once you get a solid grasp of the basics and it starts to "click", everything will become super easy.

QFT. I've actually woken up in the middle of the night and written queries down in a notebook I keep next to my bed for when I get a really good idea.

[drewprops]

I'm kind of jealous.... doing some SQL has been on my hobby list for a long time. The deal is though, I'd only likely do it now if I was planning on building some sort of social data sharing website (which is of course the sort of thing you'd want to have years of database-building experience). Elegant data handling is awesome.

(Oh, and brother... you need to lay off the cheeseburgers!)

[euain]

It might not be something you're interested in (in which case please ignore!) but the crow's feet in your ERD are not quite right.

You have multiplicities of 0 to many in all the relationships wheras I'm sure there are some where, for example a vendor_location has only one vendor.

I'm not trying to be picky or criticise - so please ignore if you were just playing and don't care!

[cosus]

Did you tip three dollars for a ten dollar meal? It might be the Jew, but that sounds egregious.

[Brad]

Originally Posted by cosus Did you tip three dollars for a ten dollar meal? It might be the Jew, but that sounds egregious.

wtf?

[ThunderPoit]

OK, so I haven't really learned as much as I'd hoped since my last post. I ran into a problem where a bunch of spam bots overran a forum i had set up for some friends. there are now roughly 13,000 posts i want gone. The posts are in two tables, phpbb_posts and phpbb_posts_text. phpbb_posts contains the user_id and the post_id, phpbb_posts_text contains the post_id and the post_text. i want to delete all records from phpbb_posts and phpbb_posts_text where user_id in phpbb_posts is > 2868. i was able to select them all by doing a join on post_id, but i cant seem to find a way to delete them. Any ideas?

[ThunderPoit]

Originally Posted by cosus Did you tip three dollars for a ten dollar meal? It might be the Jew, but that sounds egregious.

So is that good or bad? (egregious can mean either, iirc)

[Brad]

To delete, use the DELETE command.

DELETE FROM phpbb_posts_text USING phpbb_posts WHERE phpbb_posts_text.post_id = phpbb_posts.post_id AND phpbb_posts.user_id > 2868;DELETE FROM phpbb_posts WHERE user_id > 2868;

That's using Postgres syntax in the first one. I think the USING/joining bits in a DELETE work differently in MySQL. The general idea is the same, though.

[ThunderPoit]

heh, oops. i think my hosting company is gonna get pissed at me.
i ran this command: delete from phpbb_posts_text, phpbb_posts using phpbb_posts_text inner join phpbb_posts where phpbb_posts.poster_id > 2868
and mysqld has been choking the hell out of the server for at least 10 min now.
please tell me its actually deleting and not stuck in an infinite loop.

and yes, i did back up the db before i did any of this. i just dont know why i didnt test on a copy

[ThunderPoit]

well that was fun, all the posts on my forum went poof!
learned my lesson, waiting for the ginormous backup to upload and restore

[Banana]

Ow. Very fun.

I suppose if I were uncertain whether my SQL was correct, I'd nest this in a transaction so I can verify the results before actually committing. (If it's MySQL, it's only good if we're using InnoDB, of course)


Which makes me wonder- in Access, I can "peek" into the outputs for any action queries (e.g. DELETE, UPDATE, and INSERT). Surely there is similar functionality (short of using transactions?)

[BenP]

Originally Posted by ThunderPoit heh, oops. i think my hosting company is gonna get pissed at me. i ran this command: delete from phpbb_posts_text, phpbb_posts using phpbb_posts_text inner join phpbb_posts where phpbb_posts.poster_id > 2868 and mysqld has been chocking the hell out of the server for at least 10 min now. please tell me its actually deleting and not stuck in an infinite loop. and yes, i did back up the db before i did any of this. i just dont know why i didnt test on a copy

I think you have to join on something, don't you? If you look at Brad's code, his where statement included "phpbb_posts_text.post_id = phpbb_posts.post_id"... I'm as new to SQL as you, though, so I could be wrong.

[Brad]

Originally Posted by Banana Which makes me wonder- in Access, I can "peek" into the outputs for any action queries (e.g. DELETE, UPDATE, and INSERT). Surely there is similar functionality (short of using transactions?)

Another reason I like PostgreSQL: the EXPLAIN command. Oracle has this too, but the syntax is slightly different.

For example...

EXPLAIN DELETE FROM phpbb_posts_text USING phpbb_posts WHERE phpbb_posts_text.post_id = phpbb_posts.post_id AND phpbb_posts.poster_id > 2868;                                   QUERY PLAN                                   -------------------------------------------------------------------------------- Hash Join  (cost=14584.40..54527.79 rows=378903 width=6)   Hash Cond: (phpbb_posts_text.post_id = phpbb_posts.post_id)   ->  Seq Scan on phpbb_posts_text  (cost=0.00..28969.68 rows=391468 width=10)   ->  Hash  (cost=8552.11..8552.11 rows=378903 width=4)         ->  Seq Scan on phpbb_posts  (cost=0.00..8552.11 rows=378903 width=4)               Filter: (poster_id > 2868)(6 rows)

Again, I'm not sure if there's an equivalent operation in MySQL.

[Brad]

Originally Posted by Banana Ow. Very fun. I suppose if I were uncertain whether my SQL was correct, I'd nest this in a transaction so I can verify the results before actually committing. (If it's MySQL, it's only good if we're using InnoDB, of course)

IMO, when you're manually mucking in the database, you should always always always be working in a transaction. That MySQL doesn't support transactions everywhere is nothing short of moronic.

[Banana]

Originally Posted by Brad IMO, when you're manually mucking in the database, you should always always always be working in a transaction. That MySQL doesn't support transactions everywhere is nothing short of moronic.

I quite feel the same way about requiring transaction, which is why my install has been configured to use InnoDB as the default engine, but their intention is understandable- they were looking more for a fast multiple-read server more than a server to store data but that may be changing- I understand they're working on a Maria storage engine, which is supposed to replace the default MyISAM engine, and has transaction support.