November 25, 2015 at 8:29 am
Hello everyone,
first of all excuse for my bad English!
I try to resume with a basilar example what I should reach.
-- table category
[id_categoria] [int] NOT NULL,
[categoria] [nvarchar](50) NULL
-- table film
[id_film] [int] NOT NULL,
[film] [nvarchar](50) NULL,
[fk_categoria] [int] NULL
The following query is useful but it doesn't respect the hierarchical tree (I will stop to 1st level with my example but i'll need to go further. In the meantime I have to understand hot does it work).
SELECT categoria.id_categoria, categoria.categoria, film.id_film, film.film
FROM categoria
INNER JOIN
film ON categoria.id_categoria = film.fk_categoria
which output:
id_categoria | categoria | id_film | film
1 | horror | 1 | titolo | 1
1 | horror | 2 | titolo | 2
2 | fantascienza | 3 | titolo 3
I've read and tried to use CTE with function REPLICATE which could allow an output with Parent/Child hierarchies but I can't.
It should
What should I do? Is correct my starting query or I have to change it?
Hope I was clear 🙂
Thanks in advance
Mara
November 25, 2015 at 9:10 am
eut.kop (11/25/2015)
Hello everyone,first of all excuse for my bad English!
I try to resume with a basilar example what I should reach.
-- table category
[id_categoria] [int] NOT NULL,
[categoria] [nvarchar](50) NULL
-- table film
[id_film] [int] NOT NULL,
[film] [nvarchar](50) NULL,
[fk_categoria] [int] NULL
The following query is useful but it doesn't respect the hierarchical tree (I will stop to 1st level with my example but i'll need to go further. In the meantime I have to understand hot does it work).
SELECT categoria.id_categoria, categoria.categoria, film.id_film, film.film
FROM categoria
INNER JOIN
film ON categoria.id_categoria = film.fk_categoria
which output:
id_categoria | categoria | id_film | film
1 | horror | 1 | titolo | 1
1 | horror | 2 | titolo | 2
2 | fantascienza | 3 | titolo 3
I've read and tried to use CTE with function REPLICATE which could allow an output with Parent/Child hierarchies but I can't.
It should
What should I do? Is correct my starting query or I have to change it?
Hope I was clear 🙂
Thanks in advance
Mara
Your query appears to be retrieving each film and their category. I don't see anything in your query or the data structure that indicates you need recursion here. Can you post some sample data that represents the problem you are having?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 25, 2015 at 9:18 am
I see the same thing as Sean, there's no hierarchy in what you posted. There's a parent/child relationship, but it won't go multiple levels.
And please, next time post sample data like this:
CREATE table categoria(
[id_categoria] [int] NOT NULL,
[categoria] [nvarchar](50) NULL
);
INSERT INTO categoria
VALUES
(1, 'horror'),
(2, 'fantascienza');
CREATE table film(
[id_film] [int] NOT NULL,
[film] [nvarchar](50) NULL,
[fk_categoria] [int] NULL
);
INSERT INTO film
VALUES
(1,'titolo', 1),
(2,'titolo', 1),
(3,'titolo', 2);
November 26, 2015 at 1:21 am
Luis Cazares (11/25/2015)
I see the same thing as Sean, there's no hierarchy in what you posted. There's a parent/child relationship, but it won't go multiple levels.
I wish to have something similar (also the layout should reflect the hierarchical tree)
horror
| titolo 1
| titolo 2
fantascienza
| titolo 3
| | titolo n (this is a second level)
but now you make me understand my relationship between tables don't permit this.
Luis Cazares (11/25/2015)
And please, next time post sample data like this:
Sorry, next time I'll post code in a right way
Thanks
Mara
November 26, 2015 at 5:53 am
eut.kop (11/26/2015)
I wish to have something similar (also the layout should reflect the hierarchical tree)horror
| titolo 1
| titolo 2
fantascienza
| titolo 3
| | titolo n (this is a second level)
but now you make me understand my relationship between tables don't permit this.
Thanks
Mara
Yes, you can currently have only 2 levels.
CREATE table categoria(
[id_categoria] [int] NOT NULL,
[categoria] [nvarchar](50) NULL
);
INSERT INTO categoria
VALUES
(1, 'horror'),
(2, 'fantascienza');
CREATE table film(
[id_film] [int] NOT NULL,
[film] [nvarchar](50) NULL,
[fk_categoria] [int] NULL
);
INSERT INTO film
VALUES
(1,'titolo', 1),
(2,'titolo', 1),
(3,'titolo', 2);
select title from
(
select [fk_categoria], [id_film], title= replicate(' ',3)+[film]
from film
union all
select
[id_categoria], null, title = [categoria]
from categoria
) as t
order by [fk_categoria], [id_film]
November 26, 2015 at 8:40 am
serg-52 (11/26/2015)
Yes, you can currently have only 2 levels.
CREATE table categoria([id_categoria] [int] NOT NULL,
[categoria] [nvarchar](50) NULL
);
INSERT INTO categoria
VALUES
(1, 'horror'),
(2, 'fantascienza');
CREATE table film(
[id_film] [int] NOT NULL,
[film] [nvarchar](50) NULL,
[fk_categoria] [int] NULL
);
INSERT INTO film
VALUES
(1,'titolo', 1),
(2,'titolo', 1),
(3,'titolo', 2);
select title from
(
select [fk_categoria], [id_film], title= replicate(' ',3)+[film]
from film
union all
select
[id_categoria], null, title = [categoria]
from categoria
) as t
order by [fk_categoria], [id_film]
That is exactly what I mean 🙂
Thanks a lot.
I try to add more levels; in case of troubles I will ask again.
Mara
November 26, 2015 at 5:38 pm
eut.kop (11/26/2015)
serg-52 (11/26/2015)
Yes, you can currently have only 2 levels.
CREATE table categoria([id_categoria] [int] NOT NULL,
[categoria] [nvarchar](50) NULL
);
INSERT INTO categoria
VALUES
(1, 'horror'),
(2, 'fantascienza');
CREATE table film(
[id_film] [int] NOT NULL,
[film] [nvarchar](50) NULL,
[fk_categoria] [int] NULL
);
INSERT INTO film
VALUES
(1,'titolo', 1),
(2,'titolo', 1),
(3,'titolo', 2);
select title from
(
select [fk_categoria], [id_film], title= replicate(' ',3)+[film]
from film
union all
select
[id_categoria], null, title = [categoria]
from categoria
) as t
order by [fk_categoria], [id_film]
That is exactly what I mean 🙂
Thanks a lot.
I try to add more levels; in case of troubles I will ask again.
Mara
Be aware that the example doesn't represent a recursive query. That's just a query using union all
November 26, 2015 at 11:48 pm
Luis Cazares (11/26/2015)
Be aware that the example doesn't represent a recursive query. That's just a query using union all
Yes, exactly. If OP needs more levels we need more info what are those levels about.
November 27, 2015 at 1:33 am
serg-52 (11/26/2015)
Luis Cazares (11/26/2015)
Be aware that the example doesn't represent a recursive query. That's just a query using union allYes, exactly. If OP needs more levels we need more info what are those levels about.
So sorry for the mystake... I just started and I gave you wrong subject.
Two questions about code 'union all':
1. If I would add other field to table film
film (
...
[commento] [nvarchar](50) NULL,
[voto] [int] NULL,
....
how should I modify statement to show them in output?
2.I should try to add a 2^ level of hierarchical (i.e. nodes under 1st level 'titolo n'): a table sub_film with a foreign key fk_film
CREATE TABLE [dbo].[sub_film](
[id_sub_film] [int] NOT NULL,
[sub_titolo] [varchar](50) NULL,
[fk_film] [int] NULL
INSERT INTO sub_film
VALUES
(1,'sub_titolo 2.0', 2),
(2,'sub_titolo 2.1', 2),
(2,'sub_titolo 3.0', 3),
My target is following output:
horror
| titolo 1
| titolo 2
| sub_titolo 2.0 (2nd level)
| sub_titolo 2.1 (2nd level)
fantascienza
| titolo 3
| sub_titolo 3.0 (2nd level)
I hope I explained in a proper way.
Thanks
Mara
November 27, 2015 at 4:14 am
Yes, you can follow the same track.
CREATE table categoria(
[id_categoria] [int] NOT NULL,
[categoria] [nvarchar](50) NULL
);
INSERT INTO categoria
VALUES
(1, 'horror'),
(2, 'fantascienza');
CREATE table film(
[id_film] [int] NOT NULL,
[film] [nvarchar](50) NULL,
[fk_categoria] [int] NULL
);
INSERT INTO film
VALUES
(1,'titolo 1', 1),
(2,'titolo 2', 1),
(3,'titolo 3', 2);
CREATE TABLE [dbo].[sub_film](
[id_sub_film] [int] NOT NULL,
[sub_titolo] [varchar](50) NULL,
[fk_film] [int] NULL
);
INSERT INTO sub_film
VALUES
(1,'sub_titolo 2.0', 2),
(2,'sub_titolo 2.1', 2),
(2,'sub_titolo 3.0', 3)
;
select title from
(
select f.[fk_categoria], f.[id_film], [id_sub_film], title= replicate(' ',6)+[sub_titolo]
from sub_film as sf
join film as f on sf.[fk_film] = f.[id_film]
union all
select [fk_categoria], [id_film], null, title= replicate(' ',3)+[film]
from film
union all
select
[id_categoria], null, null, title = [categoria]
from categoria
) as t
order by [fk_categoria], [id_film], [id_sub_film]
A word of caution. You haven't declared any foreign key. And referencing columns are nullable. You should either correct it or decide on where should 'orphan' films and subfilms go and rewrite the query accordingly.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply