20. august 2007 - 15:33Der er
6 kommentarer og 1 løsning
Justering af query
Hej,
Har denne lettere omfattende query, som jeg ikke kan få til at spille. Problemet er at den ikke tager rækker i "category" med når "battle" er tom. Hvordan kan tilpasse, så den viser alle rækker i category?
select category.category_id, category.name,stc.antal,section.name as catName,count(battle.battle_id) as numbattles from used_battles inner join battle on battle.battle_id = used_battles.battle_id right join category on battle.category_id = category.category_id inner join section on category.section_id = section.section_id inner join (select count(*) as antal,category_id from star_to_category group by category_id)stc on stc.category_id =category.category_id group by category.category_id ,category.name,section.name,stc.antal order by category_id asc
Ved brug af inner join, afgrænser du antal records, til kun der records hvor der er match på join kriterie. Ved brug af left join eller right join, kan du bede om alle records fra tabellen på henholdsvis venstre/højre siden af join.
Skulle du bruge en left join, i stedet for rigth join på category?
select category.category_id, category.name,stc.antal,section.name as catName,count(battle.battle_id) as numbattles from used_battles inner join battle on battle.battle_id = used_battles.battle_id left join category on battle.category_id = category.category_id inner join section on category.section_id = section.section_id inner join (select count(*) as antal,category_id from star_to_category group by category_id)stc on stc.category_id =category.category_id group by category.category_id ,category.name,section.name,stc.antal order by category_id asc
Et hurtig omskrevet forslag, hvor du starter med category, hvis det er din 'hovedtabel':
select category.category_id, category.name,stc.antal,section.name as catName,count(battle.battle_id) as numbattles from category left join battle on battle.category_id = category.category_id left join used_battles on battle.battle_id = used_battles.battle_id left join section on category.section_id = section.section_id left join (select count(*) as antal,category_id from star_to_category group by category_id)stc on stc.category_id =category.category_id group by category.category_id ,category.name,section.name,stc.antal order by category_id asc
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
