CODEWITHSUNDEEP
phpmysql
Sami
Sami

Reputation: 477

How to loop JOIN

I have this table data in the picture. It basically consists of name of places and to which other place it belongs in the same table. The field belongs_to refers to the id of the other place.

enter image description here

Here is the query I am using: 

         SELECT A.type,
                A.name_en AS name_A,
                B.name_en AS name_B,
                C.name_en AS name_C

                FROM address AS A
                LEFT JOIN address AS B ON A.belongs_to = B.id
                LEFT JOIN address AS C ON B.belongs_to = C.id 
                WHERE A.name_en LIKE '%".$_GET['name']."%'
                "

It works fine when I type "sy" it gets me :

[type] = city
[name_A] =sydney
[name_B] =NSW
[name_C] =australia

However when I type "ken" it gets me:

[type] =suburb
[name_A] = kensington
[name_B] =sydney
[name_C] = NSW

It misses Australia. I want to make it flexible so It get me all the data up to the country name where the type_order is 1 or belongs_to is 0. I have been trying to search for looping technique and could not find a good reference. Tell me If you have another suggestion to get this done or if you think my solution is the right one please help me with the looping thing or refer me to a good detailed reference regarding MySQL looping.

Upvotes: 3

Views: 136

Answers (3)

artoodetoo
artoodetoo

Reputation: 938

The technique you choose to keep linked data is so called "Adjacency List". There are no way to "loop" random depth A.L., in general. To see items from 'kensington' up to 'australia', you need one more join in query. You have to add one join to every additional row.

Look for alternative techniques such as "Nested Lists" and "Materialized Paths". OR use loop in PHP to iterate through Adjacency List items.

Upvotes: 0

Doan Cuong
Doan Cuong

Reputation: 2624

SELECT A.name_en AS name_A, NULL AS TYPE    

FROM tablename1 AS A
LEFT JOIN tablename1 AS B ON A.belongs_to = B.id                
WHERE A.name_en LIKE '%ken%' OR A.belongs_to = B.id OR A.belongs_to = 0

UNION ALL

SELECT NULL AS name_A, C.type
FROM tablename1 AS C
WHERE c.name_en LIKE '%ken%'

Hope this will help you

Upvotes: 0

There is a dream I am living T
There is a dream I am living T

Reputation: 171

There is nothing wrong in user joins. Your condition basically depends on your WHERE condition. Please provide the structure of the table, or you can try this:

SELECT A.type,
                A.name_en AS name_A,
                B.name_en AS name_B,
                C.name_en AS name_C

                FROM address AS A
                LEFT JOIN address AS B ON A.belongs_to = B.id
                LEFT JOIN address AS C ON B.belongs_to = C.id 
                WHERE A.name_en LIKE '%".$_GET['name']."%' or A.id=1

Upvotes: 3

Related Questions