By summit on

Hi,
I have the following query which works great to get the info I want, except I only want the toplevel terms and the terms one level down.
Is this possible by extending this query please?

This is my query:

SELECT n.nid, tn.tid, td.vid, td.name FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid INNER JOIN {term_data} td ON tn.tid = td.tid WHERE n.type = 'weblinks'

Thanks a lot in advance for your reply!
greetings, Martijn

⋅ Categories: Drupal 6.x

Comments

summit’s picture

summit commented

I found something and I think it has to be done manual on second level:

SELECT Vakanties_term_data.name, Vakanties_term_data.tid, Vakanties_term_hierarchy.parent
            FROM Vakanties_term_data
            INNER JOIN Vakanties_term_hierarchy ON Vakanties_term_hierarchy.tid = Vakanties_term_data.tid
            LEFT JOIN Vakanties_term_node ON Vakanties_term_node.tid = Vakanties_term_hierarchy.tid
            WHERE Vakanties_term_data.vid = "4"
            AND Vakanties_term_hierarchy.parent = "0" 
		    OR Vakanties_term_hierarchy.parent = "121"
			OR Vakanties_term_hierarchy.parent = "162"
			OR Vakanties_term_hierarchy.parent = "30"
			OR Vakanties_term_hierarchy.parent = "32"
			OR Vakanties_term_hierarchy.parent = "102"
			OR Vakanties_term_hierarchy.parent = "7"
            GROUP BY (Vakanties_term_data.tid)
            ORDER BY Vakanties_term_data.name
summit’s picture

summit commented

Hi,

I need these two to be combined, how please?

SELECT n.nid, tn.tid, td.vid, td.name 
			FROM {node} n 
			INNER JOIN {term_node} tn ON n.nid = tn.nid 
			INNER JOIN {term_data} td ON tn.tid = td.tid 
			INNER JOIN Vakanties_term_hierarchy th ON th.tid = td.tid
			WHERE n.type = 'weblinks'
			
			
			SELECT td.name, td.tid, th.parent
            FROM Vakanties_term_data td
            INNER JOIN Vakanties_term_hierarchy th ON th.tid = td.tid
            LEFT JOIN Vakanties_term_node tn ON tn.tid = th.tid
            WHERE td.vid = "4"
            AND th.parent = "0" 
		    OR th.parent = "121"
			OR th.parent = "162"
			OR th.parent = "30"
			OR th.parent = "32"
			OR th.parent = "102"
			OR th.parent = "7"
            GROUP BY (td.tid)
            ORDER BY td.name