Show my Adds in system whit onlye on image

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Show my Adds in system whit onlye on image

Author

Topic

Just me
Starting Member

1 Post

Posted - 2011-12-16 : 16:50:09

Hi all,

Iam developing a second-hand market portal where users can sell and buy used stuff.
I'm stuck with a sql expression and hope someone can help

I have 5 tables:

PROJEKT (in english: Project)
KATEGORI_L1 (in english: category 1)
KATEGORI_L2 (in english: category 2)
FOELGPROJEKTET (in english: follow)
KUNDE (user)
IMAGE (Images)

Category 1 is the main category, Category 2, subcategory.
PROJECT contents info aboutt Adds and IMAGE table contens references to Adds images.

I want to make a sql that retrieves information about ads I've created in the system.

I've actually done a sql and it works fine.

BUT my sql, show 3 image ref (IMAGE.IMG_REF) for one add (a add can have 3 images), i wish only to show the first ref of image from the image table. I waant onlye show 1 image ref group by Projekt ID

How can I change the sql so it only shows 1 image per refence.

quote:
SELECT
IMAGE.IMG_REF,
PJKT_ID as PROJEKTID,
KATEGORIE_L2.TEKST,
PROJEKT.OVERSKRIFT,
PROJEKT.PRIS,
PROJEKT.ANTAL_KLIK,
FOELGPROJEKTET.STATUS AS NYANNOBESKED,
PROJEKT.OPRETTET_DATO
FROM
KATEGORIE_L1,
KATEGORIE_L2,
KUNDE,

PROJEKT LEFT JOIN FOELGPROJEKTET ON FOELGPROJEKTET.ANNOREF = PROJEKT.PJKT_ID
Left JOIN IMAGE ON IMAGE.PROJEKT_REF = PROJEKT.PJKT_ID
WHERE
KATEGORIE_L1.KAT1_ID = KATEGORIE_L2.KAT2_REF AND
PROJEKT.KAT2_REF =KATEGORIE_L2.KAT2_ID AND
PROJEKT.KUNDE_REF = KUNDE.KUNDE_ID AND
KUNDE_REF=13
group by IMAGE.IMG_REF,FOELGPROJEKTET.STATUS,PROJEKT.PJKT_ID,KATEGORIE_L2.TEKST,PROJEKT.OVERSKRIFT,PROJEKT.PRIS,PROJEKT.ANTAL_KLIK,PROJEKT.OPRETTET_DATO
ORDER BY PROJEKT.OPRETTET_DATO,FOELGPROJEKTET.STATUS DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 01:54:20

GROUP BY ProjectID and take MIN() of ImageID (or primary key of image table).
then add a join to this from other tables


SELECT 
IMAGE.IMG_REF,
PJKT_ID as PROJEKTID,
KATEGORIE_L2.TEKST,
PROJEKT.OVERSKRIFT,
PROJEKT.PRIS,
PROJEKT.ANTAL_KLIK,
FOELGPROJEKTET.STATUS AS NYANNOBESKED,
PROJEKT.OPRETTET_DATO
FROM 
KATEGORIE_L1,
KATEGORIE_L2,
KUNDE ,
PROJEKT 
LEFT JOIN FOELGPROJEKTET 
ON FOELGPROJEKTET.ANNOREF = PROJEKT.PJKT_ID
Left JOIN (SELECT i1.*
FROM IMAGE i1
INNER JOIN (SELECT PROJEKT_REF,MIN(IMG_REF) AS MinImage
 FROM Image
 GROUP BY PROJEKT_REF)i2
ON i2.PROJEKT_REF = i1.PROJEKT_REF
AND i2.MinImage = i1.IMG_REF
)i
ON i.PROJEKT_REF = PROJEKT.PJKT_ID 
WHERE
KATEGORIE_L1.KAT1_ID = KATEGORIE_L2.KAT2_REF AND
PROJEKT.KAT2_REF =KATEGORIE_L2.KAT2_ID AND
PROJEKT.KUNDE_REF = KUNDE.KUNDE_ID AND
KUNDE_REF=13
ORDER BY PROJEKT.OPRETTET_DATO,FOELGPROJEKTET.STATUS DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 01:56:53

and for better clarity use ANSI join syntax
ie like


....
FROM 
KATEGORIE_L1
INNER JOIN KATEGORIE_L2
ON KATEGORIE_L1.KAT1_ID = KATEGORIE_L2.KAT2_REF 
INNER JOIN PROJEKT
ON PROJEKT.KAT2_REF =KATEGORIE_L2.KAT2_ID
INNER JOIN KUNDE 
ON PROJEKT.KUNDE_REF = KUNDE.KUNDE_ID   
....

rather than


FROM 
KATEGORIE_L1,
KATEGORIE_L2,
KUNDE ,
PROJEKT 
....
WHERE
KATEGORIE_L1.KAT1_ID = KATEGORIE_L2.KAT2_REF AND
PROJEKT.KAT2_REF =KATEGORIE_L2.KAT2_ID AND
PROJEKT.KUNDE_REF = KUNDE.KUNDE_ID
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources