i want to learn how to create a query where i use the IN and JOIN

[jasonc310771]

I have been asking for a few queries and really think it is time i learnt how to do this myself.

i have tried to understand how the query works that have been given to me in the past, but still none the wiser.

i have asked but only been told that the answers are in this forum somewhere, please can someone provide links to these help pages which will show me in detail how i use the IN and JOIN

[Fumigator]

Joins are really not that complicated. You have two tables, both tables have rows. When you JOIN rows from the two tables, you produce one row in your resultset every time a row in table1 matches a row in table2.

Here's a simple example:


name_table
----------
id name
1 mike
2 fred
3 jade
4 melinda

img_table
---------
name_id image
1 mike_bday.jpg
1 mike_dead.jpg
2 fred_on_bike.jpg
3 jade_swimming.jpg
3 jade_laughing.jpg


Let's see what happens if we do a JOIN with no "ON" clause like this:


SELECT n.name, i.image
FROM name_table as n
JOIN img_table as i


With this query, every row in name_table matches every row in img_table and you get this:


name image
mike mike_bday.jpg
mike mike_dead.jpg
mike fred_on_bike.jpg
mike jade_swimming.jpg
mike jade_laughing.jpg
fred mike_bday.jpg
fred mike_dead.jpg
fred fred_on_bike.jpg
fred jade_swimming.jpg
fred jade_laughing.jpg
jade mike_bday.jpg
jade mike_dead.jpg
jade fred_on_bike.jpg
jade jade_swimming.jpg
jade jade_laughing.jpg
melinda mike_bday.jpg
melinda mike_dead.jpg
melinda fred_on_bike.jpg
melinda jade_swimming.jpg
melinda jade_laughing.jpg


Obviously this is pretty useless, and will produce name_table number of rows times img_table number of rows (in this case 4 times 5, 20 rows). Imagine if you did this with tables containing thousands of rows. Will get you nowhere in a hurry.

So now we want to match up the images in img_table with the names in name_table. Simple add the "ON" clause, connecting the id in name_table with name_id in img_table:


SELECT n.name, i.image, n.id, i.name_id
FROM name_table as n
JOIN img_table as i
ON n.id = i.name_id


Now only rows where the ids match will be returned.


name image id name_id
mike mike_bday.jpg 1 1
mike mike_dead.jpg 1 1
fred fred_on_bike.jpg 2 2
jade jade_swimming.jpg 3 3
jade jade_laughing.jpg 3 3


