SQL Query - Unique SELECT CONTAINS-VBForums
Results 1 to 3 of 3

Thread: SQL Query - Unique SELECT CONTAINS

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 2010
    Posts
    44

    SQL Query - Unique SELECT CONTAINS

    Visual Studio Express 2010
    VB.NET
    SQL Server 2008

    I am having difficulty creating a SQL query to retrieve records where the "Unique" condition is within a cell's value, not being the whole cell itself. To explain this further, I'll show an example of a few records (typed by hand due to font issues)-
    Code:
    "global_tolerance"     "composite_tolerance"
    di0.020ADMEM         di0.010A
    di0.020MADMEM       di0.010MA
    eo0.020ADMEM        N/A
    If I type a query that is something like...
    Code:
    "SELECT DISTINCT global_tolerance, composite_tolerance FROM panel_definition WHERE 
    (global_tolerance LIKE '%di%') AND (global_tolerance LIKE '%eo%')"
    ...then the above records are returned as three distinct rows, as the first and second records are slightly different. What I need some way of doing is to return the records with "di" in the "global_tolerance" column where the record is unique if the "0.020" portion and "0.010" portion are unique (or, in other words, the first and second records are not considered unique to each other).

    Has anyone dealt with this before?

  2. #2
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: SQL Query - Unique SELECT CONTAINS

    You can either return formatted/transformed representation of data or return the data as it is in the table.

    Is the loss of information acceptable?

    You can't return DISTINCT global_tolerance, composite_tolerance with the result you described... it will have to be DISTINCT part_of_global_tolerance_or_xx9.999_portion, part_of_composite_tolerance_or_xx9.999_portion. But please note absence of trailing alpha characters in DISTINCT result.

    If loss of information is unacceptable then you will have to display/represent your data in another manner, e.g. master-detail relationship (or hierarchical query) rather than join result in just one datagrid.

  3. #3

    Thread Starter
    Member
    Join Date
    Jul 2010
    Posts
    44

    Re: SQL Query - Unique SELECT CONTAINS

    That is actually the best possible solution, because I don't need the "eo" or "di" or "ADMEM", I only need the "0.010" and the "0.020". The problem is, I could not track down the syntax to return such a thing. Another issue is that even if there is some syntax to remove the first X characters or last X characters, the substrings before and after "0.010" and "0.020" are never the same length.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.