Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    6

    Unanswered: subquery substitute?

    i am using mysql 3.56

    i have a problem where i am almost sure that a subquery is the only solution. but my version of mysql does not support sub selects as well as not the following:

    Foreign keys 4.1
    Views 5.0
    Stored procedures 5.0
    Triggers 5.0
    Unions 4.0
    Full join 4.1
    Constraints 4.1 or 5.0
    Cursors 4.1 or 5.0
    R-trees 4.1 (for MyISAM tables)
    Inherited tables Not planned
    Extensible type system Not planned

    so i am basically wondering how you substitute a subquery?

    cheers

    Nav

  2. #2
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    What is your current/desired query?

  3. #3
    Join Date
    Aug 2003
    Posts
    6
    Originally posted by Mincer
    What is your current/desired query?
    SELECT outletname, port
    FROM outlet
    WHERE outletseq NOT IN (
    SELECT o.outletname, o.slot
    FROM outlet o LEFT OUTER JOIN
    phonepanel p ON p.outletseq = o.outletseq LEFT OUTER JOIN
    datapanel d ON d.outletseq = o.outletseq
    WHERE p.outletseq IS NULL AND
    d.outletseq IS NULL );

    thanks for helping out Mincer

    Nav

  4. #4
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    It looks to me like you are trying to find something that isn't there. The subquery returns rows that are only in outlet, but the main query seems to looks for rows that are not in outlet. What is it you are looking for, exactly?
    Last edited by aus; 10-02-03 at 16:48.

  5. #5
    Join Date
    Aug 2003
    Posts
    6
    kinda complicated:

    ok tabe outlet has outletname and a n of slots:
    e.g.

    outletname Slot outletseq(pk)
    ------------- ------- -----------
    outletA1 A 1
    outletA1 B 2
    outletA1 C 3
    outletA1 D 4
    outletA2 A 5
    (so on)

    now, IF a outletslot is been used it can either be for a phone or data and thus the outletseq is sent to the data or phone table.
    but if a outlet is not been used then it only exisit in the outlet table and other link exist.

    The broblem is that my result must be a entire outlet (slot A-wotevr) in which no slots are been used.

    err.. hope this explains

    Cheers

    Nav

  6. #6
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    Try running only the subquery without the WHERE clause and take a look at your results. The results you want will be a subset of the records returned. You can then weed out the records you don't want with the WHERE clause. If you are looking for the outlets not attached to any panel, then the WHERE clause you have already should do the trick. I might not be seeing your side of it. If this doesn't work try executing
    mysqldump <yourdbname> outlet phonepanel datapanel > outletquery.sql
    then attach the sql file so that I can set up those tables in my database and work from there.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •