0

Please refer to this background question.

After constructing this COUNT, how would I then link each of these 'Prices' to, for instance, a column called 'Genre' in TableTwo?

e.g.

Table1: Prices, ID

Table2: Genre, ID

Example output:

PRICES, COUNT, Genre
--------------------
13.99,   2,   Horror
52.00,   3,   Comedy
1.99,    1,   Romance

I should hope this question is easy to follow however I will try to elaborate further on request! Cheers!

EDIT:

Yes, this is a much simpler version of what I'm trying to do. As said in the previous question, I have this field name that I want to count the instances of. Now that I have this answer(from the previous question), I now want to link this to another table that I have(to help me analyse some data a little better).

For sake of example, let's say we have a Blockbuster branch that has 2 suppliers. In TableOne I have 'Title'. I have now listed each unique value from Title and counted each one (So in the store I have a unique title called 'Dead Man's Shoes' and there is 10 copies. However, I also have a unique title called 'Touch Of Evil' and because this is more popular, there is 100 copies. I now want to see which supplier these two come from (From TableTwo). Therefore

Example output:

Title,            Count,   Supplier
------------------------------------
Dead Man's Shoes,  10,     Supplier1
Touch Of Evil,     100,    Supplier2

Does that help any better?

Community
  • 1
  • 1
Federer
  • 33,677
  • 39
  • 93
  • 121
  • 1
    Logically this seems wrong to me , as prices have no relation in Genre in real life. However this might simply be an example. The part which i don't get is .. what is "ID" in both tables, cause the previous question had no "ID" defined as well. So can u please elabore what "ID" represents and also in Table1 , you have "Prices", which i believe should be "Price", please clarify its otherwise. As "Prices" indicate your storing multiple values in it somehow. – Sabeen Malik Oct 22 '09 at 09:41
  • @Sab: My instinct tells me both IDs refer to the Movie/Media entity ID. Well, can't expect all table schema to be 'realistic', the question is on the technicality, not how it relates to real life. But still a good food for thought for `malcmcmul`. :) – o.k.w Oct 22 '09 at 09:47
  • @okw , yeah thats what i thought but then as u see he has grouped on Price in the previous question, the example output aint really possible, because 13.99 has count 2 which means 2 IDs and how can 2 IDs represent one genre .. hence my request for clarification. I am sure you have seen some "brialliant" DB designs here and there ;) so its always good to clarify before wasting your braincells :) – Sabeen Malik Oct 22 '09 at 09:51
  • @Sub: Haha yea, well you got me there. `malcmcmul`??? If you have the time, do enlighten us. :) – o.k.w Oct 22 '09 at 09:55
  • @Sab: In my answer I've assumed that the ID is some sort of genre ID. This means that the resultset can contain multiple prices for the same genre and vice-versa. If this isn't what the OP requires then they need to clarify. – LukeH Oct 22 '09 at 09:58
  • Extended it a little better. Let me know if that's not clear enough! :) – Federer Oct 22 '09 at 09:59
  • @malcmcmul: Yes now it makes more sense. – o.k.w Oct 22 '09 at 10:05

2 Answers2

2
 SELECT t1.Prices, COUNT(t1.ID) AS TheCount, t2.Genre
 FROM Table1 AS t1
     INNER JOIN Table2 AS t2
         ON t1.ID = t2.ID
 GROUP BY t1.Prices, t2.Genre
LukeH
  • 263,068
  • 57
  • 365
  • 409
0

You have to user JOIN function. Your query would look something like

SELECT * FROM prices JOIN genres ON ( prices.id = genres.id )

and the result would be what you desire. :)

More on this subject here.

Bogdan Constantinescu
  • 5,296
  • 4
  • 39
  • 50