Solved

Need help with SQL search

Posted on 2004-03-25
13
272 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

803 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