Mysql Join Issue

[yobo]

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

[Barand]

You need to join them via the lookup table

SELECT 
    postname 
  , name  
FROM posts p
JOIN lookup l ON p.id = l.postid
JOIN postcategory c ON l.categoryid = c.id

[yobo]

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.

[Barand]

Those 1's should be l (lowercase L)

[yobo]

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'

[Muddy_Funster]

 

could you post the results of doing a DESCRIBE on each table?