Need To Select Data From 2 Tables, 4 Fields And It's Causing Confusion!

[Dave3765]

Ok...

 

The simplified DB in mysql 5.0.45 looks something like this...

 

table_1

+-------------------+

| id | word |

|--------------------|

| 123 | cat |

| 456 | dog |

+-------------------+

 

table_2

+-------------------+

| id1 | id2 |

|--------------------|

| 123 | 456 |

+-------------------+

 

 

 

What I want to do is query WHERE word='cat' and get back out the word 'dog'.

 

So need to get the id from table_1 where word.table_1='cat', find all occurrences of this in id1.table_2, then use this data found from table_2 to find id2.table_2 and match this with everything in word.table_1.  :googly:

 

 

I have tried various things and the furthest I think I've got is this:

 

 

SELECT t1.word, t2.id1, t2.id2

FROM table_1 AS t1, table_2 AS t2

WHERE t1.id=t2.id1

AND t2.id2=t1.id

AND word='cat'

 

 

Where am I going wrong? 

 

[teng84]

based on your query you cant get nay results..

can tell me what do you expect from that query  or your sample output maybe !

[Dave3765]

I have shown more of the table in this view:

 

table_1

+--------------------------------+

| id   | word | category |

|---------------------------------|

| 123 | cat | 1   |

| 456 | dog | 1   |

| 789 | rat | 2   |

+--------------------------------+

 

table_2

+-------------------+

| id1 | id2 |

|--------------------|

| 123 | 456 |

| 123 | 789 |

+-------------------+

 

 

What I'm trying to do is the same as before but only return results where the category of the queried word (in this case, cat) is the same (i.e 1 - one).

 

What I Want:

 

I want to query the word 'cat' and get back 'dog' because...

 

the id of cat has 2 match's in table_2, and the id2 fields in table_2 relate to the 2 other words in table_1.

 

Then only 'dog' should be returned because the category field for dog and cat matchs.

[teng84]

this?

 

SELECT t1.word, t2.id1, t2.id2
FROM table_1 AS t1, table_2 AS t2
WHERE t1.id=t2.id1
OR t2.id2=t1.id
AND word='cat'

[Dave3765]

Nope - the OR is causing way too many results to be returned.

 

Here's how I would like to go from 'cat' to 'dog':

 

1. query 'cat'

2. get the id for 'cat'

3. find match's to id in id1

4. get the related id2's for id1

5. go back to table_1 and get all the id's that match the id2's

6. filter out any results where the category doesn't equal the original query - in this case only show match's that have a category number of 1.

 

which should return, in this case only 'dog'.

 

A pain in the butt - I know 

 

Is this possible in mysql - would it require several joins or would I just be better iterating through it in php?

 

 

 

[eddierosenthal]

how about this:

select  a.id, a.word, a.category

from table_1 a, table_1 b

where a.category = b.category and

      a.id != b.id and

a.id in (select id2 from table_2 )

 

 

[aschk]

Also try:

SELECT word 
FROM table_1 t
JOIN
(SELECT t2.id2, t1.category
  FROM table_1 t1
  JOIN table_2 t2 ON t1.id = t2.id1
  WHERE t1.word = 'cat'
) x ON t.category = x.category AND t.id = x.id2

 

note: my query avoids the use of "IN (select id2 from table_2)", which can cause problems with large datasets.

[eddierosenthal]

much better!

[Dave3765]

aschk - perfect.

 

This does exactly what I needed and it's fast too.

 

I owe you a beer