Need help with a complex SQL query - I think I need a two-stage inner join or something like that?

Question

Okay, here's what I'm trying to do. I have a drupal table (term_data) in mysql that lists tags and their ID numbers and a second table, (term_node) that shows the relationship between tags and the data the tags refer to. For example, if node 1 had 3 tags, "A", "B" and "C". term_data might look like this:

name tid
A    1
B    2
C    3

and term_node might look like this:

nid  tid
1    1
1    2
2    2
3    3
3    2

In this example, node 1 has been tagged with "A" and "B", node 2 has been tagged with "A" and node 3 has been tagged with "B", and "C".

I need to write a query that, given a tag name, list for me all the OTHER tags that are ever used with that tag. In the above example, searching on "A" should return "A" and "B" because node 1 uses both, searching on "C" should return "B" and "C", and searching on "B" should return "A", "B" and "C".

Any ideas? I got this far:

select distinct n.nid from term_node n INNER join term_data t where n.tid = t.tid and t.name='A';

Which gives me a list of every node that has been tagged with "A" - but I can't figure out the next step.

Can anyone help me out?

Answer 1

Updated: Mark pointed out that the query wasn't correct.

SELECT DISTINCT t.name, t2.name Other 
FROM
   term_data t 
   INNER JOIN term_node n ON t.tid = n.tid
   INNER JOIN term_node n2 ON n2.nid = n.nid
   INNER JOIN term_data t2 ON n2.tid = t2.tid
WHERE
   t.name = 'A'

Marks answer should be accepted since he got it right first. Here is a demonstration of a similar query

http://odata.stackexchange.com/stackoverflow/q/13298/demo-for-need-help-with-a-complex-sql-query

Answer 2

Alright, the following should work... without building example tables and data it's a bit hard for me to test though... by all means comment and I can try and amend :)

SELECT DISTINCT rel.name
FROM term_data rel,
 term_node rellink,
 term_node sourcelink,
 term_data source,
WHERE rellink.tid=rel.tid
 AND rellink.nid=sourcelink.nid
 AND sourcelink.tid=source.tid
 AND source.name='A'

You'd be wanting to change the 'A' in the nested select if you're looking on other tags.

Answer 3

Try:

select distinct d2.name
from term_data d1
join term_node n1 on d1.tid = n1.tid
join term_node n2 on n1.nid = n2.nid
join term_data d2 on n2.tid = d2.tid
where d1.name = 'A'

Answer 4

Your description of term_node data and the example do not seem to match but using the example data provided I believe the following query will do what you need.

select distinct td.name, td2.name as tagged_name
from term_data td
inner join term_node tn
on tn.tid = td.tid
inner join term_node tn2
on tn2.nid = tn.nid
inner join term_data td2
on td2.tid = tn2.tid

The first join looks up the term_node records that match the name, term_node is then joined to itself to find all other tid's for that node, finally the second term_node is joined to term_data to retrieve the names of the tag.

You need to tack on the appropriate where clause to select just the tag you want.

Result set follows for above:-

name    tagged_name
A       A
A       B
B       A
B       B
B       C
C       B
C       C

Hope this helps

Ray

Answer 5

I created the schema in my workbench, and here's the query I came up with:

SELECT * FROM `term_data` WHERE `term_data`.`tid` IN (
    SELECT `term_node`.`tid` from `term_node` WHERE `nid` IN (
        SELECT `nid` FROM `term_node` JOIN `term_data` ON `term_data`.`tid` = `term_node`.`tid` WHERE `term_data`.`name` = 'A'
    )
);

Sorry for the structure ;) Here's SHOW CREATE TABLE for both tables:

CREATE TABLE `term_data` (
  `tid` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `term_node` (
  `term_node_id` int(11) NOT NULL,
  `nid` int(11) NOT NULL,
  `tid` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`term_node_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

This seemed to work as expected, if I understood your question correctly. So one more time, we have some nodes which are tagged. We'd like to select a tag (A), and then select other tags that were used to tag same nodes as tag A.

Cheers.

P.S. Output is the following:

tid      name

/* For tag A */
1        A
2        B

/* For tag B */
1        A
2        B
3        C

/* For tag C */
2        B
3        C