MySQL JOIN without using ON when no rows match?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

MySQL JOIN without using ON when no rows match?

Post by JAB Creations »

I want to use the language row from my cms_sections table however no column/rows will match in my blog_threads table to do a successful JOIN. Since I only need that one row from cms_sections but I need two rows from blog_threads I can't do a UNION either.

So is there a way I can get the three columns (one column from cms_sections and two columns from blog_threads) in a single MySQL query?
waylon999
Forum Commoner
Posts: 26
Joined: Mon Mar 23, 2009 5:29 pm

Re: MySQL JOIN without using ON when no rows match?

Post by waylon999 »

I'm not sure if this is what you are after, but say you have 2 tables:
users {id, name} -> (1,joe), (2,sue)
providers {id, name} -> (1,verizon), (2,cingular)

then you can query the db on these 2 tables that don't have a common column:

Code: Select all

 
select p.name, u.name from providers p, users u where p.id = 1 and (u.id=1 OR u.id=2)
 
which would return 2 rows:
verizon, joe
verizon, sue


Is that close to what you are trying to accomplish?
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: MySQL JOIN without using ON when no rows match?

Post by JAB Creations »

AWESOME!

That worked great!

Code: Select all

SELECT cs.section_name, ua.user_username FROM cms_sections AS cs, user_accounts AS ua WHERE cs.id = 1 AND (ua.user_id=1 OR ua.user_id=2)
I see the you can choose multiple tables by using a comma...it's one of those odd ball things that aren't covered by tutorials. Dam...once I get really good with databases I'm so going to write a tutorial or something and include this sort of stuff!

I had to add AS (alias) to get your example to work though thankfully I was already aware of that. But yeah thanks a ton again for helping me out with that! :)
Post Reply