Just nu i M3-nätverket
Jump to content

SQL select-problem


Henkuttt

Recommended Posts

I have a table looking like:

 

TABLE NAME: persons

pid|name

1|Joe

2|John

3|Andy

4|Robert

5|Cliff

 

TABLE NAME: category

 

catid|catname|catperson

1|Economic|1

2|Economic|4

3|Security|1

4|Cleaning|5

5|Economic|3

6|Security|2

7|Cleaning|1

 

First of all, I want to group them so I can sort out how many diffrent categorys that exist.

 

"SELECT * FROM persons GROUP BY catname"

 

So far, It's all good.

 

But now i want to GROUP them but ALSO order by a person. Something like:

 

"SELECT * FROM persons INNER JOIN category ON category.catperson = persons.pid GROUP BY catname ORDER BY category.catperson = '1' DESC"

 

This does not work fully. But its close. It works if I skip GROUP BY, but then i see all the entries from category. I just want to see ONE of each 'catname'.

 

Link to comment
Share on other sites

Hej!

 

Ändra först och främst din databasdesign...

 

personstabellen är ok...

 

Ny category tabell

 

catid

catname

 

Fyll den med dina tre kategorier (eller fler)

 

catid catname

1 Economy

2 Security

3 Cleaning

 

Skapa en tredje tabell cat_persons

 

id

pid

catid

 

fyll den med (ex.vis)

id pid catid

1 1 1

2 4 1

3 1 2

4 5 3

5 3 1

6 2 2

7 1 3

 

Hämta data med (ex.vis)

 

SELECT dbo.persons.pid, dbo.persons.name, dbo.category.catname

FROM dbo.persons INNER JOIN

dbo.cat_persons ON dbo.persons.pid = dbo.cat_persons.pid INNER JOIN

dbo.category ON dbo.cat_persons.catid = dbo.category.catid

GROUP BY dbo.category.catname, dbo.persons.pid, dbo.persons.name

ORDER BY dbo.persons.pid

 

/PJ

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.



×
×
  • Create New...