Need help with SQL
Learner
We have a Tickets table and a Comments table in our work work horus
tracking system. Now we have a need to pull the data from Tickets and
Comments tables where ticked_id is the foreign key in the Comments
table such that the recent comments should be picked. This can be done
by doing a GROUP BY t.id on the Tickets table. This also eliminates
all the duplicate records that matched the equi join condition. The
below SQL seem like working as expected
Here is the SQL I am trying to come up with.
SELECT t.ID, t.Summary,
t.Description,t.Priority,t.C_Benefit,t.C_First_Estimate,t.C_Level_Two_Estimate,t.Time_Spent,t.C_Percent_Complete,t.C_Project_Status,c.body,
c.updated_at
FROM tickets t, comments c
WHERE t.id = c.ticket_id
group by t.id
Can some one please help me understand if this is the way to go or can
it be written more professionally.
Thanks,
L
SetonSoftware
> Hi,
>
> We have a Tickets table and a Comments table in our work work horus
> tracking system. Now we have a need to pull the data from Tickets and
> Comments tables where ticked_id is the foreign key in the Comments
> table such that the recent comments should be picked. This can be done
> by doing a GROUP BY t.id on the Tickets table. This also eliminates
> all the duplicate records that matched the equi join condition. The
> below SQL seem like working as expected
>
> Here is the SQL I am trying to come up with.
>
> SELECT t.ID, t.Summary,
> c.updated_at
> FROM tickets t, comments c
> WHERE t.id = c.ticket_id
> group by t.id
>
> Can some one please help me understand if this is the way to go or can
> it be written more professionally.
>
> Thanks,
>
> L
Going forward, please post table structures/DDL to recreaet the
tables.
This is the better way to handle JOINs, by using explicit JOIN
language.JOINing in the WHERE clause is still supported by obsolete.
SELECT t.ID, t.Summary,
t.Description,t.Priority,t.C_Benefit,t.C_First_Estimate,t.C_Level_Two_Estim
ate,t.Time_Spent,t.C_Percent_Complete,t.C_Project_Status,c.body,
c.updated_at
FROM tickets t
LEFT OUTER JOIN comments c ON t.id = c.ticket_id
It sounds like there are multiple comments per ticket and you only
want the latest one. Is this the case? If so you wouldn't need a GROUP
BY to acccomplish that. You could do it with a co-related subquery.
Carl
Erland Sommarskog
> We have a Tickets table and a Comments table in our work work horus
> tracking system. Now we have a need to pull the data from Tickets and
> Comments tables where ticked_id is the foreign key in the Comments
> table such that the recent comments should be picked. This can be done
> by doing a GROUP BY t.id on the Tickets table. This also eliminates
> all the duplicate records that matched the equi join condition. The
> below SQL seem like working as expected
>
> Here is the SQL I am trying to come up with.
>
> SELECT t.ID, t.Summary,
> t.Description,t.Priority,t.C_Benefit,t.C_First_Estimate,
> t.C_Level_Two_Estimate,t.Time_Spent,t.C_Percent_Complete,
> FROM tickets t, comments c
> WHERE t.id = c.ticket_id
> group by t.id
>
>
> Can some one please help me understand if this is the way to go or can
> it be written more professionally.
Else I cannot comment. If there are multiple comments on a ticket, don't
you want view them all? Or only the "recent"? What are "recent"?
Is usually help if you post:
o CREATE TABLE statements for your table.
o INSERT statements with sample data.
o The desired result given the sample.
o A short explanation of why you want that data.
o Which version of SQL Server you are using.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx