join from the SAME table????

[Cinquecento]

I've a table listing GROUP and CATEGORY relationships....

I'm passing a CATEGORY to a query and want to get back those CATEGORY's in the same group....but.....

SELECT CATEGORY from iCat WHERE GROUP = (select GROUP from iCat WHERE CATEGORY = 'STR');

just don't work......

I want it to return a list of CATEGORY's in the same GROUP as 'STR'....

any hints?

Ian

[bastien]

the only catergory is "STR"

which field holds the "STR" identifier?

[Cinquecento]

CATEGORY - 'STR' is just an example - it would be replaced by a variable in due course....

In this case I want to find all of the CATEGORY's in the same GROUP as 'STR'.

It's easy to find which GROUP 'STR' belongs to - but how do I use that value to find all the CATEGORYs that use that GROUP?

Did I explain that right?

[[deleted]]

Gotta love MySQL for it's lack of subqueries! 🙂

PostgreSQL is also free, and it supports SQL properly, including subqueries.

Anyway, you can self-join using an alias:

SELECT b.*
FROM table AS a, table AS b
WHERE a.grp = b.grp
AND a.category = 'str';

[Cinquecento]

I wondered why my downloaded 'generic' SQL guide didn't work with subqueries!

Postgresql! Why do I always back the loser! Must be my british 'back the underdog mentality' at play.

I hope I've not wasted my time learning PHP as well!

Ian

[[deleted]]

hehe well, you win some you lose some 🙂

[dnszero]

Flag on the play!

Cheap shot taken at mysql just because someone didn't know about aliases!

[[deleted]]

hmm.. not really a cheap shot, in cases like these you'll probably find that a subquery is much more efficient.

Besides, how well do you think this self-join method is going to perform when there are 50 million rows in the table? or when the value comes from four levels deep? Would you do a fourfold self-join to get one value every time?

[Bunkermaster]

probably 😃

[[deleted]]

btw, utsl.. wtf?

[Bunkermaster]

Use The Source Luke 😃

[[deleted]]

Ah, ITTWWYM!

[dnszero]

Good point.

[Bunkermaster]

I thought this was what you meant?

[[deleted]]

🙂

[csn]

Originally posted by vincente
Linux + PHP + PostgreSQL = succes

What's succes? :p

Isn't there an A(pache) in there? :p :p :p