CODEWITHSUNDEEP
sql-server-2008join
phill
phill

Reputation: 13854

sql left join with distinct correct?

I'm trying to do a join between tables 1 and 2 which have a 1 to many relationship.

table1 has the following fields

table2 has the following fields

I want to show all items in table2 with their corresponding items in table1 grouped by table2.userfld1 

select distinct t2.userfld1, t2.status, t2.description, t1.createdate, t1.contact
from table2 as t2 left join table1 as t1 
      on t2.tkey = t1.tkey 
group by t2.userfld1

is this correct?

Upvotes: 1

Views: 960

Answers (2)

xil3
xil3

Reputation: 16439

I think what you're looking for is order by, not group by, and I also fixed your query:

select t2.userfld1, t2.status, t2.description, t1.createdate, t1.contact
where table2 t2 left join table1 t1
      on t2.tkey = t1.tkey 
order by t2.userfld1

Is this what you were looking for?

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838896

No that's not correct, you can't select columns that aren't in the group by unless they are contained in an aggregate function. And I think what you are asking for doesn't even make sense. My best guess is that you mean ORDER BY, not GROUP BY:

SELECT DISTINCT t2.userfld1, t2.status, t2.description, t1.createdate, t1.contact
FROM table2 t2
LEFT JOIN table1 t1
ON t2.tkey = t1.tkey 
ORDER BY t2.userfld1

Three other errors that I've fixed:

  • SELECT ... FROM not SELECT ... WHERE
  • You should join with a table, not a column.
  • You had no aliases after the table names, but later refer to these missing aliases.

Upvotes: 1

Related Questions