| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9
Comments:
<0> (select * from tbl2) mytable2
<0> or (select * from tbl2) as mytable2
<0> in the sql you just pasted
<1> oh kay....
<1> so do I do that at the start?
<1> just (select * from tbl2) as mytable2?
<0> at the start? provide an alias for the subselect...
<0> the subselect is the select between brackets
<1> nah didnt like that
<1> "syntax error in union"
<0> didn't think so... probably not possible
<0> did you try with and without the keyword "as"?
<1> ahh no
<1> same
<0> continue in the channel, Matty
<0> (no pm)
<1> yeah - i just didnt really wan to paste that link globally
<1> +t
<0> oh
<0> then ask first pls
<1> k
<0> do it again because I closed it :)
<1> haha!
<1> np
<1> I also hate it when ppl pm me ;)
<1> so thats with the "as" - I also tried without
<1> the syntax error is hilighting the "as"
<1> orthe "testtable1" when there is no"as"
<0> make a completely different set of tables with simple data and tables to test the syntax
<0> see if you can get it working
<0> so without the transform, sums, group by, pivot...
<0> as simple as possible
<1> yeah - thats kinda complicated I guess ;)
<0> actually you don't need tables
<0> just do:
<0> select 1 inner join (select 2) as b
<1> can you comment in sql?
<1> like "'" or "//"
<0> try "--"
<0> or /*comment*/
<0> (this is all t-sql, so again, I don't know if it'll work in access)
<1> nope :( burger
<0> burger?
<0> you mean bugger?
<1> yes. but burger is more polite, and is slightly taking the piss
<0> I see
<1> I have been in chans that kickban for rude words
<1> 12:56 < TorBeest> select 1 inner join (select 2) as b
<0> you can never overswear some admins on here ;)
<1> "as b"?
<0> yes, b is a name
<1> okay
<0> you can call it fizzlepop too
<1> I will now ;)
<0> <- gone for a bit
<1> okies
<1> **** YEAH!
<1> that worked!
<1> :D
<1> dude - you are a genius :D
<1> also - in the same way that "distinct" lists only distinct values, is there a select postfix that I can use to display records where all the fields!=""
<1> SicLuDe: mIRC muh?
<1> TorBeest: I called it wizzlepop by mistake. but its set in there now ;)
<2> MattyT: what does distinct have anything to do with field != "" ?
<1> it doesnt
<2> so y the hell did u mention distinct?
<1> thats why i said in the same way that "distinct" lists only distinct
<1> values, is there a select postfix that I can use to display
<1> records where all the fields!=""
<1> so... In the same way as distinct
<2> if you say so
<1> I did say so
<2> did u try : where field != ""
<2> ?
<3> That really makes no sense.
<1> yeah
<2> and?
<1> becase I am using a left join, some of the records in the left table create a blank record
<1> I do want all the records in the left table, and only those that match in the right table
<2> if you are using left join, the records in the RIGHT table would come in as null not left
<1> but I am also pivoting the table, which means that I always get a record where all fields==""
<2> um.. so u want say 20 records in the left and 10 in the righ?
<1> =which is annoying becuase its a query behind a report
<1> and that menas that I always get a blank page in the report
<1> SicLuDe: something like that... yes
<2> dude that doesnt make sense
<2> when you join, you get a resultset...
<1> it wouldnt, unless I explained the exact table
<1> but it is why outer joins were created
<2> no just overall conceptually it doesnt make sense
<1> ok. (I am not impressed about havign to do this)
<1> imagine the uk.
<1> its split into "post code regions"
<2> u cant have one resultset wtih 10 rows in the first 5 columns and then 20 in the next 5
<1> if you have ever seen a uk post code, you can tell the post code region by taking the first 2 letters
<1> SicLuDe: sush , you will not understand until I have explained
<2> is this a similar analogy to distinct and field != "" ? where its the "same way" but one has nothign to do with another?
<1> so - knowing that post code areas exist, I can now tell you that there are 125 post code regions in the uk, including northern ireland
<1> if you then imagine a databse of customer ids, you can group your customers into their post code regions
<1> got it?
<2> ok?
<1> HOWEVER, if there are NO customers in a region, then there are no enteries in the resultset for that postcode region... right?
<2> ok?
<1> ok - so imagine a case where you want to know a count of all the customers, grouped by region
<2> so you want a list of only the post code regions that have something in them?
<2> select left(postcode, 2), count(*) from sometable group by left(postcode, 2)
<1> no. I want to have a list of ALL the post code regions with a count of the customers in them, but if there are no customers, then still display the region
<1> ie LEFT JOIN
<4> don't need a left join for that
<1> (where the left table is a tbale of regions)
<1> dfworking: dont "need" or wont work with a left join?
<2> matty: see my query above
<1> SicLuDe: will only show the regions where count>0
<2> right
<2> and what are u supposed to show if they dont exist?
<1> I want ALL the regions
<1> nothing. thats ... why ... I ... use ... a ... left ... join.
<4> what tables you have?
<2> select left(b.postcode, 2), (select count(*) from customertable a where left(a.postcode, 2) = left(b.postcode, 2)) from postcodetable b group by left(b.postcode, 2)
<1> SicLuDe: still not thinking of the big picture. What is there is a region with no customers in it
<1> ?
<2> dude
<2> go try this
<4> if you have two tables then you'll either need the left join as you said or a subselect
<1> now if you pivot the table so that the regions become the column heading, and the values are the sum of the customers....
<1> SicLuDe: now imagine a report with 125 text fields printed onto a map of the uk. if you generate the report, and one fo the regions has no customers in it, then there will be no field in the resultset to put into the report.
<1> df...precisely
<2> MattyT: i dotn know waht your report consist of and i dont know waht the 125 text fields are so its kinda hard to imagine it
<1> now imagine that in the pivot table, there is a group of customers with NO customers in all regions, meaning that in the report you get a blank page
<2> did you try the query out?
<1> SicLuDe: yeah
<2> and you dont get any wtih count = 0?
<1> it works perfectly, with the execption that there is nearly always a blank page
<1> SicLuDe: no
<2> i dont know about that...
<2> you tried out:
<2> select left(b.postcode, 2), (select count(*) from customertable a where left(a.postcode, 2) = left(b.postcode, 2)) from postcodetable b group by left(b.postcode, 2)
<2> ?
<2> do you have a table of postcodes?
<2> and how can you have 125 postcode regions if the first 2 digits determine the postcode region?
<1> no. a table of regions. where regions.pcreion = left(cust.postcode,2) (or similar)
<1> s/pcreion/pcregion/
<2> so what does ur query look like?
<5> hi!
<5> is there any reason for why <null> is not the same as [empty]
<1> http://sql-servers.com/nopaste/?show=273
<4> ionix, yes, one is a value, one is the absence of a value
<2> ok you are doing a left join.. that is not what i pasted
<2> your query is very ugly for me to go in and actually try to debug what you are doing
Return to
#sql
or
Go to some related
logs:
your date does not match internet time
#cisco
windows xp danish serial
#nhl
#heroin
#unixhelp
#windows
#sendmail
#stocks
postyourgirls
|
|