Searching for Table Column values from another Table Column 2

WZUP · May 30, 2012

Hello,

I need some direction with searching for a value using two tables.
I need to search for the p.pattern value with the d.itemnmbr

Exmample

SELECT p.pattern, d.itemnmbr, d.itemdesc,
d.qtyorder, d.unitcost, c.cmmttext
FROM pop10100 h
left outer join POP10110 d on d.ponumber=h.ponumber
left outer join pop10550 c on d.ponumber=c.popnumbe
left outer join a_patternlocation P on p.vendor=d.vendorid
where d.itemnmbr like p.pattern

The return results are multiple records for each p.pattern in Table P

Sorry if this a dumb question.

Wzup

gmmastros · May 30, 2012

Can you post some sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

WZUP · May 30, 2012

When I use "and d.itemnmbr like p.pattern" no records are returned.

SELECT p.pattern, d.ponumber, convert(varchar,h.DOCDATE,106) as PODate, d.itemnmbr, d.itemdesc,
d.qtyorder, d.unitcost, d.extdcost, d.vendorid, r.vendname,
c.cmmttext
FROM pop10100 h
left outer join POP10110 d on d.ponumber=h.ponumber
left outer join pop10550 c on d.ponumber=c.popnumbe
left outer join pm00200 R on r.vendorid=d.vendorid
left outer join a_patternlocation P on p.vendor=d.vendorid
where d.itemnmbr like '%PATTERN%'
--and d.itemnmbr like p.pattern

Here is the result without the 2nd like statement

Pattern Po number date Item no
2614 PO-0003657 22-Aug-11 PATTERN REPAIR SET-UP
2614 PO-0006622 24-Jan-12 PATTERN REPAIR
2614 PO-0006953 8-Feb-12 PATTERN REPAIR
2614 PO-0003730 30-Aug-11 PATTERN REPAIR SET-UP
2614 PO-0004231 30-Sep-11 PATTERN REPAIR - 2353

The objective is to check to see if the pattern is contained in the item no.

wzup

SQLBill · May 30, 2012

From your example, there aren't any rows where p.pattern exists in d.itemnmbr.

When you do a LIKE without wildcards, it is like using =.

Are you really looking for d.itemnmbr rows that have PATTERN in them? Or are you looking for rows that have p.pattern in d.itemnmbr? If the latter, can you give examples of what matching rows would look like?

This will probably require dynamic SQL. (something like WHERE d.itemnmbr LIKE ''%'' + p.pattern + ''%'')

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

simian336 · May 30, 2012

Try

SELECT p.pattern, d.ponumber, convert(varchar,h.DOCDATE,106) as PODate, d.itemnmbr, d.itemdesc,
d.qtyorder, d.unitcost, d.extdcost, d.vendorid, r.vendname, c.cmmttext
FROM pop10100 h
left outer join POP10110 d on d.ponumber=h.ponumber
left outer join pop10550 c on d.ponumber=c.popnumbe
left outer join pm00200 R on r.vendorid=d.vendorid
left outer join a_patternlocation P
on p.vendor=d.vendorid
and d.itemnmbr = p.pattern
where d.itemnmbr like '%PATTERN%'

Simi

gmmastros · May 30, 2012

This should not require dynamic sql. Instead, try this...

Code:

SELECT p.pattern, d.ponumber, convert(varchar,h.DOCDATE,106) as PODate, d.itemnmbr, d.itemdesc,
	d.qtyorder, d.unitcost, d.extdcost, d.vendorid, r.vendname,
	c.cmmttext
FROM pop10100 h
	left outer join POP10110 d on d.ponumber=h.ponumber
	left outer join pop10550 c on d.ponumber=c.popnumbe
	left outer join pm00200 R on r.vendorid=d.vendorid
	left outer join a_patternlocation P on p.vendor=d.vendorid
where d.itemnmbr like '%PATTERN%'
      and d.itemnmbr like '%' + p.pattern + '%'

