tags:

views:

48

answers:

2

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?

+2  A: 
 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
A: 

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