[RESOLVED] join help

cgraz

I'm using MySQL - subqueries are supported although I'd preferably like to do this w/a join:

I have two tables

compass_blocks

id (PK)
description
position

compass_blocks_position

blocks_id (FK)
pageID

I'm trying to grab the id and description fields from the table compass_blocks. I have an existing $id (from compass_blocks) as a PHP variable, and I want to grab all of the blocks that are in the same compass_blocks.position as the current $id I'm holding, but that also are on the same compass_blocks_position.pageID as the current $id OR that have compass_blocks_position.pageID = 'cps-all'

In pseudo:

SELECT DISTINCT cb.id, cb.description FROM compass_blocks AS cb 
	WHERE cb.position = @position@ AND (cbp.pageID = @pageID@ OR cbp.pageID = 'cps-all')

where @position@ represents the position of $id (only one position allowed) and @pageID@ represents the pageID of $id (there could be more than one, as blocks can be placed on multiple pages).

I've tried multiple variations of joins (I'm only capable of some very basic joins) and couldn't quite get it.

tomhath

Untested:

SELECT DISTINCT cb.id, cb.description 
FROM compass_blocks AS cb 
INNER JOIN compass_blocks_position cbp
   ON cbp.blocks_id = cb.id
WHERE cb.position = @position@
  AND (cbp.pageID = @pageID@ OR cbp.pageID = 'cps-all')

cgraz

Thanks Tom. The problem is I don't have @position@ or @pageID@ - I only have $id and need to grab these two as part of my query. Hope this makes more sense.

tomhath

Okay, I misunderstood the first time.

You need to find the first pair of rows in a subselect so you can join back to the tables in order to find the other rows. Something like this:

SELECT DISTINCT cb.id, cb.description
FROM (
    SELECT cb1.position, cbp1.pageID
    FROM compass_blocks AS cb1
    INNER JOIN compass_blocks_position cbp1
       ON cbp1.blocks_id = cb1.id
    WHERE cb1.id = $id
    ) AS r1
INNER JOIN compass_blocks AS cb
  ON cb.position = r1.position
INNER JOIN compass_blocks_position cbp
  ON cbp.blocks_id = cb.id
WHERE cbp.pageID = r1.pageID
   OR cbp.pageID = 'cps-all'

cgraz

Wow. Thanks Tom! That actually worked perfectly.

I'm having a bit of trouble wrapping my head around joins, despite all of the tutorials and books I've read. This query seems to make some sense to me after looking at your method and breaking it down into steps. If you have any recommendations on how I can comprehend anything other than the most simple join, I'd appreciate it.

Thanks again!

tomhath

Practice is the best help. Looking at it again, there was no need for a subselect here. Try working it out with a 4-way join.

cgraz

That was good practice. It looks as if I'm getting the same results using this:

SELECT DISTINCT cb.id, cb.description 
FROM compass_blocks AS cb1 
INNER JOIN compass_blocks_position cbp1
	ON cbp1.blocks_id = cb1.id
INNER JOIN compass_blocks AS cb
	ON cb.position = cb1.position
INNER JOIN compass_blocks_position cbp
	ON cbp.blocks_id = cb.id
WHERE cb1.id = 22 AND (
	cbp.pageID = cbp1.pageID
	OR 
		CASE WHEN cbp1.pageID = 'cps-all' 
			THEN 1 = 1 
		ELSE cbp.pageID = 'cps-all' 
		END
	)
ORDER by cb.sort_weight DESC;

I added the CASE statement into your original query as well as I had to make sure I was getting all the results I needed.

Other than the case statement, is this what you were envisioning with the 4-way join?

tomhath

That's it. It's an uncommon way to join back to the same table (indirectly through the second table). I would probably use the subselect version because to me it's easier to read, but there's nothing wrong with the second one.