Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

(OT) MySQL select Question

by Anonymous Monk
on Jul 26, 2003 at 21:44 UTC ( [id://278142]=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello,
I'm really sorry for asking a non Perl question here, but i'm totally stuck and have nowhere else to go. If someone can suggest a good MySQL forum i'd love to know?

My question is:

I want to do a reletively simple select I think, something like this, except this doesn't work, so I need the correct syntax. I think I might have to use the JOIN, but I don't really know what that is either, my SQL is a bit dodgy to say the least! :-)

This doesn't work:

select * from table1 where table1.category = 1 OR table1.category = (s +elect table2.id from table2 where table2.parent_id = 1)

Can anyone suggest a way to make this work? I want to select all fields from table1 where either table1.category = 1 OR where table2.parent_id = 1, but I can't seem to find a way to do this.........

Many thanks, and if it helps to justify this question being posted on this forum, I am writing everything using the Perl DBI module, which is totally excellent!!!! :-)

Cheers,
Tom

Replies are listed 'Best First'.
Re: (OT) MySQL select Question
by antirice (Priest) on Jul 26, 2003 at 22:25 UTC

    I've actually been working on a module to allow you to mimic subselections in mysql. But alas, it's on the backburner right now.

    For this particular case, you could actually just use the following:

    select distinctrow table1.* from table1,table2 where table1.category=1 + or (table1.category = table2.id and table2.parent_id = 1);

    Hope this helps...and works as it's untested.

    antirice    
    The first rule of Perl club is - use Perl
    The
    ith rule of Perl club is - follow rule i - 1 for i > 1

      Thanks, I can't tell you how long that was taking me to work out. You got it right first time and saved me further hours of work, so thanks again!
      mysql doesn't do subqueries yet... You better do 2 selects and insert the data from the first into the second one.

      Greetz
      Beatnik
      ... I'm belgian but I don't play one on TV.

        Umm... I know MySQL doesn't do subselects. In the selection I posted, there aren't any subselections. The module of which I was speaking was so you could actually use SQL-92 style subselections but have them translated into multiple queries which, yes, do create temporary tables. For this particular query, however, there is no need to create a second table.

        Updated: Seemed a bit harsh. Just having a bad day :-/

        antirice    
        The first rule of Perl club is - use Perl
        The
        ith rule of Perl club is - follow rule i - 1 for i > 1

Re: (OT) MySQL select Question
by dbwiz (Curate) on Jul 27, 2003 at 05:35 UTC

    Subselects are already available in MySQL version 4.1, which is still in alpha, though.

    In this particular case, the subselect could be replaced by a join.

    Assuming that (from what I see in your example) table1.category is a foreign key for table2.id, then you can rewrite the query as follows

    SELECT table1.* FROM table1 INNER JOIN table2 ON (table1.category=table2.id) WHERE table1.category = 1 OR table2.parent_id = 1
Re: (OT) MySQL select Question
by dws (Chancellor) on Jul 27, 2003 at 00:13 UTC
    Can anyone suggest a way to make this work?

    MySQL 4.0 adds support for UNION. With that (and assuming table1 and table2 have the same columns), you could do something like:

    SELECT * FROM table1 WHERE table1.category = 1 UNION SELECT * FROM table1 JOIN table2 ON table1.category = table2.parent_id WHERE table2.parent_id = 1
    If you're stuck with an older version of MySQL, you're probably faced with running the two queries separately, then merging the results.

Re: (OT) MySQL select Question
by glwtta (Hermit) on Jul 26, 2003 at 23:55 UTC
    Apparently they already have subselects working in development, they've been promising them for MySQL 5.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://278142]
Approved by phydeauxarff
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others meditating upon the Monastery: (7)
As of 2024-03-29 12:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found