2

I forgot a trick a teacher told me that allowed a Select query to be your table (assuming only parts of the database is used with repeating entries)...

THIS_FUNCTION(Select link, things, conditions from OneTable tabA where conditions = IGotSomething)

THIS_FUNCTION(Select link, things, conditions from OneTable tabB where conditions = IlostSomething)

Select tabA.things - tabB.things from tabA inner join tabB on tabA.link = tabB.link

Something like that.

Someone help me remember?

In case anyone is asking... My database uses one table for all transactions that come in and out, defined by a column where stuff is going. To get an inventory of everything, I need to see where stuff came from (-stock) and where stuff is going to (+stock).

There WILL be cases that where it's going to is ALSO where it came from.

I already have an idea how to do this, but I need that trick I forgot...

Whoops! I forgot to mention I need to be able to build these queries based on user needs during runtime. It's something customizable, thus building and rebuilding views may take up more processes than wanted.

dgood1
  • 106
  • 10

1 Answers1

0

Edit: here's one way:

CREATE PROCEDURE this_function()
BEGIN
  Select link, things, conditions from OneTable tabA where conditions = IGotSomething;
END

Then:

CALL this_function();

Here's another way, but it will create new tables in your DB:

CREATE ALGORITHM = UNDEFINED VIEW `tabA` AS Select link, things, conditions from OneTable tabA where conditions = IGotSomething;

CREATE ALGORITHM = UNDEFINED VIEW `tabB` AS Select link, things, conditions from OneTable tabB where conditions = IlostSomething;

Then:

Select tabA.things - tabB.things from tabA inner join tabB on tabA.link = tabB.link
jerdiggity
  • 3,655
  • 1
  • 29
  • 41
  • This wasn't the trick... but I like it... Thank you. I guess I'll use this for now. I hope I can put this all in one query. – dgood1 Oct 06 '13 at 00:42
  • Oh gotcha. Well the good news is you only need to run the first part once. After that, it should create two tables in your database. – jerdiggity Oct 06 '13 at 00:44
  • Whoops! That'll be a problem then... I forgot to mention I need to be able to build these queries based on user needs during runtime. It's something customizable, thus building and rebuilding views may take up more processes than wanted. I'll experiment on it then... Thanks for the heads up. – dgood1 Oct 06 '13 at 00:48
  • Bad news and good news... I remembered the trick I forgot... The With clause... which, defined by this [question](http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql), is no longer supported by MYSQL... Which means I HAVE to use one of your suggestions... Thank you ^w^... – dgood1 Oct 06 '13 at 01:25