We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Need help with SQL search

tifosiv122
tifosiv122 asked
on
Medium Priority
327 Views
Last Modified: 2008-03-10
I need to create a SQL querry that allows the user to search 3 coulmns.

First, my tables:

*******************************
SalesItem:
SalesItemId - PK
Description
CompanyId
ItemId

SupplierItem:
SupplierItemId - PK
ItemId - PK
CD_Supplier - PK
VendorDescription
Priority

SalesAbstractItem:
SalesAbstractItemId - PK
SalesItemId
Unit
OurNumber
Description
ShowOnList
NonListed
SalesPrice1
SalesPrice2
SalesPrice3
SalesPrice4
SalesPrice5
DateOfCreation
CreatedBy
ChangedAt
ChangedBy

*************************

I need to have one search string that will search SalesAbstractItem.OurNumber, SalesAbstractItem.Description, and SupplierItem.VendorDescription.

SalesItem's ItemId is relation to SupplierItem
SalesItem's SalesItemId is relation to SalesAbstractItem

I also need a Union between SupplierItem and SalesAbstractItem.

Thanks,
Erik
Comment
Watch Question

Hi,
  For the first part please try the following,

select c.OurNumber, c.Description, b.VendorDescription
 from SalesItem a, SupplierItem b, SalesAbstractItem c
where a.SalesItemId = c.SalesItemId and a.ItemId = b.ItemId

2) Could you please explain what you need on the union part?. Do you mean matching records.

Thankx.

Author

Commented:
I can use the first part, its basically what I came up with as well, but I used some 'like '%' where I will be able to throw my string from the user search.

As for the Union, I need the supplierItem and the SalesAbstractItem to be unioned. So basically I need to see:

VendorDescription | OurNumber | Description

But they need to be linked...not just a list of random stuff...the VendorDescription needs to be on the same line as 'ournumber'. Since they both have keys on SalesItem it should be possible.

Thanks,
Erik
This query should do it..


select  b.VendorDescription, c.OurNumber, c.Description
 from SalesItem a, SupplierItem b, SalesAbstractItem c
where a.SalesItemId = c.SalesItemId and a.ItemId = b.ItemId
and  c.OurNumber =  'userstring'


if need the result as

VendorDescription | OurNumber | Description

do the following..


select  b.VendorDescription, '|', c.OurNumber, '|' c.Description
 from SalesItem a, SupplierItem b, SalesAbstractItem c
where a.SalesItemId = c.SalesItemId and a.ItemId = b.ItemId
and  c.OurNumber =  'userstring'

Hope this helps..

Author

Commented:
I used | just to show seperation...

I need a union between supplieritem and salesabstractitem or the rows will not be linked, they will just be lists starting at #1 record.

Erik
try this..

select  b.VendorDescription, , c.OurNumber,  c.Description
 from  SupplierItem b, SalesAbstractItem c
where b.itemId = c.SalesItemId


Do you mean union as combining 2 tables?

In SQL union means showing the rows of 2 tables ..
example..
table1:  empid, name

table2:  managerid, name

we can have unuion of these 2 tables, which shows all the rows in table1 first followed by  all the rows in table2.

sorry iam not clear about your union.  Please clarify.

thankx.

Author

Commented:
Yeah i'm stuck on the union part.

Basically our salesitem has itemID and SalesItemID.
ItemID relates to SupplierItem
SalesItemID relates to SalesAbstractItem

I need to pull a row, lets say:

VendorDescription     OurNumber     Description
Pipe                         101                2" Pipe

If I don't union the 2 tables, the VendorDescription and the other 2 columns will have no relation to each other. OurNumber and Description are already linked because they are on the same table...but VendorDescription is not joined in any way unless I union.

I have my 2 statements...one returns 4 rows, the other 3. I was told I can union them but I need to use a new command or something for the second one because you can only union 2 with the extact # of rows...Does this make sense? What is the command I would use to add a temporary spot so I can union a 4 row and a 3 row? <--- this is all I really need at this point.

Thanks,
Erik
can you just paste the values in your tables??

Author

Commented:
Not sure exactly what you mean, but the tables cannot be combined in any way...not my idea.
Basically the supplieritems are items from vendors, say Pepsi.
The abstractitems are stuff we resell or make...so lets say Rum and Coke {made with Pepsi}.
As we get new supplier items our items change, but our items use multiple supplier items, thats why it is impossible to physically join them. I really need to use a union.

Erik
Give me an example..  please show the values in 3 tables and let me know what result  you need. that will help..
 

Author

Commented:

Our#        Description                 AbstractItemID
3000      Series 3000      1

Our#                        Description                                         VendorDescriotion         SalesAbstractItemID
0850-0000-000      F900-550,ADDRESSABLE S/D,ION              55000-550                 115


Those are the two I need to combine, here are my querry:

***********************************************************************

select ournumber, description, salesabstractitem.salesabstractitemid
from
salesabstractitem
where
ournumber like '%' or
salesabstractitem.description like '%';

and

select OurNumber, salesabstractitem.Description, VendorDescription, SalesAbstractItemId
from SalesAbstractItem, SalesItem, SupplierItem
where
salesabstractitem.salesitemid = salesitem.salesitemid
and
salesitem.itemid = supplieritem.itemid
and
supplieritem.vendordescription like '%'
and
salesitem.companyid = '8';

***********************************************************************

These 2 need to be joined {unioned}


Erik
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Works perfectly, thats all I needed, lol:

'' as VendorDescription

Perfect!

Thanks,
Erik

Author

Commented:
Works perfectly, thats all I needed, lol:

'' as VendorDescription

Perfect!

Thanks,
Erik
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.