| |
| |
| |
|
Page: 1 2 3
Comments:
<0> i need to ask a question
<0> $db_hostname = "localhost";
<0> $db_port = "";
<0> its asking for db_port .... what is the port number i should write here
<1> hey
<1> I've got a simple existing query that retunrs and id, title, etc.
<1> I now want to return records ONLY if that ID is not listed in a certain field in a moderation table
<1> how do I do that "not in" logic?
<2> where id not in (select statement)
<1> hm that simple?
<3> thats why it's SQL
<2> structured query language?
<3> sometimes described as "simple query language"
<1> hmm an issue
<3> blakespot, keep on mind, if you have many records in source table, and many in moderated - you will have a very slow performance
<3> use left joins with IS NULL checks or WHERE EXISTS
<1> whats a good pastebin like site?
<1> pastebin's not working
<3> try google with "nomorepaste" keyword
<4> .
<1> can i just paste here?
<1> a fewl ines
<4> yeah
<1> here:
<1> SELECT th.threadid, th.title
<1> FROM vbthread th, vbmoderatorlog ml
<1> WHERE th.forumid < 34
<1> AND th.threadid NOT IN (SELECT threadid FROM vbmoderatorlog)
<1> ORDER BY th.lastpost DESC LIMIT 10;
<1> get error "near (SELECT threadid..."
<3> is that MySQL?
<1> ya
<3> something lower that 4.1 ?
<3> *than
<1> i don't know the ver offhand
<1> is the NOT IN wrong?
<3> its correct
<1> if I take the AND ... NOT IN line out it works
<1> (as before)
<3> I dont see how you join those two tables
<1> Do I need to?
<1> look at this example
<1> mysql> SELECT name FROM animal WHERE id NOT IN (SELECT animal_id FROM animal_food);
<1> valid mysql
<1> where is he joining the tables?
<3> but you have two tables in the select
<1> oh sorry, I took that out after I pasted - obviously should have mentioned
<1> it;s now:
<3> means you will have records as much as all from first one multiplied by second one
<3> I see
<1> SELECT th.threadid, th.title
<1> FROM vbthread th
<1> WHERE th.forumid < 34
<1> AND th.threadid NOT IN (SELECT threadid FROM vbmoderatorlog)
<1> ORDER BY th.lastpost DESC LIMIT 10;
<3> wait
<1> if I take the AND line out it works, without filtering out those of course
<3> SELECT th.threadid, th.title
<3> FROM vbthread th
<3> left join (SELECT threadid FROM vbmoderatorlog) ml on th.threadid=ml.threadid
<3> WHERE th.forumid < 34
<3> AND ml.threadid IS NULL
<3> ORDER BY th.lastpost DESC LIMIT 10;
<3> this trick will help you :)
<3> means get all records from first table which are NULL when joined to second one (not presented)
<3> *left joined*
<1> hm error here as well
<3> ok, once again
<1> near 'SELECT threadid FROM vbmoderatorlog) ml on th.threadid=ml.threa...
<3> SELECT th.threadid, th.title
<3> FROM vbthread th
<3> left join vbmoderatorlog ml on th.threadid=ml.threadid
<3> WHERE th.forumid < 34
<3> AND ml.threadid IS NULL
<3> ORDER BY th.lastpost DESC LIMIT 10;
<3> try this :) seems that your DBMS does not support sub queries
<1> that did it
<3> good
<1> wth - MySQL supports no subqueries?
<1> I use them in Oracle all the time
<1> well not all the time
<3> as far as I remember it doesnt till version 4.1
<1> wow
<3> man
<3> oracle is a database
<3> MySQL is... something crappy, MyNotThatMuchSQL
<3> moreover, NOT IN is efficient only for small amount of checks in the list
<3> like where typeid not in (1,4,6)
<1> this seems pretty snappy
<3> but when you deal with two long tables, use left joins or EXISTS
<1> maybe 10,000 recs in vbpost
<1> err vbthread
<3> and vbmoderatorlog?
<3> if more than 10 - dont use NOT IN..
<3> means the check will be executed for each of 10,000 ten times
<3> so you have 100,000 I/O's
<3> and if you do join with proper indexing, it wont exceed 100 i/o's
<1> prob 150 recs
<1> grows tho (slowly)
<1> thanks so much
<1> problem solved
<3> :)
<3> you welcome
<1> http://www.ipodhacks.com/
<1> right block, From New Forums
<3> ;]
<1> I have a moderator that can only SOFT delete posts
<1> so his deleted posts were still showing
<1> no longer...
<1> :-)
<1> FR33 V14GRA !!!
<1> bla bla
<4> http://www.ghane.net/?s=gallery&dir=resistor_pr0n
<5> ok... i need to do a query and call for the last matched value on a column
<5> like a distinct but last
<2> sorted ?
<4> How many chars is IPv6 ?
<4> I need a field for it in a table
<4> not sure how many to reserve for it
<4> O.o
<2> ipv6 is what, 128 bits ?
<2> so, 16 bytes?
<4> hmm
<4> nvarchar 16 ?
<2> I'd go with char(16)
<4> what about those ":" inbetween ?
<2> myself
<4> those r counted in it too ?
<2> no, those are just a notation
<4> hm
<2> the actual address as a number is 128 bits
<4> how many of those are there ?
<2> I don't really know a lot about the string representations, though
<4> oh
<4> hmmm
<4> say, getting a user IP by ASP.NET
<4> it would probably be 1234:234:234:234
<4> something like that
<4> with the :
<4> I wonder how many of those it has
<4> hmm
<2> http://www.scit.wlv.ac.uk/rfc/rfc18xx/RFC1884.html
<4> thnx
<4> FF07:0:0:0:0:0:0:0
<4> :O
<4> 4x8 = 32 chars
<4> +
<4> 7 x ":"
<4> 39
<4> hmm hmm
<4> http://www.codeproject.com/cs/internet/ipv6.asp?df=100&forumid=14189&exp=0&select=902443
Return to
#sql
or
Go to some related
logs:
#chat-world
#allnitecafe
#vb
wireshark no-capture-interfaces
#php
#allnitecafe
#worldchat
melancap
secymama
#allnitecafe
|
|