CODEWITHSUNDEEP
phpmysqlcodeigniterjoinwhere-clause
fin
fin

Reputation: 93

Join across multiple tables using values from joined tables

I have the follow tables:

systems
-----------
id
name
price
online
productid


specifications
-------------
id
systemid
type
componentid
quantity


components
-------------
id
name
brand
type
description

I need to filter across these tables with multiple options. Each system has multiple specifications rows, and each 'specifications' row links to it's corresponding 'components' row.

My issue is this:

I need to be able to filter systems by multiple properties based on the table joins. I've been using code that'll allow me 1 search option, but nothing further:

select
    `systems`.`id`,
    `systems`.`name`, 
    `specifications`.`type` 
from `systems` 
    join specifications 
        on `systems`.`id` = `specifications`.`systemid` 
    join components 
        on `specifications`.`componentid` = `components`.`id`
where 
    `specifications`.`type` = 'cpu' 
    and `components`.`brand` = 'amd'

So, that'll let me do a join where the specifications type is CPU and the brand is AMD, but if I add something else to look for too, like specifications.type ='graphics' AND components.brand = 'nvidia' it just doesn't work. I think this is inherent in the way joins work, as I've said, I'm having trouble articulating the issue here as I'm quite new to these more complex database transactions and would greatly appreciate being pointed in the right direction!

I'm using CodeIgniter as my framework, and I'd like to try and get the bottom of this via MySQL as opposed to doing it in PHP if it's possible - as I'd like a better understanding of what's going on here.

Upvotes: 0

Views: 153

Answers (2)

fin
fin

Reputation: 93

Ok, I've got it working, by having each query run as a subquery.

So it returns all the IDs of systems that have an AMD processor, to a conditional IN clause of finding all the systems that have an NVIDIA graphics card.

SELECT  `systems`.`id` ,  `systems`.`name` ,  `specifications`.`type` 
FROM  `systems` 
JOIN specifications ON  `systems`.`id` =  `specifications`.`systemid` 
JOIN components ON  `specifications`.`componentid` =  `components`.`id` 
WHERE (
    `specifications`.`type` =  'graphics'
    AND  `components`.`brand` =  'nvidia'
    )
AND (
    `systems`.`id` IN (
          SELECT  `systems`.`id` 
          FROM  `systems` 
          JOIN specifications ON  `systems`.`id` =  `specifications`.`systemid` 
          JOIN components ON  `specifications`.`componentid` =  `components`.`id` 
     WHERE (
          `specifications`.`type` =  'cpu'
          AND  `components`.`brand` =  'amd'
          )
     )
 )

Programmatically, I find it quite cumbersome, and I'm not sure how it stacks up efficiency-wise - being a largely self-taught programmer I'm always trying to make sure I'm doing things the "right" way. Can anyone see any issues with running it this way? Would it be better to have CodeIgniter return that set of IDs instead?

Bare in mind, that this question was simplified somewhat, and it will eventually contain several subqueries - though the site itself will never be under a massive load.

Upvotes: 0

Darrrrrren
Darrrrrren

Reputation: 6078

Do you mean to say that

select `systems`.`id`,`systems`.`name`, `specifications`.`type` from `systems`
join specifications on `systems`.`id` = `specifications`.`systemid` 
join components on `specifications`.`componentid` = `components`.`id`
where 
     (`specifications`.`type` = 'cpu' AND `components`.`brand` = 'amd') OR
     (`specifications`.`type` = `graphics` AND `components`.`brand` = `nvidia`)

Does not work?

What about something like this

SELECT S.`id`, S.`name`, P.`type` FROM `systems` S 
JOIN `specifications` P ON S.`id` = P.`systemid`
WHERE S.`id` IN (

    SELECT S2.`systemid` AS id FROM `specifications` S2
    JOIN `components` C2 ON S2.`componentid` = C2.`id`
    WHERE S2.`type` = 'cpu' AND c2.`brand` = 'amd'
) AND S.`id` IN (

    SELECT S3.`systemid` AS id FROM `specifications` S3
    JOIN `components` C3 ON S3.`componentid` = C3.`id`
    WHERE S3.`type` = 'graphics' AND c3.`brand` = 'nvidia'
)

Upvotes: 1

Related Questions