Refactoring SQL statment

[AGus01]

Can any one help me refactor this SQL statment so I can read it?

    SELECT Tblevents.eventid, Tblevents.eventname, Tblevents.details, Tblevents.date, qry_home_cs_all.CountOfcontactid AS Totalatt, qry_CS_status_1.CountOfContactid AS Uni, qry_CS_status_2.CountOfContactid AS Biz, qry_CS_status_3.CountOfContactid AS Other, Tblevents.event_type FROM (((qry_CS_status_1 RIGHT JOIN Tblevents ON qry_CS_status_1.eventid=Tblevents.eventid) LEFT JOIN qry_CS_status_3 ON Tblevents.eventid=qry_CS_status_3.eventid) LEFT JOIN qry_CS_status_2 ON Tblevents.eventid=qry_CS_status_2.eventid) LEFT JOIN qry_home_cs_all ON Tblevents.eventid=qry_home_cs_all.eventid WHERE (((Tblevents.event_type)=2)) ORDER BY Tblevents.eventid DESC;

 

[feherke]

Hi

To improve readability I suggest to :
[ul]
[li]remove pointless parenthesis ( () )[/li]
[li]use aliases[/li]
[li]do not mix [tt]left[/tt] and [tt]right[/tt] [tt]join[/tt]s[/li]
[li][tt]join[/tt] with [tt]using[/tt] clause when possible[/li]
[/ul]
So this is the look I prefer :

[b]select[/b]
e.eventid,e.eventname,e.details,e.date,e.event_type,
h.CountOfcontactid [b]as[/b] Totalatt,
s1.CountOfContactid [b]as[/b] Uni,s2.CountOfContactid [b]as[/b] Biz,s3.CountOfContactid [b]as[/b] Other 

[b]from[/b] Tblevents e
[b]left[/b] [b]join[/b] qry_CS_status_1 s1 [b]using[/b] (eventid)
[b]left[/b] [b]join[/b] qry_CS_status_3 s3 [b]using[/b] (eventid)
[b]left[/b] [b]join[/b] qry_CS_status_2 s2 [b]using[/b] (eventid)
[b]left[/b] [b]join[/b] qry_home_cs_all h [b]using[/b] (eventid)

[b]where[/b] e.event_type=2

[b]order[/b] [b]by[/b] e.eventid [b]desc[/b]

Note : is possible that I accidentally messed up the statement during editing.

Feherke.

http://rootshell.be/~feherke/

 

[feherke]

Hi

I just found this, it may help you to choose your way between SQL formatting methods. The free online SQL Formatter.

Feherke.

http://rootshell.be/~feherke/