Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need help with SQL search

Posted on 2004-03-25
13
Medium Priority
?
305 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 2000 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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

610 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