Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

WHERE LIKE a subquery 1

Status
Not open for further replies.

smah

MIS
Sep 4, 2002
9,396
US
This returns 1 record from my table t1 as expected:
Code:
SELECT DISTINCT myfield FROM t1
	WHERE item = 'myitem'
	LIMIT 1
	;

For the sake of discussion, let's say that the 1 "myfield" value returned was 123456. So then using that information, this will return multiple records like 123456, 123456A, 123456B, and so on and also works as expected:
Code:
SELECT * FROM t1
	WHERE myfield LIKE (CONCAT('123456','%'))
	;

So, putting those together, what I actually want is multiple records to be returned from this:

Code:
SELECT * FROM t1
	WHERE myfield LIKE (CONCAT(SELECT DISTINCT myfield FROM t1
		WHERE item = 'myitem'
		LIMIT 1),'%'))
		;


However, I only get the 1 record for 123456. My brain seems to be a bit frazzled a the moment and I can't decide if it's a logic problem in this query or a format problem. It seems like it should work, but I must be missing something.
 
that doesn't make sense to me

is this what you want?
Code:
SELECT t1.* 
  FROM ( SELECT DISTINCT myfield 
           FROM t1
          WHERE item = 'myitem'
         LIMIT 1 ) AS only_one
INNER
  JOIN t1
    ON t1.myfield LIKE CONCAT(only_one.myfield,%')

if not, could you explain more please? perhaps using real table and column names?



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
That doesn't get me there. Hopefully some real data and an explanation might help. This is a single table of assembly information. One "item" is assembled into another and then into another, etc. The connection between these items (for assembly) is not the item name, it's the "wono" field. The goal is to get all the records that are related to the top-level "item" by connecting them through the "wono" relationship (which thankfully is simply a suffix change).

Some real (but still somewhat simplified) data:
Code:
item       |wono |plannedlabor|op_num
-----------|-----|------------|------
anotheritem|8824 |       0.168|300
anotheritem|8824 |     0.40008|200
anotheritem|8824 |         0.6|100
diffitem9  |8824E|        0.95|200
diffitem9  |8824E|        0.05|100
diffitem8  |8824D|         0.2|100
diffitem7  |8824C|         0.2|100
diffitem6  |8824B|       1.575|200
diffitem6  |8824B|         0.9|100
diffitem5  |8824A|        1.25|400
diffitem1  |8386A|        1.44|100
diffitem1  |8386A|           9|200
diffitem1  |8386A|        2.52|300
diffitem2  |8386B|           3|100
diffitem3  |8386C|         0.9|100
diffitem3  |8386C|           6|200
diffitem3  |8386C|      1.9998|300
diffitem3  |8386C|           3|400
diffitem4  |8386D|        0.12|100
diffitem4  |8386D|        2.28|200
myitem     |8386 |           3|100
myitem     |8386 |      1.0002|200
myitem     |8386 |        0.42|300
diffitem5  |8824A|     0.83325|300
diffitem5  |8824A|         2.5|200
diffitem5  |8824A|        0.25|100

So, this query will simply retun 8386
Code:
SELECT DISTINCT wono FROM t1
    WHERE item = 'myitem'
    LIMIT 1
    ;

And this query will return several rows containing where the wono value is 8386_. Which is actually what I want.
Code:
SELECT * FROM t1
    WHERE wono LIKE (CONCAT('8386','%'))
    ;


But I want to get there by using the "item" field. So I expected this query with subquery to return the same thing as above, but it doesn't.
Code:
SELECT * FROM t1
    WHERE wono LIKE (CONCAT(SELECT DISTINCT wono FROM t1
        WHERE item = 'myitem'
        LIMIT 1),'%'))
        ;
It only returns only 1 row.
 
i dunno, man, but i went and tested the query i gave you yesterday and it works just fine
Code:
CREATE TABLE t1
( item VARCHAR(37)
, wono VARCHAR(9)
, plannedlabor DECIMAL(9,4)
, op_num INTEGER
);
INSERT INTO t1 VALUES
 ('anotheritem','8824',  0.168, 300)
,('anotheritem','8824',0.40008, 200)
,('anotheritem','8824',    0.6, 100)
,('diffitem9', '8824E',   0.95, 200)
,('diffitem9', '8824E',   0.05, 100)
,('diffitem8', '8824D',    0.2, 100)
,('diffitem7', '8824C',    0.2, 100)
,('diffitem6', '8824B',  1.575, 200)
,('diffitem6', '8824B',    0.9, 100)
,('diffitem5', '8824A',   1.25, 400)
,('diffitem1', '8386A',   1.44, 100)
,('diffitem1', '8386A',      9, 200)
,('diffitem1', '8386A',   2.52, 300)
,('diffitem2', '8386B',      3, 100)
,('diffitem3', '8386C',    0.9, 100)
,('diffitem3', '8386C',      6, 200)
,('diffitem3', '8386C', 1.9998, 300)
,('diffitem3', '8386C',      3, 400)
,('diffitem4', '8386D',   0.12, 100)
,('diffitem4', '8386D',   2.28, 200)
,('myitem',    '8386',       3, 100)
,('myitem',    '8386',  1.0002, 200)
,('myitem',    '8386',    0.42, 300)
,('diffitem5', '8824A',0.83325, 300)
,('diffitem5', '8824A',    2.5, 200)
,('diffitem5', '8824A',   0.25, 100)
;              
SELECT t1.*
  FROM ( SELECT DISTINCT wono
           FROM t1
          WHERE item = 'myitem' ) AS top_level
INNER
  JOIN t1
    ON t1.wono LIKE CONCAT(top_level.wono,'%')
;

item        wono   plannedlabor   op_num
diffitem1   8386A   1.4400   100
diffitem1   8386A   9.0000   200
diffitem1   8386A   2.5200   300
diffitem2   8386B   3.0000   100
diffitem3   8386C   0.9000   100
diffitem3   8386C   6.0000   200
diffitem3   8386C   1.9998   300
diffitem3   8386C   3.0000   400
diffitem4   8386D   0.1200   100
diffitem4   8386D   2.2800   200
myitem      8386    3.0000   100
myitem      8386    1.0002   200
myitem      8386    0.4200   300

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Well, after further investigation you are correct. Using the sample data, your query works exactly as expected. As it turns out, so does my original one.

It seems there's something in my real table that is causing me to only get top_level results; the suffixed records do not get output. I guess that I have some more digging to do. At least I figured out that I'm not losing my mind [yet].
 
Both are VARCHAR and there does appear to be trailing blank space characters in both fields.
 
Bingo! - the trailing spaces in wono were the problem. (I didn't mention earlier that I was sorting by date to get the most recent record). Both variations now work as I was expecting them to from the beginning. I thought there was something that I was missing. Thanks.

Modified version of your query
Code:
SELECT t1.*
  FROM ( SELECT DISTINCT wono
           FROM t1
          WHERE item = 'myitem'
			 ORDER BY duedate DESC
			 LIMIT 1) AS top_level
INNER
  JOIN t1
    ON t1.wono LIKE CONCAT(SUBSTR(top_level.wono,1,4),'%')
;

Modified version of my original version
Code:
SELECT *
	FROM t1
	WHERE
		wono LIKE (SELECT distinct CONCAT(SUBSTR(wono,1,4),'%')
			FROM t1
			WHERE item = 'myitem'
			ORDER BY duedate DESC
			LIMIT 1
			)
;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top