SQL help

[Agent]

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

[Shad]

You want a join. Will write you one in two tics...

[Shad]

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.

[MrFlibble]

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

[Iain]

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

[Agent]

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

[Iain]

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

[Shad]

Good stuff lads.

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

[Iain]

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

[Shad]

http://www.microsoft.com/sql/

How it should be done

[TiG]

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

[omegaFanta]

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 ).

[Iain]

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

[MrFlibble]

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

[DaBeeeenster]

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.

[Rythmic]

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....