0

I need to make a query (not another table, I'm trying to avoid that please) joining 3 tables. I have one table that specifies ownership schemes, like this:

+--------------------------------------------+
|id_scheme | scheme_name | registration_date |
+--------------------------------------------+

and another table which contains medical equipment, these have one ownership scheme and a supplier id, and finally another table which contains the suppliers, so, I need a query that can tell me for every supplier, how many equipments i have, and how many of these equipments I have in each ownership scheme category. I don't know how to write a query that use the rows of a table (all my ownership schemes) as a query result columns. So, the query can return something like this:

+-----------------------------------+
|supplier|equipments|acquired|leased|
+-----------------------------------+
|Philips |    50    |   13   |  30  |
+-----------------------------------+

or like this:

+----------------------------------------------+
|supplier|equipments|acquired|leased|commodatum|
+----------------------------------------------+
|Philips |    50    |   13   |  30  |     7    |
+-----------------------------------+----------+

and so on. Please, your help will be very appreciated.

EDIT I put an example of what i want in the image, the equipment and suppliers tables of course have more columns, but i thinks thats all i need to build the query

enter image description here

Taryn
  • 242,637
  • 56
  • 362
  • 405
hedka77
  • 107
  • 5
  • 17
  • Please give us the detail structure of your other tables with some records. This will help a lot to answer your question. – Mubin Shrestha Aug 07 '14 at 21:07
  • thanks, i already put the structure on the image – hedka77 Aug 07 '14 at 21:39
  • you can use case when...SELECT DISTINCT A.NAME SUPPLIER, COUNT(B.ID_EQUIPMENT) EUIPMENTS, CASE WHEN B.ID_SCHEMA = 1 THEN COUNT(ID_SCHEMA) END ADUIRIDO, CASE WHEN B.ID_SCHEMA = 2 THEN COUNT(ID_SCHEMA) END ARRENDAMIENTO -- THE LIST GOES ON , CASE WHEN IS THE BASIC IDEA FROM SUPPLIER_TABLE A INNER JOIN EUIPMENT_TABLE B ON A.ID_SUPPLIR = B.ID_SUPPLIER INNER JOIN OWNERSHIP_TABLE C ON B.ID_SCHEMA = C.ID_SCHEMA GROUP BY A.ID_SUPPLIER – Mubin Shrestha Aug 07 '14 at 21:56
  • The only problem is that "Adquirido" or "Arrendamiento" or any other ownership schema is not static, i mean, i need this to work dinamically even if I add more schemas and have to change the query everytime, because I am using these tables on a system and the info is always changing (the users can add more schemas as they want to) – hedka77 Aug 07 '14 at 22:11
  • but you give me an idea to solve it with a little programming! Thanks a lot! – hedka77 Aug 07 '14 at 22:14

1 Answers1

0

I hope i understand your question. Heres me trying to give an answer with an example.

SELECT name,author,title from customerstable,classicstable
WHERE customerstable.isbn = classicstable.isbn;

This would display a table that has data from two tables (customerstable and classicstable) whenever they share an isbn. Of course you would change your values with your data. But a more detailed structure would help for a better answer. maybe even the first few rows for each table you are trying to join in a query.

In your database the isbn could be replaced with ids that are the same throughout multiple tables.

Anthony Saldana Jr.
  • 150
  • 1
  • 5