Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Turning Two Tables Into A Matrix

matrix

  • Please log in to reply
7 replies to this topic

#1 mctim

mctim

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 110 posts

Posted 09 July 2012 - 08:33 PM

Here's the problem say I have 3 users sam, joe, and bob stored in a database. I also have 3 tags describing food that sam, joe, and bob like. For this example lets say I have Chinese, Mexican, and italian. Now sam, joe, and bob have all ate several dishes that could be classified into one of these 3 categories, so what I want to construct a 3x3 matrix where the columns are the categories and the rows are the users. The data in that matrix will be the number of dishes each user has ate under that category. For example say Bob is the first row, and he happened to eat one Mexican Dish, which happens to be the category associated w/ the first column. Then the matrix element at 1x1 should equal 1. All that explained I really have no idea how to build a query that would return that or if that it is even possible to do in one query.

Is it possible? If so how do I do it?
  • 0

#2 kernelcoder

kernelcoder

    CC Devotee

  • Expert Member
  • PipPipPipPipPipPip
  • 990 posts
  • Location:Dhaka
  • Programming Language:C, Java, C++, C#, Visual Basic .NET
  • Learning:Objective-C, PHP, Python, Delphi/Object Pascal

Posted 10 July 2012 - 12:21 AM

How about this approach: Let say you have 3 tables -- People, Foods and EatenRecords.

People table's columns: ID (primary key) FirstName, LastName (you may add other columns depending on your necessity, as example age, address, email, phone etc)

Foods table 's columns: ID (primary key), CategoryName (you may add other columns depending on your necessity, as example price, region etc)

EatenRecords table's columns: PeopleID, FoodID, Count (you may add other columns depending on your necessity, as example date of eating etc)

Now your goal is: View all the peoples with there food category and food eaten count.

Following is a sample query to meet the goal.
SELECT P.FirstName, F.CategoryName, R.Count
FROM People P, Foods F, EatenRecords R
WHERE R.PeopleID = P.ID AND R.FoodID = F.ID;

Here is a sample snapshot of the testing I did with MySQL database.

People table's data:
+----+-----------+----------+
| ID | FirstName | LastName |
+----+-----------+----------+
|  1 | Bob       | Puff     |
|  2 | Joe       | King     |
|  3 | Sam       | Sue      |
+----+-----------+----------+

Foods table's data:
+----+--------------+
| ID | CategoryName |
+----+--------------+
|  1 | Italian      |
|  2 | Mexican      |
|  3 | Chinnese     |
+----+--------------+


Data after executing the above query :
+-----------+--------------+-------+
| FirstName | CategoryName | Count |
+-----------+--------------+-------+
| Bob       | Mexican      |     1 |
| Bob       | Chinnese     |     2 |
| Joe       | Italian      |     2 |
+-----------+--------------+-------+

  • 0

#3 mctim

mctim

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 110 posts

Posted 10 July 2012 - 09:26 AM

That not exactly what I'm wanting though. Taken the 3 tables shown above I'm hoping to get something more like this:

|--------| Mexican | Chinese | Italian |
+------+----------+------------+---------+
|-Sam-|-----0-----|------0-----|---- 0---|
|-Bob--|-----1-----|------2-----|---- 0---|
|-Joe--|-----0-----|------0-----|---- 2---|
+------+----------+------------+---------+
  • 0

#4 kernelcoder

kernelcoder

    CC Devotee

  • Expert Member
  • PipPipPipPipPipPip
  • 990 posts
  • Location:Dhaka
  • Programming Language:C, Java, C++, C#, Visual Basic .NET
  • Learning:Objective-C, PHP, Python, Delphi/Object Pascal

Posted 10 July 2012 - 06:42 PM

Well, to meet your goal, let us change our table's schema


People table's columns: ID (primary key), Name

Foods table 's columns: PeopleID, Mexican, Chinese, Italian (those are of type integer/number)

And the query is:

SELECT P.Name, F.Mexican, F.Chinese, F.Italian
FROM People P, Foods F
WHERE P.ID = F.PeopleID;

  • 0

#5 mctim

mctim

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 110 posts

Posted 10 July 2012 - 11:53 PM

I see what your doing here but here's the last problem I wont know all the food categories ahead of time. So from my understanding of sql (which could be vastly incorrect) i wont be able to make a table that could properly represent the categories. Also, I'm not particularly interested in who ate what just what got ate. I said that to point out that the names column in the previous post is just there for reference. I imagine the 3 table looking soething like this.

+-----------+--------------+-------+
| FirstName | CategoryName | Dish Id|
+-----------+--------------+-------+
| Bob	 | Mexican	 |	 1 |
| Bob	 | Chinnese |	 2 |
| Joe	 | Italian	 |	 3 |
+-----------+--------------+-------+

Where the name and the dish id are the primary keys that identify the rows, and I want to extract the count of each category for each user, and if possible id like to do it in one query
  • 0

#6 kernelcoder

kernelcoder

    CC Devotee

  • Expert Member
  • PipPipPipPipPipPip
  • 990 posts
  • Location:Dhaka
  • Programming Language:C, Java, C++, C#, Visual Basic .NET
  • Learning:Objective-C, PHP, Python, Delphi/Object Pascal

Posted 11 July 2012 - 01:52 AM

