SQL statement question

[cilu]

I have a table with two fields ID and Name, with possible multiple entries for the same name. Here is an example:

ID   Name
1    aaa
2    bbb
3    bbb
4    ccc
5    aaa

Now, I want to know if there is possible to get with a single SELECT all the names and how many times they appear in the table:

aaa    2
bbb    2
ccc    1

Thank you.

[Krzemo]

SELECT Name,COUNT(*) FROM MyTable GROUP BY Name ORDER BY Name

Why not?

Best regards,
Krzemo.

[cilu]

So GROUP BY is the key... Thank you.

Now, this is question part 2:
I have tree tables, let's call them table1, table 2 and table 3:

TABLE1:
ID   Name
1    aaa
2    bbb
3    ccc

TABLE2
ID   Name
1    pro1
2    pro2
3    pro3

TABLE3
ID   ID_TABLE1   ID_TABLE2
1    1                   2
2    2                   2
3    3                   1

I want to make a single SELECT and retrieve this:

[1]   aaa    pro2
[2]   bbb    pro2
[3]   ccc     pro1

I tried this but it doesn't work:

SELECT  T1.Name, T2.Name FROM Table1 as T1, Table2 as T2, Table3 as T3
   WHERE T3.ID_Table1 = T1.ID and T3.ID_Table2 = T2.ID

[Krzemo]

I tried this but it doesn't work:

Really?
For me it works perfectly see below(MsSQL database Northwind):

CREATE TABLE Table1 
 (
   ID INT PRIMARY KEY
  ,Name VARCHAR(50)
 )
GO
CREATE TABLE Table2 
 (
   ID INT PRIMARY KEY
  ,Name VARCHAR(50)
 )
GO
CREATE TABLE Table3 
 (
   ID INT PRIMARY KEY
  ,ID_Table1 INT REFERENCES Table1(ID)
  ,ID_Table2 INT REFERENCES Table2(ID)
 )
GO
INSERT INTO Table1 VALUES( 1,'aaa')
INSERT INTO Table1 VALUES( 2, 'bbb')
INSERT INTO Table1 VALUES( 3 ,'ccc')
INSERT INTO Table2 VALUES( 1,'pro1')
INSERT INTO Table2 VALUES( 2,'pro2')
INSERT INTO Table2 VALUES( 3,'pro3')
INSERT INTO Table3 VALUES(1 , 1  , 2)
INSERT INTO Table3 VALUES(2 , 2  ,  2)
INSERT INTO Table3 VALUES(3 , 3  , 1)
GO
SELECT  T1.Name, T2.Name FROM Table1 as T1, Table2 as T2, Table3 as T3
   WHERE T3.ID_Table1 = T1.ID and T3.ID_Table2 = T2.ID

Output:

 
Name Name
aaa pro2
bbb pro2
ccc pro1
(3 row(s) affected)

Best regards,
Krzemo.

[cilu]

OK. My mistake. That works, but actually I want something else. I'm sorry for posting the wrong thing.

Here is what I actually want (this is much more complicated):

TABLE1:
ID   Name
1    aaa
2    bbb
3    ccc

TABLE2
ID   Name
1    pro1
2    pro2
3    pro3

TABLE3
ID   ID_TABLE1   ID_TABLE2
1    1                   1
2    1                   2
3    1                   3
4    2                   1

Can I do something like this:

aaa        pro1,pro2,pro3
bbb        pro1

This one means a string "pro1,pro2,pro3", but there can be another delimiter than ','.

[Krzemo]

CREATE FUNCTION dbo.FN_GetConcatedNames(@i_ID INT) RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE
	@_Str VARCHAR(4000)
SELECT @_Str=''
 
SELECT @_Str=@_Str+IsNull(','+t2.[Name],'') 
	 FROM Table3 t3,Table2 t2
	 WHERE t3.ID_Table2=t2.ID AND T3.ID_Table1=@i_ID
 
RETURN SUBSTRING(@_Str,2,4000)
END
 
GO
 
SELECT T1.Name,dbo.FN_GetConcatedNames(T1.ID) Names2
FROM Table1 T1

Hope it helps.

[jp140768]

Are you talking about a cross tab (or maybe its called pivot) query?

[cilu]

CREATE FUNCTION dbo.FN_GetConcatedNames(@i_ID INT) RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE
	@_Str VARCHAR(4000)
SELECT @_Str=''
 
SELECT @_Str=@_Str+IsNull(','+t2.[Name],'') 
	 FROM Table3 t3,Table2 t2
	 WHERE t3.ID_Table2=t2.ID AND T3.ID_Table1=@i_ID
 
RETURN SUBSTRING(@_Str,2,4000)
END
 
GO
 
SELECT T1.Name,dbo.FN_GetConcatedNames(T1.ID) Names2
FROM Table1 T1

Hope it helps.

I'm not sure... I mean, I was hoping for something easier. I have this app in VC++ and I'm using CDaoDatabase and CDaoRecordset and I'm not sure how can use your code.

[cilu]

Are you talking about a cross tab (or maybe its called pivot) query?

Sorry, I don't remember what a cross table is...

I have three tables, one with movies, one with actors and one that links actors to movies, since more than one actor plays in a movie. So basically what I want is select a movie name from table 1 and all the actors that plays in it from table 2 but the result be a single record:

movie1         actor1, actor2, actor3
movie2         actor5, actor100

Hope you got the picture...

[cornelia]

poti face in felul asta:

select name, count(name)as number from MyTable group by name
toate bune, all the best
Cornelia

I have a table with two fields ID and Name, with possible multiple entries for the same name. Here is an example:

ID   Name
1    aaa
2    bbb
3    bbb
4    ccc
5    aaa

Now, I want to know if there is possible to get with a single SELECT all the names and how many times they appear in the table:

aaa    2
bbb    2
ccc    1

Thank you.

[Krzemo]

Are You using Access (.mdb) database?

[cilu]

Are You using Access (.mdb) database?

Yes I do.

[jp140768]

I set up a small Access database, with three tables, one has an example similar to an entry earlier, where we have one table, with the name listed multiple times, and we wanted to count it. This can be seen using Query 1.

I think though the main question was in relation to having a table of films, a table of actors and then a table which linked the two together. I set these up, and then set up query 2, which will give you something close to what you're looking for, or maybe not.

HTH

[cilu]

Right now I can't test it, because I have Access '97 on this computer and it doesn't recognize the format, but I'll test it tonight and let you know about it tomorrow.

PS: problem one in now solved.