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... :blush: 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::confused:
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. :blush:

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.
:wave:

[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.
:wave:
I'm not sure... :ehh: 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... :blush:

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.