SQL statement question

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

Code:

---

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:

Code:

---

aaa    2
bbb    2
ccc    1

---

Thank you.

Code:

---

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

---

Why not?

Best regards,
Krzemo.

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:

Code:

---

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:

Code:

---

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

---

I tried this but it doesn't work:

Code:

---

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

---

Quote:

---

I tried this but it doesn't work:

---

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

Code:

---

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:

Code:

---


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

---

Best regards,
Krzemo.

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):

Code:

---

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:

Code:

---

aaa        pro1,pro2,pro3
bbb        pro1

---

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

Code:

---

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:

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

Quote:

---

Originally Posted by Krzemo

Code:

---

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.

Quote:

---

Originally Posted by jp140768

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:

Code:

---

movie1        actor1, actor2, actor3
movie2        actor5, actor100

---

Hope you got the picture...

poti face in felul asta:

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

Quote:

---

Originally Posted by cilu

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

Code:

---

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:

Code:

---

aaa    2
bbb    2
ccc    1

---

Thank you.

---

Are You using Access (.mdb) database?

Quote:

---

Originally Posted by Krzemo

Are You using Access (.mdb) database?

---

Yes I do.

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

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.