Need help with mysql query

[mabus]

Hello,

 

I have mysql query problems that I can't seem to figure out yet. the scenario involves two tables, and the objective is to be able come up with an uput that

combines the reults from the two related tables. here's the scenario.

 

ViewType (Id, Name) < --- this is a lookup table

Table B (Id, ProfileId, Date, ViewTypeId) <-- ViewTypeId field is a foreign key associated with ViewTypeTable

 

Now, what I am aimign for is to come up with results that summarizes according the month and the view type.

So the table should have these columsn "Date", "Number of Views", "ViewType 1 view" , "View Type 2 view"

 

Please share your ideas to me.

 

Thanks in advance...

[fenway]

Why not join the tables?

[mabus]

hhhmm..joins are too expensive, os I'm putting that as a last resort

 

[corbin]

I would consider joins your best resort.

 

 

It's either joins or 23890128901890752374890123748901273589012734890723847128903758901237 queries.

[mabus]

ahahahhahaha..  thats fun.. hhhmm ..I'm not too good at joins. can anyone give me a sample query on this?

 

thanks in advance guys, I really appreciate it

[Barand]

SELECT DATE_FORMAT(b.Date, '%Y %m) as yearmonth, 
    COUNT(*) as totalviews,
    GROUP_CONCAT(t.Name) as views
FROM TableB b
    INNER JOIN ViewType t ON b.ViewTypeId = t.Id
GROUP BY yearmonth

[mabus]

thanks for that dude. I appreciate it..

[mabus]

This one works dude. The "yearmonth" , and "totalviews" columns that it produces are correct , except for the lasts ones. What I'd like to have is to have different columns for the view types too. Got any query suggestions on how I can I achieve that?

 

again, thanks in advance.

[Barand]

http://www.phpfreaks.com/forums/index.php/topic,222873.msg1025547.html#msg1025547