Solved

Need help with SQL search

Posted on 2004-03-25
13
296 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
0
Comment
Question by:tifosiv122
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 3

Expert Comment

by:debi_mela
ID: 10681903
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.
0
 
LVL 1

Author Comment

by:tifosiv122
ID: 10682023
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
0
 
LVL 3

Expert Comment

by:debi_mela
ID: 10682079
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..
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:tifosiv122
ID: 10682100
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
0
 
LVL 3

Expert Comment

by:debi_mela
ID: 10682170
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.
0
 
LVL 1

Author Comment

by:tifosiv122
ID: 10682226
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
0
 
LVL 3

Expert Comment

by:debi_mela
ID: 10682285
can you just paste the values in your tables??
0
 
LVL 1

Author Comment

by:tifosiv122
ID: 10682324
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
0
 
LVL 3

Expert Comment

by:debi_mela
ID: 10682351
Give me an example..  please show the values in 3 tables and let me know what result  you need. that will help..
 
0
 
LVL 1

Author Comment

by:tifosiv122
ID: 10682397

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
0
 
LVL 3

Accepted Solution

by:
debi_mela earned 500 total points
ID: 10682466
While using union  you should simillar result in both queries..


Try the following.. However i don't understand how you are linking them..

---------------------------------------------------------------------------------

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

union

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';

---------------------------------------------------------------------------------

Thankx.
0
 
LVL 1

Author Comment

by:tifosiv122
ID: 10682497
Works perfectly, thats all I needed, lol:

'' as VendorDescription

Perfect!

Thanks,
Erik
0
 
LVL 1

Author Comment

by:tifosiv122
ID: 10682498
Works perfectly, thats all I needed, lol:

'' as VendorDescription

Perfect!

Thanks,
Erik
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question