SQL query join question

Question

How do you do SQL query for the following condition? Suppose you have two tables: table1 and table2, where each entry in table1 can have multiple corresponding entries in table2. The pseudo code for the query that I want is:

for each $row in table1
   $rows = find all rows in table2 that corresponds to $row with $row.id == table2.foreign_id
   # $rows is an array of corresponding row in table2
   if all rows in $rows meet some condition 
   then 
     return $row
   else
     continue
   end
end

EDIT: note in the above pseudo code, I only want the row in table1 that has all its relations in TABLE2 that meets some conditions, not just some condition in table1.

PS: I want to do it in SQL due to efficiency problems that I may have otherwise.

Thanks a lot.

Answer 1

There's only one answer to this really:

W3Schools.com

Answer 2

select * from table1 as t1
inner join table2 as t2
    on t1.id == t2.foreign_id
where -- some condition goes here

This query will only return the rows from table1 that have a match in table2 and that match the where clause.

I would suggest checking out SQLCourse - Interactive Online SQL Training for Beginners since this really is a basic SQL query.

Answer 3

You can reformulate this with a where not exists ( .. ) type clause.

For example, pretending you want a list of customers whose orders are all completed:

 select * from customers c
 where not exists (
     select * from orders 
     where customerid=c.id 
       and status <> 'C'
 )

So you are asking for all customers who have no uncompleted orders - which is the same thing as all customers whose orders are all completed.

Instead of:

if all rows in $rows meet some condition

You are saying:

if NO rows in $rows DO NOT meet some condition

Edit: As pointed out in the comments, this will also return customers who have no orders. You could add and exists (select * from orders where customerid=c.id) to the end of the above to exclude these rows.

Answer 4

general format is:

SELECT * 
FROM Table1 t1
   INNER JOIN Table2 t2 ON (t1.ID = t2.ID)
WHERE ...

Answer 5

ck is right. Take a look at http://w3schools.com/sql/ and read up on Primary Keys, Foreign Keys and Joining tables. That should help you.

This example might help you as well.

Good luck.

Answer 6

Hi,

I think this is what you are getting at... The nested select is a derived table named sub_query and corresponds to this part of you pseudo code ($rows = find all rows in table2 that corresponds to $row with $row.id == table2.foreign_id). The outer select lets you further filter the first part of your pseudo code by some condition (your if statement)

   select
        sub_query.*
    from
        (select
            *
        from
            table1,
            table2
        where
            table1.id = table2.foreign_key_id) sub_query
    where
        sub_query.some_field = "some condition"

Enjoy!

Answer 7

As ck mentioned, this is really basic sql.

for each $row in table1

SELECT table1.* FROM table1

find all rows in table2 that corresponds to $row with $row.id == table2.foreign_id

LEFT JOIN table2 ON table1.id = table2.foreign_id

if all rows in $rows meet some condition

WHERE condition_here

The entire SQL becomes

SELECT
  table1.*
FROM table1
  LEFT JOIN table2 ON table1.id = table2.foreign_id
WHERE condition_here

Answer 8

Here's a possible solution. I use Oracle, not sure if the syntax is exactly right for MySQL, but I imagine you can work up an equivalent.

The idea of this is to find all ids in table2 for which all rows meet the desired condition, then look up those ids in table1.

SELECT *
  FROM table1
  WHERE id IN (
    SELECT id
      FROM table2
      GROUP BY id
      HAVING COUNT(*) = SUM( CASE WHEN <somecondition> THEN 1 ELSE 0 END )
  )