Page 1 of 2 12 LastLast
Results 1 to 16 of 24

Thread: SQL help

  1. #1
    HEXUS.social member Agent's Avatar
    Join Date
    Jul 2003
    Location
    Internet
    Posts
    19,159
    Thanks
    733
    Thanked
    1,605 times in 1,044 posts

    SQL help

    How do you select somthing, while comparing it to another table and seeing if it appears in there ?

    For example, i have :

    SELECT username FROM phpbb_users WHERE username LIKE 'omega%' LIMIT 0, 30

    In pesudocode code i want :

    SELECT username FROM phpbb_users WHERE username LIKE 'omega%' AND WHERE username does not appear in table 'omega_members'

    I guess somthing like

    SELECT username FROM phpbb_users WHERE username LIKE 'omega%' AND WHERE username ! (APPEAR ?!) 'omega_members'

    Whats the SQL for somthing like APPEAR ? (thats not a valid SQL syntax)

    Hope that makes sence...

    Thanks
    Quote Originally Posted by Saracen View Post
    And by trying to force me to like small pants, they've alienated me.

  2. #2
    Senior Member Shad's Avatar
    Join Date
    Jul 2003
    Location
    In front
    Posts
    2,773
    Thanks
    22
    Thanked
    40 times in 24 posts
    You want a join. Will write you one in two tics...
    Simon


  3. #3
    Senior Member Shad's Avatar
    Join Date
    Jul 2003
    Location
    In front
    Posts
    2,773
    Thanks
    22
    Thanked
    40 times in 24 posts
    SELECT [phpbb_users.username] FROM phpbb_users LEFT JOIN omega_members ON [phpbb_users.username] <> [omega_members.username] WHERE [phpbb_users.username] LIKE 'omega%'


    I think that will work. Run it though Query Analyser first. Not come accross LIMIT before; is that the same as TOP?

    Also, note the use of [] because 'username' is a reserved word.
    Simon


  4. #4
    Senior Member
    Join Date
    Jul 2003
    Posts
    232
    Thanks
    0
    Thanked
    0 times in 0 posts
    If I'm thinking straight (not very common!) LIMIT is MySQL's implementation of TOP.
    Note that if you're querying a MySQL DB, the syntax will be slightly different from shad's (eg. the use of <> and '%' as a wildcard) Have a shufty at the MySQL manual
    Last edited by MrFlibble; 22-10-2003 at 08:00 PM.

  5. #5
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    Shad is right that you need a join but he's slightly out on the theory. A left join will return all results that don't exist in omega_users as well so you need to return all results that match and then only include those where you didn't get a match.

    In normal SQL you'd want a sub-select but MySQL doesn't support them (although the latest version is supposed to be including them).

    Anyway, the solution is as follows in MySQL

    SELECT pu.username
    FROM phpbb_users AS pu
    LEFT JOIN omega_users AS ou ON ou.username=pu.username
    WHERE ou.username IS NULL AND pu.username LIKE 'omega%'

    username isn't a reserved word in MySQL so you're safe there. And yes LIMIT is the same as TOP

  6. #6
    HEXUS.social member Agent's Avatar
    Join Date
    Jul 2003
    Location
    Internet
    Posts
    19,159
    Thanks
    733
    Thanked
    1,605 times in 1,044 posts
    Thanks to all that replied.
    Initially Shads wasn't working, and including them all like you mention AZ.

    Ive been playing around with this and a very good friend of mine who was keeping to the ANSI standards. We didn't know that mysql didn't support sub selects and have spent ages on it
    After much hacking about with various SQL statements, trying to combine different bits and reading various things on google, the star that is called Fanta got it with :

    SELECT username FROM phpbb_users LEFT JOIN omega_members ON username = utnick WHERE utnick is null AND username LIKE 'omega%'

    Its annoying, because i had somthing simlar to what shad had written without the []'s, and without direct references to the tables (username instead of phpbb_users.username) and i couldn't see why it wouldnt work

    Now Ive got 2 versions, i just need to fully understand them.
    Its become apparent though that My SQL doesn't keep the the ANSI standards which kept throwing us off

    Many thanks to everyone though, finally nailed this.
    Is postgreSQL any better for ANSI compatibility ?

    Oh, and where does the time go ? - would you belive ive spent 6 hours on this one statment ?. I have a sad life
    Quote Originally Posted by Saracen View Post
    And by trying to force me to like small pants, they've alienated me.

  7. #7
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    erm , the SQL statement from Fanta is exactly the same as mine. Only difference is you used the utnick field in place of username. I just used username as you hadn't specified the column in omega_users you wanted to reference

    But at least you got it working
    Last edited by Iain; 22-10-2003 at 10:47 PM.

  8. #8
    Senior Member Shad's Avatar
    Join Date
    Jul 2003
    Location
    In front
    Posts
    2,773
    Thanks
    22
    Thanked
    40 times in 24 posts
    Good stuff lads.

    So does TOP work in MySQL or is it not part of SQL-92?
    Simon


  9. #9
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    TOP doesn't work in MySQL. MySQL tries to follow the sql-92 standard but has some differences as follows

    http://www.mysql.com/doc/en/Differences_from_ANSI.html

  10. #10
    Senior Member Shad's Avatar
    Join Date
    Jul 2003
    Location
    In front
    Posts
    2,773
    Thanks
    22
    Thanked
    40 times in 24 posts
    http://www.microsoft.com/sql/

    How it should be done
    Simon


  11. #11
    TiG
    TiG is offline
    Walk a mile in other peoples shoes...
    Join Date
    Jul 2003
    Location
    Questioning it all
    Posts
    6,213
    Thanks
    45
    Thanked
    48 times in 43 posts
    Join is a little bit of overkill to be honest, you could do the following -

    where
    SELECT username
    FROM phpbb_users
    where username LIKE 'omega%' and username not in (select username from omega_members)

    Believe it or not but its a quicker operation than the join command. and MYSQL sucks, ms sql is much much better even if microsoft designed it...

    TiG
    -- Hexus Meets Rock! --

  12. #12
    Registered User
    Join Date
    Oct 2003
    Posts
    1
    Thanks
    0
    Thanked
    0 times in 0 posts
    The query that TIG sugested is a subquery which doesnt work in mysql versions proir to 4.1. So we had to formulate it as an equivalent join.

    The subquery and the join are performance wise usualy the same. They are converted by the optimizer to semi-joins (thats how sql server and most engines do it).

    And M$ bought sql server BTW. SQL server is actualy based on Sybase (which is still a seperate product). As of version 7 it became a very powerful engine. But they also hired the greatest db minds on this planet to redesign it

    The TOP is an ansi construct which is similiar to mysql's LIMIT function. LIMIT is actualy more powerfull then top because you can specify the starting postion like this :
    select * from table1 LIMIT 30,40 returns rows 30 through 40
    This is great for paging a large table to the browser so it would be good that ansi learned from mysql ( in this case ).
    Last edited by omegaFanta; 23-10-2003 at 03:05 PM.

  13. #13
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    Fanta is correct, an outer join will be at the very least just as performant as a sub-select and in most cases it will out-perform it. This is true for all the SQL databases I have used, albeit a short list of Oracle and MySQL

  14. #14
    Senior Member
    Join Date
    Jul 2003
    Posts
    232
    Thanks
    0
    Thanked
    0 times in 0 posts
    Originally posted by Shad
    http://www.microsoft.com/sql/

    How it should be done
    Don't start

    MySQL was designed purely for speed. It's very light on features
    It's also free

    MSSQL is great, it's visual, it's easy to admin. It's also a grand or so
    Last edited by MrFlibble; 23-10-2003 at 08:50 PM.

  15. #15
    Goat Boy
    Join Date
    Jul 2003
    Location
    Alexandra Park, London
    Posts
    2,428
    Thanks
    0
    Thanked
    0 times in 0 posts
    MySQL 4 is pretty good, and converging on some form of ACID compliance. 4 gives you foreign key constraints, subqueries and transactional locking and rollback (most of this with InnoDB tables). When they implement Stored Procedures and Triggers I think it will seriously rival MSSQL.

    MSSQL is good in the right environment, but in most instances I think MySQL (using the features mentioned above) is more appropriate.
    "All our beliefs are being challenged now, and rightfully so, they're stupid." - Bill Hicks

  16. #16
    Put him in the curry! Rythmic's Avatar
    Join Date
    Jul 2003
    Location
    Twyford, Berks
    Posts
    758
    Thanks
    1
    Thanked
    0 times in 0 posts
    You'd be much faster using a WHERE NOT EXISTS(SELECT * FROM omega_users ou WHERE ou.username=pu.username), but of course mySQL doesn't do that.

    Apparently MS will be enhancing TOP to give it paging abilities (MSSQLs biggest flaw) but not til Yukon....
    Now go away before I taunt you a second time.

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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