Query Problems

[Huntdawg]

Query Problems

Hi! I've been having some problems with a query for a while and I just can't seem to figure it out.

I need to combine the following queries into one (below). Basically, I have two tables and I would like to combine them into one for output into html. The two tables are `categories` and `permissions`. I would like all of the categories to show and just some of the permissions (the ones that pertain) but want it to show null in the array if there aren't permissions for this user.

Here are the two queries in question:

Query 1 -
SELECT categories_id, menuname, ordering FROM categories ORDER BY ordering ASC

Query 2 -
SELECT permissions_id, user_id, category_id FROM permissions WHERE user_id = 12

Here's the output I'm looking for:

+----------------+------------+------------+------------------+----------+--------------+
| categories_id  | menuname   |  ordering  |  permissions_id  | user_id  | category_id  |
+----------------+------------+------------+------------------+----------+--------------+
|       1        |   Admin    |     1      |      1           |   12     |      1       |
|       2        |   Jobs     |     2      |      2           |   12     |      2       |
|       3        |   About Us |     3      |      NULL        |   NULL   |      NULL    |
+----------------+------------+------------+------------------+----------+--------------+

Can anybody help? I've tried LEFT JOIN, but it only pulls up the ones with the user_id in it.

Oh and here's the platform, Apache 2, PHP 4.2, MySQL 3.23

Thank you in advance!!
Chris

[Cheeky Chino]

SELECT c.categories_id, c.menuname, c.ordering, p.permissions_id, p.user_id, p.category_id
FROM categories c
JOIN permissions p
on c.categories_id = p.category_id
and p.user_id = 12
ORDER BY c.ordering ASC


Try that one out

[Huntdawg]

Ok, I tried it as is, but it kept giving me an error with the SQL code, so I played with it a bit and here's what worked:

SELECT c.categories_id, c.menuname, c.ordering, p.permissions_id, p.user_id, p.category_id
FROM categories c
LEFT JOIN permissions p
on c.categories_id = p.category_id
and p.user_id = 12
ORDER BY c.ordering ASC

I just had to add "LEFT" in front of JOIN.

By the way, I noticed you put "c" and "p" in front of the field names. I tried it with and without and it worked the same. Is there a reason to put that in there? Like, is it shorthand for putting the actual table name in front of the field name?

Thank you very much for the help!!

Chris

[Cheeky Chino]

The c and p area alias's.

In general you would have a link on the 2 tables which would have been the categories_id column but u named it differently on the 2nd table.

If it was the same, and you were joining 2 tables, there would be an error as it wouldnt know what table you wanted to select the categories_id from.

So instead of saying tablename.categories_id you have t.categories_id where t is defined after the table name, ie c or p in yur case

Cheers,
I was working Based on knowledge of MSSQL so left join is what i use wasnt t1005 sure if itt woked in mySQL but LEFT JOIN works, kewl.