Distint Select & Count Across Tables?

[MarkSmith99]

Hi,

I was wondering if anyone would be able to help with a issue I am having.

I have 2 tables set-up as below, both tables contain NumID and I need a statement that will take select the distinct NumID's (as it maybe in there more than once) from table1 then count how many times the same NumID appears in Table2.

I also need the statement to show the rest of the columns in table1 to in the results if possible.

[b]Table1		Table2[/b]

NumID		NumID
Date
Item
Item2

I have spent several hours trying to get this to work but being new to t-sql I'm struggling.

Any help would be great thanks in advance.

Mark

 

[chamilz]

try this..

select distinct a.NumID, count(b.NumID)NumID_Count
from Table_1
left join Table_2
on a.NumID = b.NumID
group a.NumID

I also need the statement to show the rest of the columns in table1 to in the results if possible.

DISTINCT command gives you only unique records based on the columns you have defined.

Column list (NumID,Date,Item,Item2) is NOT unique, qry returns duplicate records.

if the column list is unique, you could add other fields like this,

select distinct a.NumID,a.Date,a.Item,a.Item2 count(b.NumID)NumID_Count
from Table_1
left join Table_2
on a.NumID = b.NumID
group a.NumID

 

[chamilz]

it should be group by a.NumID

 

[SQLSister]

First you have a requirements problem. If you have multiple records in table1 for an id, then when you show the other fields how do you determine which record to choose?

"NOTHING is more important in a database than integrity." ESquared