| |
| |
| |
|
Page: 1 2
Comments:
<0> ???
<0> emanuel__: you need to read the online documentation for mysql
<0> http://dev.mysql.com/doc/refman/5.0/en/index.html
<1> i am trying to do this:
<1> SELECT * FROM projects INNER JOIN (SELECT * FROM keywords INNER JOIN keywords_join ON keywords.id = keywords_join.kid) WHERE keywords_join.pid = projects.id
<1> but you cant inner join an inner join lol.
<1> any other ways? :)
<2> hi
<3> what are you trying to do? in words, not bad SQL?
<2> teach me about sql injection
<1> haha okay fuiro...
<1> i am trying to allow a user to execute a search
<1> they can search using keywords ie. 'boat', 'book' or 'car'
<1> these keywords are in a table with id's.
<1> now, the results that should come up are stored in a 'products' table. they consist of the products that come up based on pre-stored keywords.
<1> so based on certain keywords...i wanna be able to say: "from the keywords entered, show all relevant products"
<3> was going more for, "I have X tables... Table1 is related to Table2 in such a way..." etc
<1> hehe sorry. here's THAT version :P ....
<1> there is 'tableA' 'tableB' & 'tableC'
<1> 'tableC' joins tables 'tableA' and 'tableB'
<1> based on keywords in 'tableB' -> i want to be able to grab all matching 'tableA' records in tableC
<3> sounds like Select * from products p inner join keywords_join j on p.id = j.pid inner join keywords k on k.id = j.kid where k.keyword = 'whatever'
<0> it is
<1> oooo i never knew you can inner join inner joins :)
<0> why not?
<1> good answer :)
<1> hehe thanks mate
<1> lets say now, there is more than one keyword entered...
<1> say: 'book boat car'
<1> of course, id split them into an array...
<1> but would a simple OR work? ie. k.keyword = 'boat' OR k.keyword = 'car'
<1> question fuiro... or jackfix...
<1> with inner joins...can you use ORDER BY at the end?
<1> ie.
<1> SELECT * FROM projects INNER JOIN keywords_join ON projects.id = keywords_join.pid INNER JOIN keywords ON keywords.id = keywords_join.kid WHERE keywords.keyword = 'keyword1' keywords.keyword = 'keyword2' ORDER BY keywords.id ASC
<1> i need to eliminate redundant recordsets using code. only, i need to sort the result recordsets in order to do so :)
<3> try it and see
<1> oh sweet! :)
<4> hi all
<4> anyone atk?
<0> for what?
<4> ah sweet
<4> i've just been wondering
<4> if you have some sort of a log in an ms sql db table
<0> huh??
<0> a 'log' of what?
<4> not you, a hypothetical "you" (=
<0> a 'log' of what?
<0> I UNDERSTAND your pseudo-question
<0> I want to know what you're attempting to refer to
<4> so if there's a db table, which consists of 3 fields: id, datetime and status
<4> and it's updated every time an object changes it's status with the object's id, the datetime when the change occured, and the new status
<4> and by "updating" I mean a new row is added
<0> that's not updating
<0> that's inerting
<0> and what ARE you babbling on about??
<4> well yes
<4> so the question is
<0> finally???
<4> how do I find out the id's of all the objects with a particular current state?
<0> you learn SQL
<0> you understand how it processes records and data and you write the appropriate query
<0> but I suppose you want someone to actually WRITE this query for you
<4> I mean I obviously can do it by just doing a "select status from table where id=particular_id order by datetime" and just check the top returned row
<0> HUH???
<4> no I don't want anyone to write it for me
<4> I want to know what's the best concept
<0> that query doesn't even approach the topic you just presented
<0> [09:30] <4> how do I find out the id's of all the objects with a particular current state? <=== your question
<4> yep
<0> [09:31] <4> I mean I obviously can do it by just doing a "select status from table where id=particular_id order by datetime" and just check the top returned row <=== your proposed 'solution' ... which is wrong
<4> I can check all the id's one by one
<4> obviously inefficient
<0> WRONG
<0> like I said, LEARN SQL
<0> learn how it operates
<0> you obviously think it's a procedural language
<0> and it is not
<4> I know it's not
<0> apparently you don't with the 'solution' you proposed to your 'problem'
<4> The solution will be in cpp code with ado used to query the db
<0> wrong
<0> the solution should be the result set of your SQL Statement
<0> not some ado crap or cpp bull****
<4> exactly
<0> you don't understand sql at all
<0> if you did you'd have your query by now
<0> and if you're trying to annoy me enough so I'll break down and do your work for you, well, you can think again
<0> [09:30] <4> how do I find out the id's of all the objects with a particular current state? <=== define 'particular current state'
<4> "state" is an int field
<0> so?
<0> how does that answer my question?
<4> and let's say there are 3 states, 1 2 & 3
<0> oh, lord, another long-winded diatribe
<4> so i want to get the id's that have the current state set to, say, 1
<0> so you WRITE that as a SQL Statement and you're done
<5> select ID from table1 where state = 1 ?
<0> gee, you CAN do it
<5> mm, "I" can
<4> that won't work here, as every state change is stored in the table
<0> excuse me?
<4> as i mentioned above, it's a sort of a "log"
<0> you do not UPDATE rows? you insert NEW rows for every data change??
<4> i don't do that
<4> that's what i have to deal with
<5> There is no field to say if its old or not?
<5> An d date field?
<5> any*
<0> there is
<4> there's a datetime field
<0> he's being deliberately obtuse
<5> Could select distinct based on ID, and sort by date, perhaps?
<0> why???
<4> that's what i intend to do
<4> but that looks kinda wrong
<5> Well, you did wrong from the start.
<4> must be a better way
<0> he wants all of the IDs with a particular state
<5> +where state = 1
<5> Yeah, but as I get it, he does not want the "log" part.
<0> he has that
<4> i only want the most recent change
<0> and he only needs to filter further on the datetime
<0> YOU decide what constitutes 'recent'
<5> Well, in any case, you have given him all he needs to know.
<5> www.w3schools.com/sql
<0> and do some datetime math on the datetime field
<4> but the state change doesn't occur on any set intervals, you see
<0> and include that in your where clause
<0> WHO CARES
<4> i can't just add "where datetime>smth"
<4> it's different for different id's
<4> as i have no idea when a particular object decided to change it's state
<0> select id from table1 where status = 1 and datetime >= <current date - some interval>
<5> You only want the latest change of a ID?
<4> yes
<0> select id from table1 where status = 1 and datetime >= <current date - some interval>
<5> Distinct ID, sort by date
<5> +where state=1
<0> select id from table1 where status = 1 and datetime >= <current date - some interval>
<4> jackfig, that would not work
<4> i have no idea what the interval is
<4> as one object could be changing it's state every second
<0> that's your problem
<0> then you can't query the data
<4> and other could have it the same since the 1985
<0> the changes are such you'll never get a reliable and repeatable result
<4> i do have a reliable result by querying each id separately, sorting it by datetime and checking the top row
<0> no, you don't
<4> i'm just asking if there is a more efficient way
Return to
#sql
or
Go to some related
logs:
chal bhag chale
angellyy
nicechatboy
/usr/lib/libc-client.so.0: undefined symbol: mm_dlog
#kl
#india
#india
#netcafe
#allnitecafe
#india
|
|