Solved

Need help with SQL search

Posted on 2004-03-25
13
266 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
  • 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now