Left join question

[bloo_fish]

EDIT: ok a new problem has now arisen, please my posts below for information, thanks for looking


Ok, i have successfully managed to preform a left join on two of my tables, then i relised that i needed a third table, and it's all kinda gone out of the window from there

setting the scene

tables:

categories
id - pk
name

tutorials
id - pk
name

tutorial_lookup
cid
tid
(primary key = both)

(tables have been simplifyed btw, but thats all you should need to know)

what i know want to do is get a list of categories, with the number of tutorials in them, and also list tutorials under them

so far i have managed the first two steps (listing categories with num of tutorials)

i have used

SELECT categories.name, categories.id AS catid,
COUNT(tid) AS numtut
FROM categories LEFT JOIN tutorial_lookup
ON cid = categories.id
GROUP BY categories.id
;

now i need to add the tutorials table to this and retrieve the id and name fields.

I have tried a few things, but the problem is, without knowing which option to use, i dont even now if i was close

i have tried adding another left join and just another table seperated with a coma, but neither seemed to work


Hopefully somebody wise can helpme

Thanks for looking/contributing

[lilleman]

Hi

I don't know if I've understand what you want to accomplish but I'll give it a go.

SELECT tC.name, tC.id AS catid, tT.name AS tutName, tT.id AS tutId, COUNT(tL.tid) AS numtut
FROM categories tC
  LEFT JOIN tutorial_lookup tL ON cid = categories.id
  LEFT JOIN tutorials tT ON tutorials.id = tL.tid
GROUP BY categories.id

Yours, Erik.

[bloo_fish]

Hi

I don't know if I've understand what you want to accomplish but I'll give it a go.

SELECT tC.name, tC.id AS catid, tT.name AS tutName, tT.id AS tutId, COUNT(tL.tid) AS numtut
FROM categories tC
  LEFT JOIN tutorial_lookup tL ON cid = categories.id
  LEFT JOIN tutorials tT ON tutorials.id = tL.tid
GROUP BY categories.id

Yours, Erik.

Cheers M8, that works almost perfectly, i had to make a few little changes, i changed name of tut_look up to t.TL and also changed one of the full names down to an t.* to make it work

Thanks for the help, and i never knew about the t.* naming thing before, really has sorted out my code now

Well thanks again, and the sql that worked was

SELECT tC.name, tC.id AS catid, tT.title, tT.id AS tutid, 
COUNT(tTL.tid) AS numtut
FROM categories tC
LEFT JOIN tutorial_lookup tTL ON tTL.cid = tC.id
LEFT JOIN tutorials tT ON tT.id = tTL.tid
GROUP BY tC.id

[r937]

unpredicable results await those whose
SELECT list contains non-aggregate columns
not in the GROUP BY

[bloo_fish]

unpredicable results await those whose
SELECT list contains non-aggregate columns
not in the GROUP BY

Hey dude, i'm still learning this stuff really, so could you care to expand on that a little, maybe any articles ect... i could read up on it?

[r937]

see 13.9.3 GROUP BY with Hidden Fields --

MySQL extends the use of GROUP BY so that you can use columns or
calculations in the SELECT list that don't appear in the
GROUP BY clause. This stands for any possible value for
this group... Do not use this feature if the
columns you omit from the GROUP BY part are not unique in
the group! You will get unpredictable results.

emphasis by mysql, not me

[bloo_fish]

see 13.9.3 GROUP BY with Hidden Fields --emphasis by mysql, not me

Ok well thansk for that, its defentaly somthing i will be looking into, i seem to have hit a small problem with this query thou, it seems to be only showing me one result per category, and i need it to show all results per category. I have been playing around with this like constantly, since you guys helped me, but i still cant figure it out

any ideas would be much appreciated

[r937]

"get a list of categories, with the number of tutorials in them, ..."

select categories.name     as catname
     , categories.id       as catid
     , count(tid)          as numtut
  from categories 
left outer
  join tutorial_lookup
    on categories.id
     = tutorial_lookup.cid
group 
    by categories.name
     , categories.id

"... and also list tutorials under them"

select categories.name     as catname
     , categories.id       as catid
     , tutorials.name      as tutname
     , tutorials.id        as tutid
  from categories 
left outer
  join tutorial_lookup
    on categories.id
     = tutorial_lookup.cid
left outer
  join tutorials
    on tutorial_lookup.tid     
     = tutorials.id

now, it appears as though you wanted to combine these results

that's possible, with UNION

select categories.name     as catname
     , categories.id       as catid
     , 1                   as sortseq
     , count(tid)          as numtut
     , cast(null as varchar) as tutname 
     , cast(null as integer) as tutid  
  from categories 
left outer
  join tutorial_lookup
    on categories.id
     = tutorial_lookup.cid
group 
    by categories.name
     , categories.id 
union all
select categories.name     
     , categories.id      
     , 2               
     , cast(null as tinyint)
     , tutorials.name    
     , tutorials.id     
  from categories 
left outer
  join tutorial_lookup
    on categories.id
     = tutorial_lookup.cid
left outer
  join tutorials
    on tutorial_lookup.tid     
     = tutorials.id 
order
    by catname
     , catid
     , sortseq
     , tutname 
     , tutid

[bloo_fish]

Hey r937 - Firstly, Thanks a lot for the reply, looks like it would have taken a bit of your time to come up with that, I am very grateful, but the UNION query doesn’t seem to work (MySQL command prompt).

The error I receive is



and the second problem is i am terribly confused by your MySQL query statement, so i am unable to fix this error, please could you try and explain it to me, then i might be able to learn how to re-create similar queries, and broaden my knowledge

Thanks again for the help, it’s very much appreciated

[r937]

CAST was added in mysql 4.0.2.

UNION is implemented in mysql 4.0.0.

both are explained quite adequately in the online docs

the two individual queries i gave you should be easy enough
to understand -- one counts the tutorials in each category,
the other lists them

the UNION query combines the two results by ensuring that
the result sets are union-compatible, which means the same
number of columns, same datatype (which is why CAST is used,
to ensure datatype compatibility)

[bloo_fish]

Ok, cheers m8, i'm downloading 4.1 now (couldn't see 4.02 for win, still only had 4.0.18 which is what i'm running)

and now i'm of to read some online docs about that query

Thanks again for all the help