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?
MySQL JOIN without using ON when no rows match?
Moderator: General Moderators
- 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?
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:
which would return 2 rows:
verizon, joe
verizon, sue
Is that close to what you are trying to accomplish?
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)
verizon, joe
verizon, sue
Is that close to what you are trying to accomplish?
- 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?
AWESOME!
That worked great!
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!
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 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!