Page 1 of 1

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

Posted: Sun Nov 09, 2008 8:08 pm
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

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

Posted: Mon Nov 10, 2008 4:11 am
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 ...

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

Posted: Mon Nov 10, 2008 4:13 am
by VladSun
JAB Creations wrote:Thoughts please?
What have you tried so far?

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

Posted: Tue Nov 11, 2008 2:54 pm
by JAB Creations
I got it working...sorry about the confusion!

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

Posted: Tue Nov 11, 2008 6:56 pm
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.

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

Posted: Wed Nov 12, 2008 12:01 am
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

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

Posted: Wed Nov 12, 2008 1:18 am
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

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

Posted: Wed Nov 12, 2008 1:28 am
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:

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

Posted: Wed Nov 12, 2008 1:42 am
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?