Skip to main content
S
Samall-OvBpM8
Participating Frequently
September 4, 2009
Question

Query question.

  • September 4, 2009
  • 1 reply
  • 590 views

Hi all,

I have a starter question about an SQL query. I have 3 simple tables:

table1table2table3
id1 (primary key)

id2 (PK)

id1 (FK from table1)

id3 (PK)

id2 (FK from table2)

Let's say the following content is in those tables:

table1table2table3
t1_1t2_1 (id1 = t1_1)t3_1 (id2 = t2_2)
t1_2t2_2 (id1 = t1_1)t3_2 (id2 = t2_2)
t1_3t2_3 (id1 = t1_3)t3_3 (id2 = t2_4)
t1_4t2_4 (id1 = t1_5)t3_4 (id2 = t2_4)
t1_5t2_5 (id1 = t1_5)t3_5 (id2 = t2_5)

Now I want to have to folowing output:

t1_1

- t2_1

- t2_2

-- t3_1

-- t3_2

t1_2

t1_3

- t2_3

t1_4

t1_5

- t2_4

-- t3_4

- t2_5

-- t3_5

Can this be done with on query?

Thanks for all your help.

Regards,

Samall

This topic has been closed for replies.

1 reply

D
Dan_Bracuk
Inspiring
September 4, 2009

First, I have heard good things about the book, "Teach Yourself SQL in 10 Minutes" by Ben Forta.

Next the query you want is

select your fields

from table1 t1 join table2 t2 on t1.id1 = t2.id1

join table3 t3 on t2.id2 = t3.id2

where whatever

order by t1.something, t2.something.

Then you can use the group attribute of cfoutput to arrange the results.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices