|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Need Help With a Simple QueryHey thre I am trying to write a query that actually works. My goal is
to only get those records that are unique to the CHIDspec.'Lbx ID' field. I am getting a syntax error in the following query: SELECT DISTINCT CHIDspec.`Lbx ID` AND SELECT CHIDspec.`Lbx ID`, CHIDspec.File, CHIUKReport.`CD Encrypt` FROM {oj `G:\Subgroup\Random Jobs\Cory\tEST`.CHIDspec CHIDspec LEFT OUTER JOIN `G:\Subgroup\Random Jobs\Cory\tEST`.CHIUKReport CHIUKReport ON CHIDspec.`Lbx ID` = CHIUKReport.File} Can someone rewrite this query so that I can pull only those distinct records based on the LBX ID, and show the LBXID, FILE and CD ENCRYPT fields in my table? Thanks Why are you using ` and { ? Where did you find SELECT ... AND ... SELECT
syntax? Are you sure this is for SQL Server? You can't just query disk-based files by injecting them as table names. Do you have actual tables in your database, or are you really attempting to use SQL Server to query against files in the filesystem? If the latter, can you provide a little more information about hte files (format, contents, etc) and if the latter, can you provide proper specs, sample data, and desired results (see http://www.aspfaq.com/5006)? <cstr***@yahoo.com> wrote in message Show quote news:1156436700.903196.178930@b28g2000cwb.googlegroups.com... > Hey thre I am trying to write a query that actually works. My goal is > to only get those records that are unique to the CHIDspec.'Lbx ID' > field. I am getting a syntax error in the following query: > > SELECT DISTINCT CHIDspec.`Lbx ID` AND SELECT CHIDspec.`Lbx ID`, > CHIDspec.File, CHIUKReport.`CD Encrypt` > FROM {oj `G:\Subgroup\Random Jobs\Cory\tEST`.CHIDspec CHIDspec LEFT > OUTER JOIN `G:\Subgroup\Random Jobs\Cory\tEST`.CHIUKReport CHIUKReport > ON CHIDspec.`Lbx ID` = CHIUKReport.File} > > Can someone rewrite this query so that I can pull only those distinct > records based on the LBX ID, and show the LBXID, FILE and CD ENCRYPT > fields in my table? > > Thanks > I am not using SQL Server, but MS Access and am trying to do a simple
query. I created the Select and select..because I really don't have any idea how to do what I'm trying to do..It was more of a test than anything, and it didnt work (I don't really work with SQL that much except for Microsoft Query Editor in MS Excel) Here is a better explanation. I have two Tables. For this example Table 1 and Table 2. Table 1 Field "file" is joined to Table 2 Field "ID" This is a left outer join where All values in Table 1 and only records from Table 2 where File = ID I am pulling the following Fields for the report that Im creating from this query 1) Table1.'file' 2) Table1.'LBX' 3) Table2.'Encrypt' I want to show all of these fileds and their data but I only want to show unique (Distinct)Table1.'file' records Can you help me with the syntax for this? This is what I had so far with the actual table names and field names SELECT DISTINCT CHIDspec.`Lbx ID` CHIDspec.`Lbx ID`, > > CHIDspec.File, CHIUKReport.`CD Encrypt` Thanks> > FROM {oj `G:\Subgroup\Random Jobs\Cory\tEST`.CHIDspec CHIDspec LEFT > > OUTER JOIN `G:\Subgroup\Random Jobs\Cory\tEST`.CHIUKReport CHIUKReport > > ON CHIDspec.`Lbx ID` = CHIUKReport.File} Cory Aaron Bertrand [SQL Server MVP] wrote: Show quote > Why are you using ` and { ? Where did you find SELECT ... AND ... SELECT > syntax? Are you sure this is for SQL Server? You can't just query > disk-based files by injecting them as table names. Do you have actual > tables in your database, or are you really attempting to use SQL Server to > query against files in the filesystem? If the latter, can you provide a > little more information about hte files (format, contents, etc) and if the > latter, can you provide proper specs, sample data, and desired results (see > http://www.aspfaq.com/5006)? > > > > > > <cstr***@yahoo.com> wrote in message > news:1156436700.903196.178930@b28g2000cwb.googlegroups.com... > > Hey thre I am trying to write a query that actually works. My goal is > > to only get those records that are unique to the CHIDspec.'Lbx ID' > > field. I am getting a syntax error in the following query: > > > > SELECT DISTINCT CHIDspec.`Lbx ID` CHIDspec.`Lbx ID`, > > CHIDspec.File, CHIUKReport.`CD Encrypt` > > FROM {oj `G:\Subgroup\Random Jobs\Cory\tEST`.CHIDspec CHIDspec LEFT > > OUTER JOIN `G:\Subgroup\Random Jobs\Cory\tEST`.CHIUKReport CHIUKReport > > ON CHIDspec.`Lbx ID` = CHIUKReport.File} > > > > Can someone rewrite this query so that I can pull only those distinct > > records based on the LBX ID, and show the LBXID, FILE and CD ENCRYPT > > fields in my table? > > > > Thanks > > First, a couple of lessons in SQL will help make sense of things...
http://www.w3schools.com/sql/sql_intro.asp http://sqlzoo.net/ Next, as Aaron pointed out, you don't have any tables here, you are referencing files. Third, this is not an access group but a SQL Server group. You will do much better if you post to the correct group. As to your question, lets start with something basic as an example. Select [Table1].[Lbx id], [Table2].[Encrypt] from Table1 LEFT outer join table2 on [Table1].[Lbx id] = [Table2].[file] -- note the [brackets] aroound the column names and table names, not quotes. -- note there are no {} used anywhere, and tables are referenced by name, we do not deal with files directly if you are using access, you probably should not be using the SQL editor anyway. Just use the query designer GUI and let the application handle the sql in the background, unless you actually want to learn SQL enough to troubleshoot it. <cstr***@yahoo.com> wrote in message Show quote news:1156438384.945806.21690@b28g2000cwb.googlegroups.com... > I am not using SQL Server, but MS Access and am trying to do a simple > query. > > I created the Select and select..because I really don't have any idea > how to do what I'm trying to do..It was more of a test than anything, > and it didnt work (I don't really work with SQL that much except for > Microsoft Query Editor in MS Excel) > > Here is a better explanation. I have two Tables. For this example > Table 1 and Table 2. > > Table 1 Field "file" is joined to Table 2 Field "ID" This is a left > outer join where All values in Table 1 and only records from Table 2 > where File = ID > > I am pulling the following Fields for the report that Im creating from > this query > > 1) Table1.'file' > 2) Table1.'LBX' > 3) Table2.'Encrypt' > > I want to show all of these fileds and their data but I only want to > show unique (Distinct)Table1.'file' records > > Can you help me with the syntax for this? > > This is what I had so far with the actual table names and field names > SELECT DISTINCT CHIDspec.`Lbx ID` CHIDspec.`Lbx ID`, > > > CHIDspec.File, CHIUKReport.`CD Encrypt` > > > FROM {oj `G:\Subgroup\Random Jobs\Cory\tEST`.CHIDspec CHIDspec LEFT > > > OUTER JOIN `G:\Subgroup\Random Jobs\Cory\tEST`.CHIUKReport CHIUKReport > > > ON CHIDspec.`Lbx ID` = CHIUKReport.File} > > > Thanks > > Cory > Aaron Bertrand [SQL Server MVP] wrote: > > Why are you using ` and { ? Where did you find SELECT ... AND ... SELECT > > syntax? Are you sure this is for SQL Server? You can't just query > > disk-based files by injecting them as table names. Do you have actual > > tables in your database, or are you really attempting to use SQL Server to > > query against files in the filesystem? If the latter, can you provide a > > little more information about hte files (format, contents, etc) and if the > > latter, can you provide proper specs, sample data, and desired results (see > > http://www.aspfaq.com/5006)? > > > > > > > > > > > > <cstr***@yahoo.com> wrote in message > > news:1156436700.903196.178930@b28g2000cwb.googlegroups.com... > > > Hey thre I am trying to write a query that actually works. My goal is > > > to only get those records that are unique to the CHIDspec.'Lbx ID' > > > field. I am getting a syntax error in the following query: > > > > > > SELECT DISTINCT CHIDspec.`Lbx ID` CHIDspec.`Lbx ID`, > > > CHIDspec.File, CHIUKReport.`CD Encrypt` > > > FROM {oj `G:\Subgroup\Random Jobs\Cory\tEST`.CHIDspec CHIDspec LEFT > > > OUTER JOIN `G:\Subgroup\Random Jobs\Cory\tEST`.CHIUKReport CHIUKReport > > > ON CHIDspec.`Lbx ID` = CHIUKReport.File} > > > > > > Can someone rewrite this query so that I can pull only those distinct > > > records based on the LBX ID, and show the LBXID, FILE and CD ENCRYPT > > > fields in my table? > > > > > > Thanks > > > > Hopefully I didnt make that too complicated, but If i did, here is the
simplest way to explain it Here is a basic query that works: SELECT CHIDspec.File, CHIDspec.`Lbx ID`, CHIUKReport.`CD Encrypt` FROM {oj `G:\tEST`.CHIDspec CHIDspec LEFT OUTER JOIN `G:tEST`.CHIUKReport CHIUKReport ON CHIDspec.`Lbx ID` = CHIUKReport.File} I need to pull distinct data from CHIDspec.`Lbx ID`, and I'm not sure how to work in the "distinct" command for this query Thanks cstr***@yahoo.com wrote: Show quote > Hey thre I am trying to write a query that actually works. My goal is > to only get those records that are unique to the CHIDspec.'Lbx ID' > field. I am getting a syntax error in the following query: > > SELECT DISTINCT CHIDspec.`Lbx ID` AND SELECT CHIDspec.`Lbx ID`, > CHIDspec.File, CHIUKReport.`CD Encrypt` > FROM {oj `G:\Subgroup\Random Jobs\Cory\tEST`.CHIDspec CHIDspec LEFT > OUTER JOIN `G:\Subgroup\Random Jobs\Cory\tEST`.CHIUKReport CHIUKReport > ON CHIDspec.`Lbx ID` = CHIUKReport.File} > > Can someone rewrite this query so that I can pull only those distinct > records based on the LBX ID, and show the LBXID, FILE and CD ENCRYPT > fields in my table? > > Thanks > I need to pull distinct data from CHIDspec.`Lbx ID`, and I'm not sure You need to provide more information. If you have this data:> how to work in the "distinct" command for this query Lbx ID File CD Encrypt 1 foo x 1 bar y 1 blat z 2 splunge a 2 brap b 3 retch c What does your result set look like? Just saying "Lbx ID needs to be distinct" does not provide enough information about what data you are actually after. A Hey guys...Thanks you are right, I should have gone to either the MS
access group or the Excel group. Sorry about that. I did figure out what I needed to do in Excel itself. (unique record filetering) Thanks for all of your help :) Cory Aaron Bertrand [SQL Server MVP] wrote: Show quote > > I need to pull distinct data from CHIDspec.`Lbx ID`, and I'm not sure > > how to work in the "distinct" command for this query > > You need to provide more information. If you have this data: > > Lbx ID File CD Encrypt > 1 foo x > 1 bar y > 1 blat z > 2 splunge a > 2 brap b > 3 retch c > > What does your result set look like? Just saying "Lbx ID needs to be > distinct" does not provide enough information about what data you are > actually after. > > A is G:tEST the name of a table in your database? Or are you accessing a
file? Whhat are the {} for? what is oj? <cstr***@yahoo.com> wrote in message Show quote news:1156438861.335351.147090@75g2000cwc.googlegroups.com... > Hopefully I didnt make that too complicated, but If i did, here is the > simplest way to explain it > > Here is a basic query that works: > > SELECT CHIDspec.File, CHIDspec.`Lbx ID`, CHIUKReport.`CD Encrypt` > FROM {oj `G:\tEST`.CHIDspec CHIDspec LEFT OUTER JOIN > `G:tEST`.CHIUKReport CHIUKReport ON CHIDspec.`Lbx ID` = > CHIUKReport.File} > > > I need to pull distinct data from CHIDspec.`Lbx ID`, and I'm not sure > how to work in the "distinct" command for this query > > Thanks > cstr***@yahoo.com wrote: > > Hey thre I am trying to write a query that actually works. My goal is > > to only get those records that are unique to the CHIDspec.'Lbx ID' > > field. I am getting a syntax error in the following query: > > > > SELECT DISTINCT CHIDspec.`Lbx ID` AND SELECT CHIDspec.`Lbx ID`, > > CHIDspec.File, CHIUKReport.`CD Encrypt` > > FROM {oj `G:\Subgroup\Random Jobs\Cory\tEST`.CHIDspec CHIDspec LEFT > > OUTER JOIN `G:\Subgroup\Random Jobs\Cory\tEST`.CHIUKReport CHIUKReport > > ON CHIDspec.`Lbx ID` = CHIUKReport.File} > > > > Can someone rewrite this query so that I can pull only those distinct > > records based on the LBX ID, and show the LBXID, FILE and CD ENCRYPT > > fields in my table? > > > > Thanks >  |
|||||||||||||||||||||||
Other interesting topics