Mysql Join Issue

[scjmoore]

Hello All,

I am working on a small PHP / Mysql project and I have ran aground on this issue, and I am looking for some advice as to why this is not working. What I am trying to acheive is to show a list of blog post titles, along with their respective categories that they are assigned too, However I am unable to get my query to work. below are my 3 tables structure:

The Posts table is called ‘posts’

±------------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------------±-------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| postname | varchar(255) | NO | | NULL | |
| postcontent | varchar(255) | NO | | NULL | |
| postdate | date | NO | | NULL | |
| authorid | int(11) | NO | | NULL | |
±------------±-------------±-----±----±--------±---------------+

The Category Table is called ‘category’

±------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
±------±-------------±-----±----±--------±---------------+

The Lookup table is called ‘postcategory’

±-----------±--------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-----------±--------±-----±----±--------±------+
| postid | int(11) | NO | PRI | 0 | |
| categoryid | int(11) | NO | PRI | 0 | |
±-----------±--------±-----±----±--------±------+

My query

mysql> select id, postname from posts join postcategory on posts.id = categoryid;
Empty set (0.00 sec)

so far example it should look like this on the web page

Post Name Post Category
Linux DNS How Too Linux
Windows 10 Hacks Windoiws

Again grateful for any help.

Joe

[r937]

@scjmoore

posts.id = categoryid

this is wrong, you should be joining on postid

[scjmoore]

Thanks R937, I tried your advice but I am still getting an empty response;

mysql> select id, postname from posts join postcategory on postid = categoryid;
Empty set (0.02 sec)

[molona]

He didn’t mean that. He means posts.id=postcategory.postid

[r937]

@molona

He didn’t mean that. He means posts.id=postcategory.postid

aw, you spoiled my fun by giving it away

[molona]

Oops! Sorry

[scjmoore]

Thanks r937 and Molana,

am I correct in thinking in order for my output to show the postname and the assigned category I will need two inner joins?

mysql> select id, postname from posts join postcategory on posts.id = postcatego
ry.postid;
±—±-----------------------+
| id | postname |
±—±-----------------------+
| 6 | testing centos |
| 7 | testing 24 |
| 7 | testing 24 |
| 8 | This is a windows Test |
| 9 | Linux Test |
±—±-----------------------+

so that it outputs like the below

Postname Post Category
Testing Centos Linux
Windows Test Windows

etc…

Joe

[molona]

Whether you need two inner joins or other type depends on your data. In this case you’re creating a various-to-various relationship so yes, you’ll need two inner joins.

[r937]

[quote=“scjmoore, post:7, topic:213867, full:true”]
am I correct in thinking in order for my output to show the postname and the assigned category I will need two inner joins?[/quote]

with your current table design, yes

you could drop the id from your category table, and use the category name itself in the postcategory table, and then you’d need only one join

you’d still use three tables (to ensure relational integrity from postcategory to category), but you’d never have to include the category table in any queries

well, except for that one query where you want to show all categories, even if they have no posts