As Bill said, without the wildcard search in the pattern (that's the percent symbols), a like search behaves like an = search. By putting the wildcards in to the query, you should probably get the results you want.

Also note that your left joins will behave as though they are inner joins because of the where clause condition on a column from the "right" table. Since it acts like an inner join, you might as well change it to an inner join. If you want to change the behavior to a left join, then you will need to structure the query a little differently.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

WZUP · May 30, 2012

I'm trying to find PO for each sepecific pattern number if there is one.
We are trying to capture the purchasing cost total cost of each one.

Since our purchasing people sometimes put the pattern number within the item number I need to try and search for it. There are 3 other fields that need to be searched as well but for now I just trying to get this to work.

SQLBill

Yes I'm trying to find each pattern number value within the rows.

simian336

Your suggestion return NULL instead of the pattern number.

NULL PO-0006744 1-Feb-12 PATTERN 2846
NULL PO-0005872 7-Dec-11 PATTERN - 2707-NEW
NULL PO-0004273 3-Oct-11 PATTERN REPAIR 3588
NULL PO-0003566 17-Aug-11 PATTERN NEW
NULL PO-0004038 20-Sep-11 PATTERN 3087 RIGGING

gmmastros

Changed to inner joins but returned no results.

SELECT p.pattern, d.ponumber, convert(varchar,h.DOCDATE,106) as PODate, d.itemnmbr, d.itemdesc,
d.qtyorder, d.unitcost, d.extdcost, d.vendorid, r.vendname,
c.cmmttext
FROM pop10100 h
inner join POP10110 d on d.ponumber=h.ponumber
inner join pop10550 c on d.ponumber=c.popnumbe
inner join pm00200 R on r.vendorid=d.vendorid
inner join a_patternlocation P on p.vendor=d.vendorid
where d.itemnmbr like '%PATTERN%'
and d.itemnmbr like '%' + p.pattern + '%'

wzup

wbodger · May 31, 2012

This is a problem

Code:

where d.itemnmbr like '%PATTERN%'
and d.itemnmbr like '%' + p.pattern + '%'

these conditions would both have to exist for you to get anything returned. You want instead

Code:

where d.itemnmbr like '%' + p.pattern + '%'

wb

WZUP · May 31, 2012

wbodger

I've tried that, it returns no results.

The first conditions is the only items that will include the p.pattern.
The problem I've had with all attempts is p.pattern is being treated as a constant (as a string value) and not the contents of p.pattern.

P.PATTERN=2846
d.itemnmbr=Pattern Repair 2846

Thanks anyway.

wzup

wbodger · May 31, 2012

So, this

Code:

SELECT p.pattern, d.ponumber, convert(varchar,h.DOCDATE,106) as PODate, d.itemnmbr, d.itemdesc,
d.qtyorder, d.unitcost, d.extdcost, d.vendorid, r.vendname,
c.cmmttext
FROM pop10100 h
inner join POP10110 d on d.ponumber=h.ponumber
inner join pop10550 c on d.ponumber=c.popnumbe
inner join pm00200 R on r.vendorid=d.vendorid
inner join a_patternlocation P on p.vendor=d.vendorid

returns data and this

Code:

SELECT p.pattern, d.ponumber, convert(varchar,h.DOCDATE,106) as PODate, d.itemnmbr, d.itemdesc,
d.qtyorder, d.unitcost, d.extdcost, d.vendorid, r.vendname,
c.cmmttext
FROM pop10100 h
inner join POP10110 d on d.ponumber=h.ponumber
inner join pop10550 c on d.ponumber=c.popnumbe
inner join pm00200 R on r.vendorid=d.vendorid
inner join a_patternlocation P on p.vendor=d.vendorid
where d.itemnmbr like '%' + p.pattern + '%'

returns no data and the query ends up looking like

Code:

SELECT p.pattern, d.ponumber, convert(varchar,h.DOCDATE,106) as PODate, d.itemnmbr, d.itemdesc,
d.qtyorder, d.unitcost, d.extdcost, d.vendorid, r.vendname,
c.cmmttext
FROM pop10100 h
inner join POP10110 d on d.ponumber=h.ponumber
inner join pop10550 c on d.ponumber=c.popnumbe
inner join pm00200 R on r.vendorid=d.vendorid
inner join a_patternlocation P on p.vendor=d.vendorid
where d.itemnmbr like 'p.pattern'

when you look at it in profiler so there is no value substitution?

wb

SQLBill · May 31, 2012

Are you getting any error messages? For example, about data types not matching?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

WZUP · May 31, 2012

wbodger
This

Code:

SELECT p.pattern, d.ponumber, convert(varchar,h.DOCDATE,106) as PODate, d.itemnmbr, d.itemdesc,
	d.qtyorder, d.unitcost, d.extdcost, d.vendorid, r.vendname,
	c.cmmttext
FROM POP10110 d
	left outer join pop10100 h on h.ponumber=d.ponumber
	left outer join pop10550 c on d.ponumber=c.popnumbe
	left outer join pm00200 R on r.vendorid=d.vendorid
	left outer join a_patternlocation P on p.vendor=d.vendorid
where  d.itemnmbr like '%PATTERN%'

Returns
p.pattern d.itemnmbr
2614 PO-0003657 22-Aug-11 PATTERN REPAIR SET-UP
2614 PO-0006622 24-Jan-12 PATTERN REPAIR
2614 PO-0006953 8-Feb-12 PATTERN REPAIR
2614 PO-0003730 30-Aug-11 PATTERN REPAIR SET-UP
2614 PO-0004231 30-Sep-11 PATTERN REPAIR - 2353
2614 PO-0002350 27-May-11 PATTERN REPAIR SET-UP

This addition

Code:

SELECT p.pattern, d.ponumber, convert(varchar,h.DOCDATE,106) as PODate, d.itemnmbr, d.itemdesc,
	d.qtyorder, d.unitcost, d.extdcost, d.vendorid, r.vendname,
	c.cmmttext
FROM POP10110 d
	left outer join pop10100 h on h.ponumber=d.ponumber
	left outer join pop10550 c on d.ponumber=c.popnumbe
	left outer join pm00200 R on r.vendorid=d.vendorid
	left outer join a_patternlocation P on p.vendor=d.vendorid
where  d.itemnmbr like '%PATTERN%'
	and d.itemnmbr like '%' + P.PATTERN + '%'

returns no result set

SQLBill

No errors nothing

It's as if the second condition is be treated a ='P.PATTERN' instead of the contents of the column name.

wzup

wbodger · May 31, 2012

I'm pretty sure gmmastros meant

Code:

SELECT p.pattern, d.ponumber, convert(varchar,h.DOCDATE,106) as PODate, d.itemnmbr, d.itemdesc, d.qtyorder, d.unitcost, d.extdcost, d.vendorid, r.vendname, c.cmmttext 
FROM POP10110 d left outer join pop10100 h on h.ponumber=d.ponumber 
left outer join pop10550 c on d.ponumber=c.popnumbe 
left outer join pm00200 R on r.vendorid=d.vendorid 
left outer join a_patternlocation P on p.vendor=d.vendorid 
where d.itemnmbr like '%' + P.PATTERN + '%'

and not

Code:

SELECT p.pattern, d.ponumber, convert(varchar,h.DOCDATE,106) as PODate, d.itemnmbr, d.itemdesc, d.qtyorder, d.unitcost, d.extdcost, d.vendorid, r.vendname, c.cmmttext 
FROM POP10110 d left outer join pop10100 h on h.ponumber=d.ponumber 
left outer join pop10550 c on d.ponumber=c.popnumbe 
left outer join pm00200 R on r.vendorid=d.vendorid 
left outer join a_patternlocation P on p.vendor=d.vendorid 
where d.itemnmbr like '%PATTERN%' 
and d.itemnmbr like '%' + P.PATTERN + '%'

wb

WZUP · Jun 1, 2012

wbodger

Code:

SELECT p.pattern, d.ponumber, convert(varchar,h.DOCDATE,106) as PODate, d.itemnmbr, d.itemdesc, d.qtyorder, d.unitcost, d.extdcost, d.vendorid, r.vendname, c.cmmttext 
FROM POP10110 d left outer join pop10100 h on h.ponumber=d.ponumber 
left outer join pop10550 c on d.ponumber=c.popnumbe 
left outer join pm00200 R on r.vendorid=d.vendorid 
left outer join a_patternlocation P on p.vendor=d.vendorid 
where d.itemnmbr like '%' + P.PATTERN + '%'

No errors, No results

wzup

WZUP · Jun 1, 2012

All,

Found the solution, the only problem was P.Pattern needed to be Rtrim
in combination with the other condition

Code:

SELECT p.pattern, d.ponumber, convert(varchar,h.DOCDATE,106) as PODate, 
	d.itemnmbr, d.itemdesc, d.qtyorder, d.unitcost, d.extdcost,
	d.vendorid, r.vendname, c.cmmttext 
FROM POP10110 d
	inner join pop10100 h on h.ponumber=d.ponumber 
	inner join pop10550 c on d.ponumber=c.popnumbe 
	inner join pm00200 R on r.vendorid=d.vendorid 
	inner join a_patternlocation P on p.vendor=d.vendorid
where d.itemnmbr like '%PATTERN%'
	and d.itemnmbr like '%' + RTRIM(p.pattern) + '%'

Thanks for you time.

wzup

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!

Searching for Table Column values from another Table Column 2

WZUP

Programmer

gmmastros

Programmer

WZUP

Programmer

SQLBill

MIS

simian336

Programmer

gmmastros

Programmer

WZUP

Programmer

wbodger

Programmer

WZUP

Programmer

wbodger

Programmer

SQLBill

MIS

WZUP

Programmer

wbodger

Programmer

WZUP

Programmer

WZUP

Programmer

Similar threads

Part and Inventory Search

Sponsor