SQL join madness

Bloke · 2010-09-22 08:58:57

My SQL chip is officially fried here and I need some help if anybody knows more about this stupid query language than I do.

What I’m trying to achieve is to get one row for each TXP article that also includes the category information. If I use LEFT JOIN or EXISTS OR GROUP BY I either end up with multiple rows for each article — one per category — or missing information; both of which are far from ideal in this application. I probably need a couple of subselects or some esoteric MySQL functionality, but I’m too dim to figure out the syntax.

I want my record set to have these columns:

ID Posted Expires LastMod AuthorID Title... Category1 Cat1_title Cat1_parent Category2 Cat2_title Cat2_parent Status Custom1 Custom2...

And have a single row for each article in the returned set. There’s a one-to-one mapping of category(1|2) to txp_category.name for each row so it should be possible to ‘explode’ multiple tables into this view. But maybe it simply can’t be done in relational databases like MySQL. Perhaps I either have to manually filter out duplicates in the subsequent code loop or do a separate txp_category query for each textpattern row to get the extended information (which is yukky).

Thanks in advance for any pointers.

ruud · 2010-09-22 16:36:53

Can you post the query you tried. You need TWO joins (textpattern + txp_category + txp_category) and can group by article ID.

Bloke · 2010-09-22 21:23:32

Ahaaa, it appears I was close earlier. I had tried two joins but got lots of empty results and figured it was because the LEFT JOINs couldn’t be chained, but in trying to recreate the SQL I used earlier to post here, I ditched the SELECT * and used named columns instead. And now it works. I guess the * was confusing things because of the duplicate column names? (that’s a stab in the dark: it could also have been my rubbish typing)

This query appears to be working without any GROUP BY needed:

SELECT txp.ID, txp.Posted, txp.AuthorID, txp.Title,
     txp.Category1, txc1.title AS Cat1_title, txc1.parent AS Cat1_parent,
     txp.Category2, txc2.title AS Cat2_title, txc2.parent AS Cat2_parent
     FROM textpattern AS txp
     LEFT JOIN txp_category AS txc1 ON (txp.category1 = txc1.name)
     LEFT JOIN txp_category AS txc2 ON (txp.category2 = txc2.name)

At least, it appears to be working on the surface. I’ll need to run some tests on some more densely populated data to prove I’m not getting duplicates.

Many thanks for the pointer, ruud. Good to know I was on the right track(ish) and just needed a nudge to get my brain working properly.

EDIT: OK, so there are a few duplicates. I’ll try the GROUP BY!

Last edited by Bloke (2010-09-22 21:25:58)

Bloke · 2010-09-22 21:33:23

Pants. I’m getting duplicate records because one of the category names is shared by the article and image types. I tried adding:

WHERE txc1.type = 'article' AND txc2.type = 'article'

to the above query and I only get records where BOTH categoris are set. Hmmmm…

Last edited by Bloke (2010-09-22 21:36:01)

MattD · 2010-09-22 22:50:01

WHERE (tcx1.type is null OR tcx1.type = 'article' )
AND (tcx2.type is null OR tcx2.type = 'article' )

Oracle SQL is what I know but should be the same or similar

Last edited by MattD (2010-09-22 22:50:12)

Bloke · 2010-09-23 11:42:18

Thanks Matt, that seems to be working a treat. Makes sense, I’ll try it with the expanded result set to check for duplicates.

Bit of a monster query though just to link a couple of tables. Guess I’ve been spoiled with my years of working with entity-relational databases instead of this outdated SQL gibberish!

Textpattern CMS

Textpattern CMS support forum

#1 2010-09-22 08:58:57

SQL join madness

#2 2010-09-22 16:36:53

Re: SQL join madness

#3 2010-09-22 21:23:32

Re: SQL join madness

#4 2010-09-22 21:33:23

Re: SQL join madness

#5 2010-09-22 22:50:01

Re: SQL join madness

#6 2010-09-23 11:42:18

Re: SQL join madness

Board footer