Using COUNT and joining to another table

[mluken]

I am having problems writing a query - I am not even sure if this is possible to do. Here is how in this example the 2 tables look:

TBL_PART_DM
-----------
PART_ID (KEY)
PART_IMG
PART_NAME


TBL_PART_LOC
------------
PART_ID (KEY)
PART_LOC (KEY)



In this example, lets say that somebody searches by PART_NAME. What I want is to write a query that pulls back everything from the first table, as well as how many records there are in the second table for that part. My knowledge of agrovate functions isn't that great, but I am still not seeing a way to do this?

SELECT DISTINCT
  T1.PART_ID,
  T1.PART_NM,
  T1.PART_IMG,
  T2.PART_COUNT
FROM
  TBL_PART_DM T1,
  (
    SELECT
      COUNT(PART_LOC) AS PART_COUNT
    FROM
      TBL_PART_LOC
  ) T2
WHERE
  PART_NM = 'SOME SEARCH STRING'

T2 can only return one value since I am using an agrovate function, so I can't return a PART_ID along with the count. If that were the case, I could easily join them by saying WHERE T1.PART_ID=T2.PART_ID.

Is there even a way to do this?

Thanks!

 

[donutman]

I think this is what you want.

[Blue]SELECT[/Blue] Part_Name[Gray],[/Gray]Part_Id[Gray],[/Gray][Fuchsia]COUNT[/Fuchsia][Gray]([/Gray]Part_Loc[Gray])[/Gray] Part_Count
   [Blue]FROM[/Blue] Tbl_Part_DM DM [Blue]INNER[/Blue] [Blue]JOIN[/Blue] Tbl_Part_Loc L
   [Blue]ON[/Blue] DM.Part_Id[Gray]=[/Gray]L.Part_ID

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mluken]

Still doesn't appear to be working. I ran this query and it doesn't work:

SELECT 
  Part_Name,
  Part_Id,
  COUNT(Part_Loc) as Part_Count
FROM Tbl_Part_DM DM INNER JOIN Tbl_Part_Loc L
  ON DM.Part_Id=L.Part_ID

However, when I remove the COUNT line from the query it works so I know everything else is right - it just still doesnt like that:

SELECT 
  Part_Name,
  Part_Id
FROM Tbl_Part_DM DM INNER JOIN Tbl_Part_Loc L
  ON DM.Part_Id=L.Part_ID

Other thoughts?

 

[donutman]

Sorry forgot to group by.

[Blue]SELECT[/Blue] Part_Name[Gray],[/Gray]Part_Id[Gray],[/Gray][Fuchsia]COUNT[/Fuchsia][Gray]([/Gray]Part_Loc[Gray])[/Gray] Part_Count
   [Blue]FROM[/Blue] Tbl_Part_DM DM [Blue]INNER[/Blue] [Blue]JOIN[/Blue] Tbl_Part_Loc L
   [Blue]ON[/Blue] DM.Part_Id[Gray]=[/Gray]L.Part_ID
   [Blue]GROUP[/Blue] [Blue]BY[/Blue] Part_Name[Gray],[/Gray] Part_Id

That should do it.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mluken]

That didn't appear to fix it either. Here is the exact query I am running:

select distinct
  actr_nm,
  actr_id,
  actr_img,
  count(actr_id) as part_count
from
  db2_dvd_actr_dm t1
    inner join
  db2_dvd_actr_prts t2
    on
  t1.actr_id = t2.actr_id
where
  lcase(actr_nm) like '%john%'
group by
  actr_nm,
  actr_id

It still doesn't like the COUNT...

 

[donutman]

You have to group by actr_img also. You won't be able to if that is image data. If so then you have to leave that off and add it again with another join. You don't need the lcase() function unless your system is case sensitive and the distinct is of no use.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mluken]

Shoot - there is still something wrong. My server doesn't return a very helpful message either. This is what I have now:

select
  actr_nm,
  actr_id,
  count(actr_id) as part_count
from
  db2_dvd_actr_dm t1
    inner join
  db2_dvd_actr_prts t2
    on
  t1.actr_id = t2.actr_id
where
  actr_nm like '%john%'
group by
  actr_nm,
  actr_id

Still no luck though.

 

[donutman]

select
  actr_nm,
  count(t2.actr_id) as part_count
from
  db2_dvd_actr_dm t1
    inner join
  db2_dvd_actr_prts t2
    on
  t1.actr_id = t2.actr_id
where
  actr_nm like '%john%'
group by
  actr_nm

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]