Help with SQL multi-table query

Question

I hoping someone can help me with a MySQL query.

I am trying to create a query in a MySQL database that finds all rows that matches a string 'foo' in column_1 of Table_A and then searches those rows with 'foo' and for all values of 'bar' in column_2 of Table_A returns all rows in Table_b that contain 'bar' in column_8.

My starting query is:

SELECT * FROM TABLE_A WHERE column_1 LIKE 'foo';

The above query only finds all data that matches a string 'foo' in column_1 of Table_A. What code do I need to add to take the above results and use Table_A column_2 to search for matches in column_8 of Table_B? I can't seem to figure out how to perform this query.

Thanks in advance for any help you can provide.

Your request doesn't make any sense. Is there a relationship between Table_A and Table_B? As explained you don't even need Table_A — JNK
– JNK, Commented Jul 15, 2011 at 18:32

JNK · Accepted Answer · 2011-07-15 18:35:08Z

3

Its not clear from your description if this is right but it sounds like you're looking for a JOIN

SELECT * 
FROM 
    TABLE_A a
  INNER JOIN TABLE_B b 
  ON a.column_2 = b.column_8
 WHERE 
    a.column_1 LIKE 'foo'
     and a.column_2 = 'bar'

edited Jul 15, 2011 at 18:35

JNK

65.5k15 gold badges127 silver badges138 bronze badges

answered Jul 15, 2011 at 18:32

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

drbunsen Over a year ago

Thank you! Seriously, thanks for taking the time to read through my complex description. You code worked perfectly!

Conrad Frix · Accepted Answer · 2011-07-15 18:34:41Z

1

you can use a sub query to select the values in table b to compare to the column in table A:

select * from tableA
Where column1 = 'foo'
and column2 in (select disitnct column8 from tableB where xxxxxxxxx)

edited Jul 15, 2011 at 18:34

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

answered Jul 15, 2011 at 18:32

Limey

2,7946 gold badges38 silver badges70 bronze badges

1 Comment

Conrad Frix Over a year ago

You can format your code by selecting and pressing the {} button.

David Ryder · Accepted Answer · 2011-07-15 18:33:22Z

0

http://dev.mysql.com/doc/refman/5.0/en/join.html

For example:

SELECT * FROM TABLE_A 
LEFT JOIN TABLE_B 
USING(COLUMN TO MATCH ON)
WHERE TABLE_A.column_1 LIKE 'foo' 
AND  TABLE_B.column_8 LIKE 'bar';

I'm not entirely clear what you are actually trying to do so that's probably not the exact code you need.

answered Jul 15, 2011 at 18:33

David Ryder

1,3115 gold badges14 silver badges28 bronze badges

1 Comment

David Ryder Over a year ago

Thanks! I completely forgot about that :)

DavidJBerman · Accepted Answer · 2011-07-15 18:35:37Z

0

SELECT * FROM TABLE_A INNER JOIN TABLE_B ON TABLE_A.COLUMN_1 = 'foo' AND TABLE_A.COLUMN_2 = 'bar' AND TABLE_B.COLUMN_8 = 'bar'

This would work on SQL server, probably is the same on MySQL. Try it! You can use more than one criterion on a join.

answered Jul 15, 2011 at 18:35

DavidJBerman

9551 gold badge11 silver badges18 bronze badges

Collectives™ on Stack Overflow

Help with SQL multi-table query

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related