Re: [pgsql-tr-genel] Re: [pgsql-tr-genel] Re: [pgsql-tr-genel] Mysql'dan Postgresql geçerken aldığım hata

On 06.04.2011 18:08, Emre Hasegeli wrote:
> 2011/4/6 kingemkurdam<kingemkurdam(at)gmail(dot)com>:
>> Aslında yapmak istediğim şey haberler diye bir view oluşturmak.
>> Mysql'da haberler view'i oluşturmak için çalıştırdığım sorgu şu şekilde.
>>
>> CREATE VIEW haberler AS SELECT
>> mod_haber.id AS id,
>> mod_haber.ad AS ad,
>> mod_haber.tarih AS tarih,
>> mod_haber.yayin AS yayin,
>> mod_haber.icerik AS icerik,
>> mod_haber.giris AS giris,
>> mod_haber.seo AS seo,
>> mod_haber.title AS title,
>> mod_haber.keywords AS keywords,
>> mod_haber.aciklama AS aciklama,
>> mod_haber.url AS url,
>> mod_haber.spot AS spot,
>> mod_haber.resim AS resim,
>> mod_haber.hit AS hit,
>> mod_haber.lang AS lang,
>> mod_kategori.ad AS kat,
>> mod_kategori.seo AS katseo,
>> mod_kategori.id AS kat_id,
>> mod_user.username AS username,
>> mod_user.id AS user_id,
>> mod_user.ad AS uad,
>> mod_user.soyad AS usoyad,
>> mod_user_group.ad AS groupad,
>> COUNT(mod_gallery.id) AS rcount
>> FROM (mod_haber
>> LEFT JOIN mod_user ON mod_haber.user_id=mod_user.id
>> LEFT JOIN mod_user_group ON mod_user.group_id=mod_user_group.group_id
>> LEFT JOIN mod_kategori ON mod_haber.kat_id=mod_kategori.id
>> LEFT JOIN mod_gallery ON mod_haber.id=mod_gallery.vid)
>> GROUP BY mod_haber.id
> Yazdığınız sorguda istediklerinizin SQL dilinde doğru anlatılmamış.
> Sizin GROUP BY kullanmanıza gerek yok. mod_gallery tablosunu COUNT'un
> içinde kullanmalısınız. Sorgunuz aşağıdaki gibi olmalı:
>
> CREATE VIEW haberler AS SELECT
> mod_haber.id AS id,
> mod_haber.ad AS ad,
> mod_haber.tarih AS tarih,
> mod_haber.yayin AS yayin,
> mod_haber.icerik AS icerik,
> mod_haber.giris AS giris,
> mod_haber.seo AS seo,
> mod_haber.title AS title,
> mod_haber.keywords AS keywords,
> mod_haber.aciklama AS aciklama,
> mod_haber.url AS url,
> mod_haber.spot AS spot,
> mod_haber.resim AS resim,
> mod_haber.hit AS hit,
> mod_haber.lang AS lang,
> mod_kategori.ad AS kat,
> mod_kategori.seo AS katseo,
> mod_kategori.id AS kat_id,
> mod_user.username AS username,
> mod_user.id AS user_id,
> mod_user.ad AS uad,
> mod_user.soyad AS usoyad,
> mod_user_group.ad AS groupad,
> COUNT(SELECT id
> FROM mod_gallery
> WHERE mod_haber.id = mod_gallery.vid) AS rcount
> FROM mod_haber
> LEFT JOIN mod_user ON mod_haber.user_id=mod_user.id
> LEFT JOIN mod_user_group ON mod_user.group_id=mod_user_group.group_id
> LEFT JOIN mod_kategori ON mod_haber.kat_id=mod_kategori.id

Yazdığınız şekilde sorgu yine gruplamamı istiyor.
Bu arada yazdığınız sorgu mysql'da tek bir satır getirdi.

*SQL error:*

ERROR: column "mod_haber.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: mod_haber.id AS id,
^

*In statement:*

CREATE VIEW haberler AS SELECT
mod_haber.id AS id,
mod_haber.ad AS ad,
mod_haber.tarih AS tarih,
mod_haber.yayin AS yayin,
mod_haber.icerik AS icerik,
mod_haber.giris AS giris,
mod_haber.seo AS seo,
mod_haber.title AS title,
mod_haber.keywords AS keywords,
mod_haber.aciklama AS aciklama,
mod_haber.url AS url,
mod_haber.spot AS spot,
mod_haber.resim AS resim,
mod_haber.hit AS hit,
mod_haber.lang AS lang,
mod_kategori.ad AS kat,
mod_kategori.seo AS katseo,
mod_kategori.id AS kat_id,
mod_user.username AS username,
mod_user.id AS user_id,
mod_user.ad AS uad,
mod_user.soyad AS usoyad,
mod_user_group.ad AS groupad,
COUNT('SELECT id FROM mod_gallery WHERE mod_haber.id = mod_gallery.vid') AS rcount
FROM mod_haber
LEFT JOIN mod_user ON mod_haber.user_id=mod_user.id
LEFT JOIN mod_user_group ON mod_user.group_id=mod_user_group.group_id
LEFT JOIN mod_kategori ON mod_haber.kat_id=mod_kategori.id