CODEWITHSUNDEEP
mysqlsqlpivot
Jeepstone
Jeepstone

Reputation: 2601

Return 2 columns using subquerys in main query in mysql

I wish to populate a table on in a mysql database. Firstly I'd like to pull back all of the possibilities and them trim out the unrequired ones (easier than just adding them by hand).

The final table is:

combinations

combID
productID
type
content
exclude
extrafield2
extrafield6

The data comes from

extrafields_values

exvalueID
productID
extrafieldID
content

For each product I need to get return a row for each combination in extra_field_values (extrafieldID = 2 and extrafieldID = 6)

For instance:

Returns the exvalueID to extrafields2 and 6 for each combination

So far I've tried:

SELECT EV.productID, extraFieldID, content AS extrafield6, 
    (SELECT  content AS extrafield2 
     FROM wjf_extrafields_values AS EV2
         INNER JOIN wjf_products AS P2
     WHERE extraFieldID = 6) AS extrafield2 
FROM wjf_extrafields_values AS EV 
    INNER JOIN wjf_products AS P ON P.productID = EV.productID 
WHERE extrafieldID = 6

Upvotes: 1

Views: 94

Answers (2)

dave
dave

Reputation: 1560

I believe you just need to link your wjf_products table to your extrafield_values table twice as shown below.

select p.productID, ev1.content as extrafield2, ev2.content as extrafield6
from wjf_products p inner join extrafields_values ev1 on p.productID = ev1.productID
inner join extrafields_values ev2 on p.productID = ev2.productID
where ev1.extraFieldID = 2
and ev2.extraFieldID = 6

Upvotes: 1

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

Why not just create and use two views instead of a query.

View1 = Combinations

View2 = Subset of View1

Insert data using the View2

Upvotes: 0

Related Questions