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…

Accepted Answer

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'
```

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

Solution

Problem