[SOLVED] REALLY bad with JOIN queries...simple help, anyone???

[ShootingBlanks]

Hello, everyone - I'm pretty new to this, so this is probably easy, but I just can't seem to get it right!

 

Here's what I have:

• "documents" table with columns "doc_id" (primary - INT) and "doc_title" (VARCHAR)
  
• "categories" table with columns "cat_id" (primary - INT) and "cat_name" (VARCHAR)
  
• "doc_cat_lookup" table with columns "doc_id" (INT) and "cat_id" (INT)
  

 

Each document may belong to multiple categories.  For example:

• Documents A, B, & C belong to "category one"
  
• Documents D & E belong to "category two"
  
• Documents F, G, H, & I belong to "category three"
  
• Document J belong to "category four"
  

 

I need a query so that I select the document names and category names from the "documents" and "categories" table where the doc_ids and cat_ids match up.  So, based on the mock-data that I wrote above, I'd like the results to be:

 

"Category One" - Doc A Title

"Category One" - Doc B Title

"Category One" - Doc C Title

 

"Category Two" - Doc D Title

"Category Two" - Doc E Title

 

"Category Three" - Doc F Title

"Category Three" - Doc G Title

"Category Three" - Doc H Title

"Category Three" - Doc I Title

 

"Category Four" - Doc J Title

 

Thanks!...

 

 

[effigy]

Try this:

 

SELECT c.cat_name AS category, d.doc_title AS document FROM categories c, documents d, doc_cat_lookup dcl WHERE c.cat_id = dcl.cat_id AND d.doc_id = dcl.doc_id ORDER by category, document

[ShootingBlanks]

SELECT c.cat_name AS category, d.doc_title AS document FROM categories c, documents d, doc_cat_lookup dcl WHERE c.cat_id = dcl.cat_id AND d.doc_id = dcl.doc_id ORDER by category, document

 

That worked - thanks!!!

 

[ShootingBlanks]

Okay - I think I have overcomplicated things to the point where I'm getting messed-up again...

 

...here's the query that I have that worked:

SELECT categories.cat_name, doc_cat_lookup.cat_id, doc_cat_lookup.doc_id,
documents.title_full, documents.cat_id, documents.doc_id
FROM documents, categories, doc_cat_lookup
WHERE documents.doc_id = doc_cat_lookup.doc_id
AND categories.cat_id = doc_cat_lookup.cat_id
ORDER BY categories.cat_name, documents.title_full

 

Despite the clutter, that all worked fine.  But now I need to add something else to it...

 

In a similar way that I described how my categories tables would work in my original post, I have now added two USER tables.  They are:

• "users" - which has columns "user_id" (primary - INT) and "username" (VARCHAR)
  
• "doc_user_lookup" - which has columns "doc_id" and "user_id"
  

 

A document could have any number of users.

 

I need to add to that SELECT query a way to filter it out so that the results are FURTHER limited by the doc_user_lookup.user_id equaling the $_SESSION['user_id'] (which I have stored in the variable $userid).

 

THANKS!!!

 

[effigy]

What have you tried? It's the same theory as before.

[ShootingBlanks]

What have you tried? It's the same theory as before.

 

After the last "AND" in the "WHERE" part, I added:

AND doc_user_lookup.user_id = '$userid'

 

...but that didn't work. 

 

(i thought it would)

 

 

[effigy]

You need to add the new tables after the FROM clause and how they should be joined after the WHERE clause. Also, make sure you're properly escaping any variables that are being placed in SQL.

 

 

[ShootingBlanks]

You need to add the new tables after the FROM clause and how they should be joined after the WHERE clause. Also, make sure you're properly escaping any variables that are being placed in SQL.

 

I actually just added a parenthetical "AND" statement, and it seemed to work:

AND (doc_user_lookup.user_id = '$userid' AND doc_user_lookup.doc_id = documents.doc_id)

 

So, my finished query is:

SELECT categories.cat_name, doc_cat_lookup.cat_id, doc_cat_lookup.doc_id,
documents.title_full, documents.doc_id, documents.live_doc,
doc_user_lookup.user_id, doc_user_lookup.doc_id
FROM documents, categories, doc_cat_lookup, doc_user_lookup
WHERE documents.doc_id = doc_cat_lookup.doc_id
AND categories.cat_id = doc_cat_lookup.cat_id AND documents.live_doc = 'n' 
AND (doc_user_lookup.user_id = '$userid' AND doc_user_lookup.doc_id = documents.doc_id)
ORDER BY categories.cat_name, documents.title_full

 

Thanks for the help out!!!