SELECT two rows in two tables that must match two values?

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:

SELECT two rows in two tables that must match two values?

Post by JAB Creations »

I have an interesting goal that has short circuited my brain. :mrgreen:

A small disclaimer: I'm referring to a blog "post" as a "thread" as I've been trying to mimic this in a forum-like approach.

I have a list of blog categories and I'd like to list the total number of published blog threads per category though not include blog threads that are not currently published.

The publish status = thread_status (if it is set to 1, if it's not published it is set to 0).

I've had enough practice with LEFT JOINS to take an educated guess I may need to approach this in a different manner.

Here is at least what I think is relevant database information. I considered storing the thread_status in the blog_xhref_categories table though it would become redundant if the blog thread were published in multiple categories. In example I would categories a blog thread about "Opera 10" in both the categories "Software" and "Web Design & Development". Thoughts please?

Category = '1'
Status = '1'

TABLE: blog_threads
COLUMN: thread_id
COLUMN: thread_status

TABLE: blog_xhref_categories
COLUMN: xhref_id
COLUMN: xhref_category_id
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: SELECT two rows in two tables that must match two values?

Post by VladSun »

JAB Creations wrote:I've had enough practice with LEFT JOINS to take an educated guess I may need to approach this in a different manner.
viewtopic.php?f=2&t=90376

I'm still waiting ...
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: SELECT two rows in two tables that must match two values?

Post by VladSun »

JAB Creations wrote:Thoughts please?
What have you tried so far?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: SELECT two rows in two tables that must match two values?

Post by JAB Creations »

I got it working...sorry about the confusion!
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: SELECT two rows in two tables that must match two values?

Post by John Cartwright »

JAB Creations wrote:I got it working...sorry about the confusion!
For the sake of future readers.. would you please share your solution.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: SELECT two rows in two tables that must match two values?

Post by JAB Creations »

I usually do though I forgot. You know I do this almost all the time. :P

Code: Select all

SELECT thread_date, thread_meta_description, thread_title, thread_urlFROM blog_xhref_tagsLEFT JOIN blog_threadsON xhref_thread_id = thread_idLEFT JOIN blog_tagsON xhref_tag_id = tag_idWHERE tag_name_base = '".$cat."'ORDER BY thread_title ASC
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: SELECT two rows in two tables that must match two values?

Post by VladSun »

So, you need a LEFT JOIN? ;)
What do you do with all the rows which have
NULL, NULL, NULL, NULL
values?!?

I've told you Jab - you DO NOT need a LEFT JOIN, but you wouldn't listen...
Yes, I know you do this almost all the time
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: SELECT two rows in two tables that must match two values?

Post by JAB Creations »

I'm afraid my detection is patterns isn't as complex as others so my learning style is a bit more blunt. :| On the upside I always test the output with phpMyAdmin so I do things like use LIMIT and selecting only the columns I need to reduce wasting memory. I'm not sure if that helps any?

Any way I think I finally understand the purpose of INNER JOIN: you can select columns from both tables, correct? I promise I'll make you proud one day VladSun if you can first tolerate my shenanigans. :mrgreen:
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: SELECT two rows in two tables that must match two values?

Post by VladSun »

INNER JOIN creates a row by joining the corresponding table rows only if the ON condition is true.
You should imagine that after every *JOIN line you have a new table made out of the previous such table and the newly JOINed one:
[sql] SELECT    ....FROM        (            (                table1                * JOIN                table2 ON ...            ) [considered AS join_table1]            * JOIN            table3 ON  ...        ) [considered AS join_table2]         JOIN        table4 ON  ...        [considered AS join_table3] [/sql]
which is
[sql] SELECT    ....FROM        (            join_table1]            * JOIN            table3 ON ...        ) [considered AS join_table2]         JOIN        table4 ON ...        [considered AS join_table3] [/sql]
which is
[sql] SELECT    ....FROM        join_table2    JOIN        table4 ON  ...        [considered AS join_table3] [/sql]
which is
[sql] SELECT    ....FROM    join_table3 [/sql]

And... if you want me to help you, please (for a 2nd time) - when you use * JOIN, DO put the table names before any column field. I am not the one whom is clear that thread_date column belongs to the blog_threads table, right?
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply