Jump to content

Mysql Join Issue


yobo

Recommended Posts

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

 

 

+-------------+--------------+------+-----+---------+----------------+
| 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
 
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
 
The Lookup table
 
+------------+---------+------+-----+---------+-------+
| 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

Link to comment
Share on other sites

Thanks Barand for your help however I am still getting errors, from folling your guidance,

 

my lookup table is called 'postcategory' my category table is called 'category' and my posts table is called 'posts'

 

using your sQL above 

 

mysql> select postname, name from posts p join lookup 1 on p.id = 1.postid join
postcategory c on 1.categoryid = c.id;
 
I get a Mysql error.
Link to comment
Share on other sites

Thanks Barand,  But I am still getting errors;

 

mysql> select postname, name from posts p join lookup l on p.id = l.postid join
postcategory c on l.categoryid = c.id;
 
ERROR 1146 (42S02): Table 'p3-blog.lookup' doesn't exist
 
mysql> select postname, name from posts p join category  l on p.id = l.postid jo
in postcategory c on l.categoryid = c.id;
 
ERROR 1054 (42S22): Unknown column 'l.postid' in 'on clause'
 
mysql> select postname, name from posts p join postcategory  l on p.id = l.posti
d join postcategory c on l.categoryid = c.id;
 
ERROR 1054 (42S22): Unknown column 'name' in 'field list'
 
mysql> select postname from posts p join postcategory  l on p.id = l.postid join
 postcategory c on l.categoryid = c.id;
 
I have replaced the names with my tables:
 
my lookup table is called 'postcategory' my category table is called 'category' and my posts table is called 'posts'
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.