Using Where Clauses W/ INNER JOINS

5 replies
I've been working on this for a while...my brains are runny eggs. So this question might not make any sense.

I'm trying to write a MySQL query that would call about 5 databases using an INNER JOIN. All of the databases use ID.

So the query I have so far is...

SELECT ......
FROM ((DB_1
INNER JOIN DB_2
USING (ID)
INNER JOIN DB_3
USING (ID))


But if I use an additional WHERE clause, it's going to apply to the entire set of DBs right? Suppose only one DB has a unique column labelled COLOR.

So, is it possible to use the clause "WHERE color = green"?
#clauses #joins #w or
  • Profile picture of the author Steve Diamond
    In a word, yes. As long as there is one and only one column called "color" in the result set, then that will work fine. If there are several columns having the same name in the set, you can specify which one you want to use in the WHERE clause by prepending the table name: tablex.color.
    Signature
    Mindfulness training & coaching online
    Reduce stress | Stay focused | Keep positive and balanced
    {{ DiscussionBoard.errors[4503545].message }}
    • Profile picture of the author Big Squid
      Thanks Mr. Diamond!!! This totally worked out for me...Thanks!
      {{ DiscussionBoard.errors[4509889].message }}
  • Profile picture of the author SebastianJ
    Or you can use aliases for your tables:

    SELECT tbl1.* FROM TABLE_1 tbl1 INNER JOIN TABLE_2 tbl2 ON tbl1.id = tbl2.foreign_id INNER JOIN TABLE_3 tbl3 ON tbl2.id = tbl3.foreign_id WHERE tbl1.color = 'green'

    In the query above, tbl1, tbl2 and tbl3 are aliases. tbl1 is an alias for TABLE_1 etc.

    Aliases are especially useful if you're creating complex queries where you're joining several tables with long table names - it drastically cuts down the length of the SQL query.
    {{ DiscussionBoard.errors[4505197].message }}
    • Profile picture of the author Big Squid
      Originally Posted by SebastianJ View Post

      Or you can use aliases for your tables:

      SELECT tbl1.* FROM TABLE_1 tbl1 INNER JOIN TABLE_2 tbl2 ON tbl1.id = tbl2.foreign_id INNER JOIN TABLE_3 tbl3 ON tbl2.id = tbl3.foreign_id WHERE tbl1.color = 'green'

      In the query above, tbl1, tbl2 and tbl3 are aliases. tbl1 is an alias for TABLE_1 etc.

      Aliases are especially useful if you're creating complex queries where you're joining several tables with long table names - it drastically cuts down the length of the SQL query.
      Great stuff there. This is useful for some queries I have coming up....! Thanks!!!
      {{ DiscussionBoard.errors[4509894].message }}
  • Profile picture of the author leppozdrav
    But if I use an additional WHERE clause, it's going to apply to the entire set of DBs right? Suppose only one DB has a unique column labelled COLOR.
    If there is only one column called as color in all of the five tables, you can go for where color = green, or else you need to gofor table alias, and use the alias in the where clause.

    If you need much clarification I can post the table structure. I will give the write a query wich you need.

    Tansk
    {{ DiscussionBoard.errors[4505217].message }}

Trending Topics