COUNT and JOIN's - Simple stuff.

[Crooked]

Hey im having some troubles writing a simple query line that will do 2 different things in 2 different query's

 

 

here is a picture showing my tables and how i wish it to print out the information:

 

I got another one that is simple enough as the above - id/username/information - which contains a id and a username and obviously some sort of information on the dude.

 

I use those 3 (quickly made but matches the normal tables) tables to sort of 2 types of list(s).

 

 

On the first one ( Higehst to lowest liked cake i do :

SELECT COUNT(*) as countof, fk_cake FROM boughtcakes GROUP BY fk_cake ORDER BY countof DESC

) Which works and prints out the highest to lowest like :

 

What i need is some sort of WHERE or a different query that will print out the cakes name and description depending on which month was the previous month like :: WHERE DATE = $previousmonth

So i can make a list of previous months most liked cakes.

 

 

The 2nd issue:

 

Using the same tables im trying to pull out information on which cakes which user has liked.

 

For example as you can see User number 1 has liked all three cakes - nr 1, nr 2 and nr 3.

 

i thought it would be simple all i had to do was JOIN the users id on the shows fk_user row, so i ran it in the SQL in phpmyadmin and it came out with :

 

 

The sql string i use is :

SELECT * FROM boughtcakes

INNER JOIN cakes on boughtcakes.id = boughtcakes.fk_cake

INNER JOIN users on cakes.id = boughtcakes.fk_user

 

If you guys can help me out on this i would really appreciate it

 

thanks in advance

[kickstart]

Hi

 

For the first one try something like this:-

 

SELECT a.Id, a.name, a.description, EXTRACT(
YEAR_MONTH FROM b.date ) AS PopularityMonth, COUNT( * ) AS countof
FROM cakes a
LEFT OUTER JOIN boughtcakes b ON a.id = b.fk_cake
GROUP BY a.Id, a.name, a.description, PopularityMonth
HAVING PopularityMonth = '201104'
ORDER BY countof DESC

 

That will give you the list of popularity for the specified year / month (ie, 2011/04 here).

 

2nd bit looks to me like you have got confused on which columns you are using to do the join.

 

SELECT * 
FROM users
INNER JOIN boughtcakes ON users.id = boughtcakes.fk_user
INNER JOIN cakes ON boughtcakes.fk_cake =  cakes.id

 

All the best

 

Keith

[Crooked]

Thanks for the help

 

the 2nd one works, and yeh i do have some small issues with knowing which fields to join with which

 

 

The first one is bringing up errors

 

#1054 - Unknown column 'a.description' in 'field list'

 

and i notice you have a few of those a.Id and a.Name,

 

i think im missing something here

[kickstart]

Hi

 

Ah, should be descriptions rather than description. Typo from when I was playing around.

 

A a is for an alias. The line FROM cakes a is specifying the table cakes with an alias of a. This way rather than having to write out the full table name with fields belonging to that table you can just use the short alias name instead.

 

All the best

 

Keith

[Crooked]

Thanks for the help, i sorta figured out why the error was accuring

 

 

1 thing i don't understand, when i change all the a's to = cakes and all the b's to boughtcakes and try to orgenize it, it comes up with different result.

 

ill try and post it back tomorow after some more tests.

 

But for now it works and ive been able to tweak it to my needs. Thanks again

[Muddy_Funster]

...

1 thing i don't understand, when i change all the a's to = cakes and all the b's to boughtcakes and try to orgenize it, it comes up with different result.

...

 

shame on you Kickstart  needless use of single letter alliases  :'(

[kickstart]

shame on you Kickstart  needless use of single letter alliases  :'(

 

I'm lazy and don't like typing long column names .

 

To Crooked, the use of alias' shouldn't affect the results. Suspect you have missed one. Paste up how you have changed it and I will have a look.

 

All the best

 

Keith

[fenway]

I actually despite the omission of the AS for specifying aliases -- too error-prone.