Need to change union statement to join for table

Here is my union that grabs all models (table alias mp) and products  (table alias a) that are ties to a specific term_id (ie. term_id = 472)

SELECT b.term_taxonomy_id as btermtaxid, c.term_taxonomy_id as ctermtaxid, c.term_id, mp.* FROM WPPPposts mp
INNER JOIN WPPPterm_relationships b ON (mp.ID = b.object_id)
INNER JOIN WPPPterm_taxonomy c ON (b.term_taxonomy_id = c.term_taxonomy_id)

 WHERE  c.term_id = 472 AND mp.post_type = 'models' AND mp.post_status = 'publish' AND c.taxonomy = 'modelcategory'
UNION

SELECT b.term_taxonomy_id as btermtaxid, c.term_taxonomy_id as ctermtaxid, c.term_id, a.* FROM WPPPposts a
INNER JOIN WPPPterm_relationships b ON (a.ID = b.object_id)
INNER JOIN WPPPterm_taxonomy c ON (b.term_taxonomy_id = c.term_taxonomy_id)

 WHERE  c.term_id = 472 AND a.post_type = 'products' AND a.post_status = 'publish' AND c.taxonomy = 'modelcategory'
What I need to do is move it so that all the models (table alias mp) have their respective products (table alias a) are joined (rather than as another row as UNION does). I have tied joining to the term_taxonomy_id but was unsuccessful in connecting it to both models and products.
m2ewAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
Well, you have to have a common denominator for models and products. From what we see I can't determine one. If you want to try with the term_taxonomy_id, then this would go through WPPPterm_relationships b, which I'd put first, then:

SELECT b.term_taxonomy_id as btermtaxid, c.term_taxonomy_id as ctermtaxid, c.term_id, m.*, p.*
FROM WPPPterm_relationships b
INNER JOIN WPPPposts m ON m.ID = b.object_id AND m.post_type = 'models' AND m.post_status = 'publish'
INNER JOIN WPPPposts p ON p.ID = b.object_id AND m.post_type = 'products' AND m.post_status = 'publish'
INNER JOIN WPPPterm_taxonomy c ON (b.term_taxonomy_id = c.term_taxonomy_id)
WHERE  c.term_id = 472 AND c.taxonomy = 'modelcategory' 

Open in new window


Too less information about the meaning of the data. I assume this would give you a record per combination of models and products belonging to the same object_id, ie. 3 models and 3 products would create 9 combinations, while there maybe would be exactly one model for one product.

The minor problem this has is joining a table twice with same field names, this'll let MSSQL Server add suffixes to the fields of the resultset.

Bye, Olaf.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Will help if you can post sample data. Without that we can't test, so this is my guess:
SELECT b.term_taxonomy_id as btermtaxid, c.term_taxonomy_id as ctermtaxid, c.term_id, p.* 
FROM WPPPposts p 
	INNER JOIN WPPPterm_relationships b ON (mp.ID = b.object_id) 
	INNER JOIN WPPPterm_taxonomy c ON (b.term_taxonomy_id = c.term_taxonomy_id)
WHERE  c.term_id = 472 AND c.taxonomy = 'modelcategory' 
  AND p.post_type IN ('models', 'products') AND p.post_status = 'publish'

Open in new window

m2ewAuthor Commented:
Hi Olaf,
term_taxonomy_id is the common denominator. However when I do the join on it I get zero rows returned.

When I run the SELECT statement below (without joining both) I can clearly see the common values. If I change products to models it will pull the data as well
SELECT b.term_taxonomy_id as btermtaxid, c.term_taxonomy_id as ctermtaxid, c.term_id, p.*
FROM WPPPterm_relationships b
INNER JOIN WPPPterm_taxonomy c ON (b.term_taxonomy_id = c.term_taxonomy_id)
INNER JOIN WPPPposts p ON p.ID = b.object_id AND p.post_type = 'products' AND p.post_status = 'publish'

WHERE  c.term_id = 472 AND c.taxonomy = 'modelcategory'
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

awking00Information Technology SpecialistCommented:
Sample data and expected results are always helpful.
m2ewAuthor Commented:
I have attached the output for both select statements below. In this case, and in many the same product is assigned with multiple models, i.e. models 14677,14767 and 14766 are assigned to the products ID 14762. I have tried inner join, left join and right join using term_taxonomy_id, but it nets zero results.

Statement 1 for products
SELECT b.term_taxonomy_id as btermtaxid, c.term_taxonomy_id as ctermtaxid, c.term_id, p.*
FROM WPPPterm_relationships b
INNER JOIN WPPPterm_taxonomy c ON (b.term_taxonomy_id = c.term_taxonomy_id)
INNER JOIN WPPPposts p ON p.ID = b.object_id AND p.post_type = 'products' AND p.post_status = 'publish'

WHERE  c.term_id = 472 AND c.taxonomy = 'modelcategory'

