| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
Comments:
<0> Oh it's in French lol
<1> hehe
<2> Da.
<2> I don't speak french. so I wouldn't really know. :)
<1> Xgc: well, it's actually spanish... mexican spanish
<1> hehe
<2> Renich: No, really?
<1> yeah, haha
<1> Xgc: http://pastebin.com/656872
<1> the only relation between "bancos_contactos" and "bancos" is bancos_contactos.id_banco
<2> Why did you paste that again?
<1> thought you didn't see it
<1> soryr
<2> I saw it. I don't speak french. I told you.
<2> and it's nearly unreadable.
<2> Renich: From the bank_id, find the region. With that region, find all banks with in the same region. From that list of banks, find the contact email addresses for those banks. It's simple. Just toss that into one query.
<1> yeah, how... haha
<1> let me see
<2> Renich: The query I showed is essentially the correct form, without the bank/contact relationship.
<1> trying to adapt it
<2> Renich: There's almost nothing left to do, except maybe join with the contact table.
<3> version
<3> is there mysql for old computer
<3> optimize version for pentium 3 pc
<2> Renich: SELECT contacts.emails FROM banks JOIN (SELECT region FROM banks WHERE bank_id=5) v1 ON banks.region=v1.region JOIN contacts ON banks.contact_id=contacts.id;
<2> Renich: If there's a chance you don't have a contact for some banks, use a left join.
<1> ok
<2> Renich: You contact table has a bank id. So adjust the join criteria to account for that.
<1> what does v1 stand for?
<4> Is there a way to accomplish the reverse of concat, and actually construct multiple rows from a single seperated list?
<2> Renich: It's the alias for the derived table.
<1> ok
<2> JOIN contacts ON banks.id=contacts.bank_id
<2> That's the adjusted JOIN criteria for contacts.
<5> all sorts JOINs flyin around
<1> Xgc: what do you think of this? SELECT bancos_contactos.e_mail FROM bancos_contactos JOIN (SELECT region FROM estados WHERE bancos.id_estado = '1') v1 ON bancos.id_estado = v1.id JOIN bancos ON bancos.id = bancos_contactos.id_banco;
<1> I get an error
<3> to me i prefer mutiply select * bank,c where bank.bankId=c.bankId rather then join
<2> Renich: You keep using those strange names.
<1> lol
<1> sorry
<1> gonna translate for you
<2> Renich: ANy particular error?
<2> Renich: What version of MySQL is this
<1> check the WHERE clause
<2> Renich: You can't do that.
<1> SELECT banks_contacts.e_mail FROM banks_contacts JOIN (SELECT region FROM states WHERE banks.id_state = '1') v1 ON banks.id_state = v1.id JOIN banks ON banks.id = banks_contacts.id_bank;
<1> yeah, I know
<1> the thing is that I don't have the id of the bank in the region table
<1> but I have the id of the region in the bank table
<2> Renich: You never said anything about states, only regions.
<1> yeah, sorry
<1> the thing is that I have 3 tables: contacts, banks and states
<1> banks haves the state id in it and contacts haves the bank id in it... so they can link
<1> I am sorry for the confusion
<3> select * from `contact`,`banks`,`state` where `bank`.`contactId`=`contact`.`contactId` and `bank`.`stateId`=`state`.`stateId` and `contact`.`stateId`=`state`.`stateId`
<2> Renich: Start slowly. Given a bank, find the state. Given that state, find the region. Given that region, find all states in the region. Given that list of states, find the list of all banks in those states. Given those banks, find the list of all contacts.
<6> I get invalid syntax for this line -> $result = mysql_query("SELECT email, city, country, desc FROM User WHERE user='{$user}';"); ..any idea?
<2> Renich: I pretty much gave you the answer, minus the relationship you didn't mention.
<1> Yeah, it sound like it, but I don't know how to get the region... that's my problem
<1> in fact, i copied what you just said.. hehe
<2> Renich: That can't be. You don't know how to find the state of the given bank?
<1> no, I don't know how to find the region of a given state
<2> Renich: Why not?
<1> the state and the region are in the same table
<2> SELECT ... FROM state t1 JOIN state t2 ON t1.region_id=t2.region_id and t1.state_id=1
<2> Renich: t2.* is the list of matching states.
<1> i got stuck here: http://pastebin.com/656881
<2> Renich: Now you just need to replace that constant 1 with the result from the subselect to find the state of a given bank.
<2> Renich: It's another simple join.
<1> so, I replace... what constant?
<1> '1'?
<2> But you can generalize and have that query return all states in all regions and limit that by joining with a specific state.region_id.
<1> man, I wish I knew as much as you! You already found 3 solutions!
<2> Sorry... all states in each region.
<1> hehe
<2> Renich: It's interesting to be able to almost speak/type/think the solution as you're typing the question. When you first start with SQL, it's quite a chore to twist your mind around problems using SQL terms.
<1> yeah, tell me about it...
<3> first renich proper design of the table first
<1> yeah, hehe... don't tell me I gotta change it now... ?
<2> THere are almost always design issues your first few years using SQL.
<3> hehe
<1> haha
<7> hi guys, i have an INT field that represents a unix time stamp, i've create a new field of type timestamp, how can i update my table to use the time of my timestamp in my INT field ?
<3> it might seen easy to create but when you related to business logic one change might change all the table
<1> I think i'm blocked... can't figure out what to do next
<1> this is as far as I could get http://pastebin.com/656920
<1> correction: http://pastebin.com/656924
<5> lepine
<5> u still around?
<3> okay renich
<1> yes?
<3> don't use all id on all auto increment table
<3> you make youself confuse on that
<1> ??
<3> like this
<3> SELECT
<3> `bancos_contactos`.`nombre`,
<3> `bancos_contactos`.`e_mail`,
<3> `estados.estado`,`estados`.`region` as `region_select`
<3> FROM
<3> `bancos_contactos`,
<3> `bancos`,
<3> `estados`
<3> where
<3> `bancos`.`bancosid` = `bancos_contactos`.`bancoid`
<3> and
<2> Renich: You shouldn't use that subselect like that.
<3> `estados`.`estadosid` = `bancos`.`estadosid` and
<3> and
<3> `bancos`.`bancosid` = '1';
<2> alien3d: Don't flood.
<3> soory
<3> forget
<1> hehe
<1> was hard keeping on
<1> Xgc: so, how should I use the subselect?
<2> Renich: You should stick to JOINs.
<1> ok, make the subselects on the joins?
<2> Renich: You don't need a WHERE clause in the outer query here at all.
<1> ok
<3> hehe if you want play join use this sofware http://www.navicat.com/
<3> i rather use mystyle
<2> Renich: Correlated subqueries, while they might look nice, can perform poorly. The one you tried is actually invalid.
<1> yeah, just was trying to explain
<7> killian14085, yes, im still here
<1> !!
<1> Got it working
<1> http://pastebin.com/656956
<1> can anybody consider on checking it and telling me how to do it rignt? It works fine, but, Xgc, you said I should not do that...
<1> in any case, which is the better way?
<2> Renich: If it works for you, leave it alone.
<1> Xgc: if nobody says anything, I will... but, do me one favor, could you give me some pointers on the manual so I can correct this in the future?
<1> I really wanna know how to make queries fast, smart and easy
<8> indexes
<8> :p
<1> hehe
<9> memory
<2> Renich: In this case, you can leave it alone. The subquery only returns a single value and is not dependent on anything outside the subquery.
<2> Renich: The engine should handle that reasonably.
<1> ok
<1> gotta get the hang of the joins...
<1> thanks a lot for the help, i mean, really, i don't know how you guys can stand newbies like me... haha
<9> Jack Daniels
<9> there's a key difference that you have to be aware of
<9> newbies are *not* bad
<9> newbies are fantastic, because it's one more person that we can help, one more person that uses our favourite database
Return to
#mysql
or
Go to some related
logs:
ubuntu tgetent
liquidglass screenshot
redhat-9 rpm-repository
#lisp
#web
#suse
absolute convergent
#suse
xchat gethostbyname fails
#ubuntu
|
|