+-----------+--------------+-------+
| FirstName | CategoryName | Dish Id|
+-----------+--------------+-------+
| Bob	 | Mexican	 |	 1 |
| Bob	 | Chinnese |	 2 |
| Joe	 | Italian	 |	 3 |
+-----------+--------------+-------+

You need to print/show like this to user perhaps through your GUI, in a file or maybe just in console, right? If yes, you can design your database as I said in my first comment and in that way you can add more foods category in to the Foods table later when you need. And after getting the data (using the query in my first comment), you need to show it in your output window/file as you want.
  • 0

#7 mctim

mctim

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 110 posts

Posted 11 July 2012 - 11:11 PM

No I'm literally trying to make a matrix to perform linear algebra on, and all the tables are moving targets. Meaning the number of people are growing the number of categories are growing and the the dishes eaten are growing. Like I said I could figure out what Bob has been eating and the tally up the counts for each category, but I feel like there has to be a more elegant solution.
  • 0

#8 kernelcoder

kernelcoder

    CC Devotee

  • Expert Member
  • PipPipPipPipPipPip
  • 990 posts
  • Location:Dhaka
  • Programming Language:C, Java, C++, C#, Visual Basic .NET
  • Learning:Objective-C, PHP, Python, Delphi/Object Pascal

Posted 12 July 2012 - 02:59 AM

You can even create that matrix with the solution in my first comment. However in my last comment I said about output tobe as GUI for file. But in this case the output is tobe a matrix. Let us go with example again.

People table's data:

+----+-----------+----------+
| ID | FirstName | LastName |
+----+-----------+----------+
|  1 | Bob       | Puff     |
|  2 | Joe       | King     |
|  3 | Sam       | Sue      |
+----+-----------+----------+



Foods table's data:

+----+--------------+
| ID | CategoryName |
+----+--------------+
|  1 | Italian      |
|  2 | Mexican      |
|  3 | Chinese     |
+----+--------------+



EatenRecords table's data:

+----------+--------+-------+
| PeopleID | FoodID | Count |
+----------+--------+-------+
|        1 |      2 |     1 |
|        1 |      3 |     2 |
|        2 |      1 |     2 |
|        2 |      1 |     4 |
|        1 |      1 |     4 |
|        1 |      2 |     4 |
|        3 |      3 |     4 |
|        3 |      2 |     4 |
+----------+--------+-------+


Now if we run the query
SELECT P.FirstName, F.CategoryName, Sum(R.Count) FROM People P, Foods F, EatenRecords R WHERE R.PeopleID = P.ID AND R.FoodID = F.ID GROUP BY P.FirstName, F.CategoryName;
, we get the result:

+-----------+--------------+--------------+
| FirstName | CategoryName | Sum(R.Count) |
+-----------+--------------+--------------+
| Bob       | Chinese     |            2 |
| Bob       | Italian      |            4 |
| Bob       | Mexican      |            5 |
| Joe       | Italian      |            6 |
| Sam       | Chinese     |            4 |
| Sam       | Mexican      |            4 |
+-----------+--------------+--------------+

Now we just need to create the matrix you want from this data. To do this consider this result as a 2D array which is easy in any web scripting language, e.g. in PHP. This array always have 3 columns. And in the array all the array[i][0] items are the names, all the array[i][1] items are food category and array[i][2] items are the count of eaten food for a specific category for a specific people. Note that here index i will be from 0 to the array row count minus 1. ..........Are you with me? If not, please read the things again.

Now find the number of different people in the array first column and number of different food-category in the second column -- this are the number of rows and columns for your matrix. If we consider PHP, your matrix will be an associative array where the key will be the name of the people and the value will be another associative array which key will be the food category and the value will be the food-eaten count. . ..........Are you with me? If not, please read the things again.

Now the logic to create the matrix. Now iterate over the each row of the SQL query result. For the people name, Check if the name is already there in your matrix (associative array's key).
  • If not, add an entry in the matrix for that name and the value as a new associative array (add the food category name and food-eaten count in this associative array).
  • If yes (this will happen for the subsequent iteration for the same name), get the associative array that exist as the value for this people. Add the food category as the key and food-eaten count as the value in this array.
I hope now you will be able to write the code for the logic. Note that for the food category that will not exist for a people, you have consider the food-eaten count as zero.

For the above example, the associative array (your expected matrix) will be as follows.
[color=#000000][font='courier new'][size=3]$matrix= array[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  ([/size][/font][/color]
[color=#000000][font='courier new'][size=3]  "Bob"=>array[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  ([/size][/font][/color]
[color=#000000][font='courier new'][size=3]  "Chinese"=>2,[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  "Italian"=>4,[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  "Mexican"=>5[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  ),[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  [/size][/font][/color][color=#000000][font='courier new'][size=3]"Joe"=>array[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  ([/size][/font][/color]
[color=#000000][font='courier new'][size=3]  "Chinese"=>0,[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  "Italian"=>6,[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  "Mexican"=>0[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  ),[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  [/size][/font][/color][color=#000000][font='courier new'][size=3]"Sam"=>array[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  ([/size][/font][/color]
[color=#000000][font='courier new'][size=3]  "Chinese"=>4,[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  "Italian"=>0,[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  "Mexican"=>4[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  )[/size][/font][/color]
[color=#000000][font='courier new'][size=3]  );[/size][/font][/color]

  • 0





Also tagged with one or more of these keywords: matrix

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download