Statement 2 for models
SELECT b.term_taxonomy_id as btermtaxid, c.term_taxonomy_id as ctermtaxid, c.term_id, p.*
FROM WPPPterm_relationships b
INNER JOIN WPPPterm_taxonomy c ON (b.term_taxonomy_id = c.term_taxonomy_id)
INNER JOIN WPPPposts p ON p.ID = b.object_id AND p.post_type = 'models' AND p.post_status = 'publish'

WHERE  c.term_id = 472 AND c.taxonomy = 'modelcategory'
WPPPterm-relationships.xls
Olaf DoschkeSoftware DeveloperCommented:
Well, what's the object_ids? These are used to joinn both model and product to b. Since these are matched to m.ID or p.ID and all IDs differ, you don't match them into the same b row, of course.

Also your sample just shows the two resultsets, it would be better you extract some data of each single table.

Bye, Olaf.
m2ewAuthor Commented:
Hi Olaf,
Here are revised select statements with table structure attached. It turns out I don't need the term_id as the term_taxonomy_id will work. I think for the select / join statement it has to pull the products in WPPPposts first, then go through the joins WPPPterm_relationships and join WPPPposts at the end to get the models.

SELECT p.*
FROM WPPPposts p INNER JOIN WPPPterm_relationships b ON (p.ID = b.object_id)
WHERE  b.term_taxonomy_id = '487' AND (p.post_type =  'models' or p.post_type = 'products') AND p.post_status = 'publish'

SELECT b.*
FROM WPPPposts p INNER JOIN WPPPterm_relationships b ON (p.ID = b.object_id)
WHERE  b.term_taxonomy_id = '487' AND (p.post_type = 'models' or p.post_type = 'products') AND p.post_status = 'publish'
WPPPposts-2.xls
WPPPterm-relationships-2.xls
Vitor MontalvãoMSSQL Senior EngineerCommented:
m2ew, you didn't give any feedback in my suggestion.
m2ewAuthor Commented:
Hi Vitor,
I need a join statement. With yours I get all the products and models in individual rows. I actually need to join all the models to the products through the term_taxonomy_id WPPPterm_relationships table. The attachments back to Olaf have the table data.
Olaf DoschkeSoftware DeveloperCommented:
I also see the term_taxonomy_id as much more important than the object ids, but it's not present in the c, is it?

The solution might do something like where WPPPpost.ID In (Select object_id From WPPPterm_relationships WHERE term_taxonomy_id='487').

Nevertheless you don't have anything to put products and models side by side, if there is no self reference in the WPPPpost table like a parent child relationship, without a join condition you could only make a cross join, which would combine every combination. In this case of one product and three models that would fit your needs, but it wouldn't work in the way of putting products to the left and models to the right and whatever there's more of determines the number of result records. This is not really a job for SQL but for the presentation layer showing two lists.

SELECT b.term_taxonomy_id as btermtaxid, c.term_taxonomy_id as ctermtaxid, c.term_id, m.*, p.*
FROM WPPPposts m, WPPPposts p
FULL JOIN WPPPterm_relationships b on ON m.ID = b.object_id OR p.ID = b.object_id
INNER JOIN WPPPterm_taxonomy c ON (b.term_taxonomy_id = c.term_taxonomy_id)
WHERE  c.term_id = 472 AND c.taxonomy = 'modelcategory' AND m.post_type = 'models' AND m.post_status = 'publish' AND m.post_type = 'products' AND m.post_status = 'publish' 

Open in new window


Unsure whether that will multiply too many records. Don't be tempted to fi it with DISTINCT, though.

Bye, Olaf.
m2ewAuthor Commented:
Hi Olaf,
Getting a FULL JOIN error on statement in line 3. Also, I think it has to be FULL OUTER JOIN for mysql
Olaf DoschkeSoftware DeveloperCommented:
Yes, there is no full inner join. Anyway, see what you get in other examples, other values than 497 (also should be '497').

Bye, Olaf.
m2ewAuthor Commented:
So it turns out I had to rearrange my joins. Here is a working statement that pulls models and assigns products

Select z.ID, z.post_title, z.post_type, a.ID, a.post_title, a.post_type from WPPPposts z
INNER JOIN WPPPterm_relationships e ON z.ID = e.object_id
INNER JOIN WPPPterm_taxonomy c ON c.term_taxonomy_id = e.term_taxonomy_id
INNER JOIN WPPPterms d ON c.term_id = d.term_id
INNER JOIN WPPPterm_relationships f ON f.term_taxonomy_id = e.term_taxonomy_id
INNER JOIN WPPPposts a ON f.object_id = a.ID
WHERE z.post_type = 'models' AND c.taxonomy = 'modelcategory' AND  a.post_type = 'products' AND

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
m2ewAuthor Commented:
It was the only complete statement that pulled all the data in using joins. Olaf was on the right track, but I had to rework the query and join order to get it to work.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.