Ah, that's better. Oh but melinda has no image, so nothing came back for melinda! We can still get a melinda row by using a LEFT OUTER join. Don't let the name confuse you-- it means that all rows in the "left" table (the first table listed" will be returned, even if there's no match for that row in the "right" table (the second table listed). You can just code it as "LEFT JOIN".


SELECT n.name, i.image, n.id, i.name_id
FROM name_table as n
LEFT JOIN img_table as i
ON n.id = i.name_id


Returns:


name image id name_id
mike mike_bday.jpg 1 1
mike mike_dead.jpg 1 1
fred fred_on_bike.jpg 2 2
jade jade_swimming.jpg 3 3
jade jade_laughing.jpg 3 3
melinda <null> 4 <null>



As you might expect, a RIGHT OUTER JOIN does the same thing, but it returns all rows from the "right" table even if there's no match on the "left" table.

So the different types of joins are:


JOIN (normal join)
INNER JOIN (same exact thing as JOIN, don't need to code the INNER but you can)
OUTER LEFT JOIN (return all rows in left table whether or not there's a match, don't need to code the OUTER but you can)
OUTER RIGHT JOIN (return all rows in right table whether or not there's a match, don't need to code the OUTER but you can)

[Fumigator]

The "IN" clause is just a shortcut way to specify a bunch of "OR" conditions on your WHERE clause.

These two statements do the same thing:


SELECT *
FROM table1
WHERE column1 = 'Aaaaa'
OR column1 = 'Bbbbb'
OR column1 = 'Ccccc'
OR column1 = 'Ddddd'



SELECT *
FROM table1
WHERE column1 IN ('Aaaaa', 'Bbbbb', 'Ccccc', 'Ddddd')

[jasonc310771]

ok seem to sort of understand that, i will play around with these examples and see what i get.

the only think i did not know is the ....

SELECT n.name, i.image, n.id, i.name_id
FROM name_table as n
LEFT JOIN img_table as i
ON n.id = i.name_id


n. and i.

what do these do?

[Fumigator]

They are alias names for the tables. Just makes it so you don't have to spell out the table names everywhere.

[jasonc310771]

been trying to go by what you have shown me and this is my code i have so far...


SELECT a.created_on a.topic_id b.topic_id c.category_id d.category_id FROM `forum_comments` as a
JOIN `forum_topic` as b ON a.topic_id = b.topic_id AND
JOIN `forum_category` as c ON b.category_id = c.category_id WHERE confirmed = '1'

not finished i know and shows error in mysql query of phpmyadmin


the forum_category table contains the categories and these relate to the forum_topics using the table field category_id

the topic_id in the forum_topic is also related to the topic_id that is in the forum_comments table

what i am trying to get is the create_on from the forum_comments table for each of the entries that are in the forum_category table where both the topic_id in both forum_topics and forum_comments are the same and where confirmed = '1'


basically... the same as what these coding forms does on the main page for the categorys, i need to get the username and date and time it was late updated with a new entry, so i can place it like this forum in my own forum pages.

[Fumigator]

shows error in mysql query of phpmyadmin

:confused:

[jasonc310771]

SELECT a.created_on AS a, a.topic_id AS a, b.topic_id AS b, c.category_id AS c
FROM `forum_comments` AS a, `forum_topics` AS b, `forum_category` AS c
WHERE a.topic_id = b.topic_id AND b.category_id = c.category_id AND a.confirmed = '1' AND b.confirmed = '1' AND c.confirmed = '1' ORDER BY a.created_on DESC


i get the following results..




a--------------------a--b--c
2008-12-11 18:45:04 41 41 3
2008-12-11 18:44:56 40 40 3
2008-12-11 18:44:51 39 39 3
2008-12-11 18:44:39 38 38 1
2008-12-11 18:44:24 37 37 2
2008-12-08 21:51:00 37 37 2
0000-00-00 00:00:00 36 36 2


but as you can see it is showing all of them, i only want to have the results for each of the 'c' field of this new table of results
so the expected results would be.


a--------------------a--b--c
2008-12-11 18:45:04 41 41 3
2008-12-11 18:44:39 38 38 1
2008-12-11 18:44:24 37 37 2



the most recent entry in the comments ordered by the 'created_on' field

[Fumigator]

One thing I just noticed is you are assigning the alias of "a" to both a column and a table. I have no idea what that would cause but you definitely don't want to be doing that-- if you want to assign alias names to your columns make sure they are different than your table alias names.

Then again, it may just work fine-- I can see that it looks like it actually worked so... :confused: :D

As for the results... so you are saying there is not a row in forum_topics with a topic_id of 40 and a category_id of 3?

[jasonc310771]

i dont know how i can to get this to sort of work but i did! lol

i shall add this to explain the table, it is as i am sure you will notice a forum i am creating for myself, yes i know there are many of them out there, but none that offer the category, topic and comments and all the ones out there that do has all the stuff i dont need, so created my own.

all works and does as it should, BUT.... the category page needs to have the date and the username of the most recent post made in each category. like normal forums have.

(same code as above)

SELECT a.created_on AS a, a.topic_id AS a, b.topic_id AS b, c.category_id AS c
FROM `forum_comments` AS a, `forum_topics` AS b, `forum_category` AS c
WHERE a.topic_id = b.topic_id AND b.category_id = c.category_id AND a.confirmed = '1' AND b.confirmed = '1' AND c.confirmed = '1' ORDER BY a.created_on DESC


there are gaps due to the post or missing posts are made for the other categorys, so this may help to explain what is going on in the DB's

the first 'a' ok this may be wrong so may change it if i have to but the first 'a' is the date of the post made the data is taken from the 'forum_comments' table

the next 'a' is the 'topic_id' from the 'forum_comments' table

the 'b' is the topic_id from the 'forum_topic' table

the 'c' is the category_id from the 'forum_category'

a--------------------a--b--c----comment
2008-12-11 18:45:04 41 41 3----most recent post in category 3
2008-12-11 18:44:56 40 40 3----the post before the most recent in category 3
2008-12-11 18:44:51 39 39 3----the post before the previous in category 3
2008-12-11 18:44:39 38 38 1----most recent post in cat, 1
2008-12-11 18:44:24 37 37 2----most recent post in cat, 2
2008-12-08 21:51:00 37 37 2----the post before the most recent in category 2
2008-11-01 00:11:00 36 36 2----the post before the previous in category 2

the results should return....

a--------------------a--b--c----comment
2008-12-11 18:45:04 41 41 3----most recent post in category 3
2008-12-11 18:44:39 38 38 1----most recent post in cat, 1
2008-12-11 18:44:24 37 37 2----most recent post in cat, 2

the most recent result where the comments refer to each of the categorys, so only one result for cat 1 and cat 2 and cat 3 and all the rest.

so there will be what seems missing posts but these are just replies made in other topics prior to the most recent.



quote...
As for the results... so you are saying there is not a row in forum_topics with a topic_id of 40 and a category_id of 3?

there would be an entry in forum_topic with a topic_id of 40 and a category_id of 3, but as this post was made prior to the most recent post in the [topic_id (40) and category_id of 3]
so this entry would not show up in the results because of it be an older post.

[Fumigator]

there would be an entry in forum_topic with a topic_id of 40 and a category_id of 3, but as this post was made prior to the most recent post in the [topic_id (40) and category_id of 3]
so this entry would not show up in the results because of it be an older post.

I understand that's how you'd like the query to work, but that's not how it is coded to work. If there is a topic id = '40' and a category_id = '3' it will be included, because that's how the query is coded.

When I coded the "latest post" portion of my custom forum (yes I made my own too :D ) I used two queries. The first query selects each category one at a time, the second query uses the category_id to find the most recent post in the posts table. Is it possible to do it in a single query? Maybe...

I would just do two queries.

[jasonc310771]

what is your query ? or queries ?

[Fumigator]

First query, select all categories.

Second query inside loop of resultset from first query, select post where category_id = id from first query, order by posting date descending. Just grab the first one from the resultset.

[jasonc310771]

pass.. that just went right over my head.

[jasonc310771]

looking at the IN and JOIN method maybe i could use that?

but i dont know what the LEFT JOIN means