| |
| |
| |
|
Page: 1 2 3 4 5
Comments:
<0> Hi guys, I have a question regarding JOIN
<1> awesome, thanks for letting us know
<0> =)
<0> is it possible to join 4 tables the following way: table A connects to table D through table B and table A connects to table D through table C. To connect table D to table A, both connections have to be done to make unique connection between A and D. Is it possible to make it with JOINS in one query?
<0> or JOINs allow only serial connections, not parallel like in my problem?
<1> if how I read that makes sense I say yes
<0> can you please show me an example ?
<0> or I can paint a scheme to you to make it easier to understand and imagine
<1> or paste a query at the topic site
<2> A INNER JOIN B ON A.a1 = B.b1 INNER JOIN C ON A.a2 = C.c2 INNER JOIN D ON B.b2 = D.d1 AND C.c2 = D.d2
<0> hm
<0> let me try...
<3> is there any way to call dateadd() and p*** in a parameter for the 'datepart' parameter?
<3> ie ... dateadd(@datepart, @frequency, @datestamp)
<3> rather then ... dateadd(d, @frequency, @datestamp)
<4> if not, then you could do it with CASE :P
<5> zoul : which rdbms?
<3> sql2k
<5> nope
<3> upb: I wanted to prevent using a case ...
<5> er...
<5> hm
<5> maybe
<3> basically, I'm qwriting T-SQL to replace the SQL Agent Scheduler ...
<3> so I have to be able to create dates based on some prety funky prereqs ...
<3> I have a schema to store all these prereqs ... and a set of rules to go on ... as I reuse different columns for different types of schedules ...
<5> you could always create dynamic sql to do it
<3> monthly schedules, daily schedules and weekly schedules ... all use the same columns, but differently
<3> GuntherX: this code will be executed on large result sets quite frequently ... dynamic sql is something I'd like to steer clear of ...
<5> iirc, you can do "declare @i int; exec ("set @i = blah()")
<3> I'd rather the optimizer know what is really going to happy
<6> in mssql, how can i select only the top rows where sum(column X) is < Y ?
<3> GuntherX: you can ...
<5> zoul : then do it that way
<0> back to my join question...
<0> http://sql-servers.com/nopaste/?show=189
<3> seelct top 10 * from tbl having sum(x) < y
<0> and I am using access
<5> tenfour : what do you mean by 'top rows' and 'handle'?
<3> GuntherX: thats fairly ... performance draining
<0> that query has an error inside...
<0> I can't understand what's wrong
<5> zoul : performance wasn't mentioned when you asked the question
<3> MozgC: we ***ume so ... wanna tell us the error?
<3> GuntherX: performance is always an issue when I ask a question ...
<3> I know how to write bad code ... heh ;)
<5> zoul : just because it *might* be slow, doesn't mean it's bad
<0> Syntax error (missing operator) in query expression '(parts.Customer = Customers.Name) LEFT OUTER JOIN SupInvoices ON (Parts.SupInvoicesID = SupInvoices.ID) LEFT OUTER JOIN CustPercents ON (SupInvoices.SupplierID = CustPercents.SupplierID AND Customers.ID = CustPercents.CustomerID)'.
<3> it's done by cutting corners, like you just suggested ... which was a good suggestion, but it affects performance ...
<7> any built in vars in SQL2k to indicate the rowcount within the results?
<3> EyePulp: @@rowcount
<3> lol
<5> MozgC : don't paste **** like that into the channel - if it refers to the query on the opaste site, then paste it into the paste site too
<0> ok
<7> within the results set though?
<5> zoul : then you need case
<7> not at the end
<0> http://sql-servers.com/nopaste/?show=190
<0> here...
<6> hmm i mean if i have a table T with int column [A] ... i have 4 rows with values 100, 200, 300, 400. i want to select * from T -- but limit the results by 300, so it would only return 100 & 200. if i limit it by 600, it would return 100, 200, 300
<6> like TOP x but where x is not just a count, it's a sum
<7> or is there some sort of incrementor value?
<5> tenfour : no trivial way to do that - you'd need to play with temp tables
<6> k
<6> thanks
<5> EyePulp : no
<3> GuntherX: heh, if I could use a var and a lookup table ... this'd be like 10 lines of T-SQL ...
<3> with a case, I'm gonna be writing almost over 500+
<6> i think i could rig it up with cube or rollup but i have to walk the table in client code anyway so i'll just do it there
<3> but I know ... what I gotta do ... is what I gotta do ...
<5> yup
<5> could always put the case inside a function... or a sproc of it's own
<3> bbl
<0> Can anyone help?
<0> http://sql-servers.com/nopaste/?show=191
<6> i can help.
<6> the problem is... join expression not supported
<0> is it a correct query?
<6> clearly not =)
<0> ?
<7> if I'm doing a m*** insert from a query, and the table I'm insterting into has an auto-incrementing identity column, how do I make it do the ID incrementing while performing the insert? I'm creating new rows that need a new ID ***igned
<7> I also used SET IDENTITY_INSERT someTable ON
<7> ah, but I probably want that off?
<7> aha
<8> does anyone know how to export diagrams so i can import them onto another sql server?
<9> Can anyone tell me how I would dispay all fields for records where one particular field is DISTINCT?
<9> So, for example, what this kind of looks like it's means to do; "SELECT * FROM Table1 WHERE Col1 IS DISTINCT"
<10> Is it possible to perform the opposite of a JOIN function, meaning return the exact oppsite result of what a specific INNER JOIN function would return
<10> I know I can do this with several commands but is it possible to do in one commanfd
<10> command*
<11> give a better example
<11> change your join clause
<11> and you get the opposite
<11> it's simple set theory
<10> more specifically, i have one table with two fields id and name, and another table with id, userID, and nameID (which corresponds to the id of the first table
<10> i want to find all the rows that match a specific userID with the inverse of nameID's\
<10> that correspond to the rows in the first table
<4> what do you mean by 'with the inverse of '
<10> so say i do an inner join matching all the nameID's to a userID with the id of the first table
<10> i want to find all the rows in the first table that dont have a corresponding userID in the second table
<4> heh
<10> is something like that possible in one command
<4> SELECT a.* FROM a LEFT OUTER JOIN b ON a.id = b.nameID WHERE b.nameID IS NULL
<10> hey....this isnt sql homework..i take offense...lol
<4> btw i dont understand your schema, how can you match userID's to nameID's
<4> they're in the same table, or does the second table really reference itself?
<11> what exactly are you trying to do? find all keys in the second table that are not referenced from the first?
<4> < volkan> so say i do an inner join matching all the nameID's to a userID with the id of the first table
<4> i think this needs a ON nameID match to userID which HAS A FUZZY REFENCSE TO firsttable.id
<11> select * from a except select a.* from a, b where a.id = b.id
<4> heh, that does the same thing as mine
<4> atleast i think so, never used except
<11> but is yours standard sql? I'm not familiar with it
<4> sure
<11> doc ref anywhere?
<4> LEFT OUTER join syntax was added to the SQL-92 standard specifically to address certain queries that had only been possible with NOT IN subqueries
<4> random quote from google
<11> right, but where's the fuzzy reference bit
<4> lol
<4> < upb> SELECT a.* FROM a LEFT OUTER JOIN b ON a.id = b.nameID WHERE b.nameID IS NULL
<4> i meant this
<11> oh, right
<11> that query will never work
<4> why not ?:P
<11> because you're joining on b.nameID = a.id and you're then filtering out b.nameID != null
<4> it has worked ok for me many times
<11> resulting in nothing
<4> huh?
<11> oh, outer join
<4> yea
<11> I suppose that returns results not matched in the on clause
<11> I forget about that
<12> I'm using sql server 2000... how would I say, "select items NOT in a specific table"?
<13> be more specific
<13> how can you select items that arent in a table?
<13> :P
<13> you need a 2nd table
<14> NOT IN or NOT EXISTS
<12> I'm selecting items from table1 that are not in table2...
<12> sorry for not being specific
<13> do a left outer join
<13> left or right outer join depending how you word it
<12> I have table A and Table B, these two are related in Table AB, I'm trying to output all items in Table B that are not found in Table AB
<11> select * from b left join ab on b.id = ab.id and ab.id is null
<11> ***uming you relate the two using id
<14> where
<4> hehe that wont work :P
<14> select * from b left join ab on b.id = ab.id where ab.id is null
<11> er, s/and/where
Return to
#sql
or
Go to some related
logs:
icohate
bill gates refrigerator
i work for cisco
#delphi
#visualbasic
#hardware
#politics
#nhl
run swiftmp3 from command line
what does checking nvram mean
|
|