MySQL JOIN tables with WHERE clause

Question

I need to gather posts from two mysql tables that have different columns and provide a WHERE clause to each set of tables. I appreciate the help, thanks in advance.

This is what I have tried...

SELECT 
  blabbing.id, 
  blabbing.mem_id, 
  blabbing.the_blab, 
  blabbing.blab_date, 
  blabbing.blab_type, 
  blabbing.device, 
  blabbing.fromid, 
  team_blabbing.team_id
FROM 
  blabbing
LEFT OUTER JOIN 
  team_blabbing 
 ON team_blabbing.id = blabbing.id 
WHERE 
  team_id IN ($team_array) || 
  mem_id='$id' || 
  fromid='$logOptions_id'
ORDER BY 
  blab_date DESC 
LIMIT 20

I know that this is messy, but i'll admit, I am no mysql veteran. I'm a beginner at best... Any suggestions?

Well as you can see the tables have one column that is not in common "team_id". I need to be able to set a where clause for each table and use the team_id in a variable. Does that help? — Juan Gonzales
– Juan Gonzales, Commented Dec 9, 2011 at 17:41

Matt Fenwick · Accepted Answer · 2011-12-09 17:48:56Z

1

You could put the where-clauses in subqueries:

select
  *
from
  (select * from ... where ...) as alias1  -- this is a subquery
left outer join
  (select * from ... where ...) as alias2  -- this is also a subquery
on
  ....
order by
  ....

Note that you can't use subqueries like this in a view definition.

You could also combine the where-clauses, as in your example. Use table aliases to distinguish between columns of different tables (it's a good idea to use aliases even when you don't have to, just because it makes things easier to read). Example:

select
  *
from
  <table> as alias1
left outer join
  <othertable> as alias2
on
  ....
where
  alias1.id = ... and alias2.id = ...  -- aliases distinguish between ids!!
order by
  ....

edited Dec 9, 2011 at 17:48

answered Dec 9, 2011 at 17:30

Matt Fenwick

49.3k24 gold badges130 silver badges198 bronze badges

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

Comments

DRapp · Accepted Answer · 2011-12-09 18:47:35Z

Two suggestions for you since a relative newbie in SQL. Use "aliases" for your tables to help reduce SuperLongTableNameReferencesForColumns, and always qualify the column names in a query. It can help your life go easier, and anyone AFTER you to better know which columns come from what table, especially if same column name in different tables. Prevents ambiguity in the query. Your left join, I think, from the sample, may be ambigous, but confirm the join of B.ID to TB.ID? Typically a "Team_ID" would appear once in a teams table, and each blabbing entry could have the "Team_ID" that such posting was from, in addition to its OWN "ID" for the blabbing table's unique key indicator.

SELECT 
      B.id, 
      B.mem_id, 
      B.the_blab, 
      B.blab_date, 
      B.blab_type, 
      B.device, 
      B.fromid, 
      TB.team_id
   FROM 
      blabbing B
         LEFT JOIN team_blabbing TB
            ON B.ID = TB.ID
   WHERE 
         TB.Team_ID IN ( you can't do a direct $team_array here )
      OR B.mem_id = SomeParameter
      OR b.FromID = AnotherParameter
   ORDER BY 
      B.blab_date DESC 
   LIMIT 20

Where you were trying the $team_array, you would have to build out the full list as expected, such as

TB.Team_ID IN ( 1, 4, 18, 23, 58 )

Also, not logical "||" or, but SQL "OR"

EDIT -- per your comment

This could be done in a variety of ways, such as dynamic SQL building and executing, calling multiple times, once for each ID and merging the results, or additionally, by doing a join to yet another temp table that gets cleaned out say... daily.

If you have another table such as "TeamJoins", and it has say... 3 columns: a date, a sessionid and team_id, you could daily purge anything from a day old of queries, and/or keep clearing each time a new query by the same session ID (as it appears coming from PHP). Have two indexes, one on the date (to simplify any daily purging), and second on (sessionID, team_id) for the join.

Then, loop through to do inserts into the "TempJoins" table with the simple elements identified.

THEN, instead of a hard-coded list IN, you could change that part to

   ...
 FROM 
      blabbing B
          LEFT JOIN team_blabbing TB
             ON B.ID = TB.ID
             LEFT JOIN TeamJoins TJ
                on TB.Team_ID = TJ.Team_ID
   WHERE 
         TB.Team_ID IN NOT NULL
      OR B.mem_id ... rest of query

How can I get the $team_array output if the array is constantly changing without using a variable?

Matt Fenwick · Accepted Answer · 2012-01-04 21:26:19Z

0

What I ended up doing is;

I added an extra column to my blabbing table called team_id and set it to null as well as another field in my team_blabbing table called mem_id
Then I changed the insert script to also insert a value to the mem_id in team_blabbing.

After doing this I did a simple UNION ALL in the query:

SELECT 
  * 
 FROM 
  blabbing 
 WHERE 
  mem_id='$id' OR 
  fromid='$logOptions_id' 
UNION ALL 
SELECT 
 * 
 FROM 
  team_blabbing 
 WHERE 
  team_id 
 IN 
  ($team_array) 
ORDER BY 
  blab_date DESC 
LIMIT 20

I am open to any thought on what I did. Try not to be too harsh though:) Thanks again for all the info.

edited Jan 4, 2012 at 21:26

Matt Fenwick

49.3k24 gold badges130 silver badges198 bronze badges

answered Dec 10, 2011 at 3:54

Juan Gonzales

1,9872 gold badges22 silver badges36 bronze badges

Collectives™ on Stack Overflow

MySQL JOIN tables with WHERE clause

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related