Thursday, March 29, 2012
Help with cross products and functions
I have two tables that i need to join. The first table has a column which
is an encoded addition of one or more values in a second table.
table 1:
col1 col2 col3
1 a b 12
...
table 2:
col1 col2
1 x 4
2 y 8
3 z 16
...
the record for table 1, maps to records 1 and 2 from table 2. I know that
the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of this
math function that decodes the encryption.
I have a function that is able to return a table that lists all the values
in table 2 that correspond to the supplied value in table 1..ie if i call
function fn_mappings(12) i get a table called @.result:
table2value
1 4
2 8
what i need to do is then combine these records together to get a view as
follows
col1 col2 table2value
1 a b 4
2 a b 8
...
is this possible to do? i need this to happen for EACH row in table 1 to
find corresponding records in table 2.
thanks for any and all help!
BenIn order to link both tables, I took the liberty of "re-create" a convenient
environment to get the results you asked. I hope it is ok.
Let me know if it works for you
-- Begin Script
create table tbl1
( id int primary key
, col1 varchar(10)
, col2 varchar(10)
, col3 int
)
create table tbl2
(
id int primary key
, fkid int
, col1 varchar(10)
, col2 int
)
insert into tbl1
values (1, 'a', 'b', 12)
insert into tbl2
values (1, 1, 'x', 4)
insert into tbl2
values (2, 1, 'x', 8)
insert into tbl2
values (3, 2, 'x', 16)
-- View the output of both tables
select * from tbl1
select * from tbl2
go
create function dbo.fn_mappings(@.in int)
returns table
as
return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
col3 = @.in))
go
-- View the dbo.fn_mappings() output
select * from dbo.fn_mappings(12)
-- Output requested
select t1.id
, t1.col1
, t1.col2
, t2.col2
from tbl1 t1
inner join tbl2 t2
on t1.id = t2.fkid
-- Drop all objects
drop function dbo.fn_mappings
drop table tbl1
drop table tbl2
"Ben" wrote:
> Hello,
> I have two tables that i need to join. The first table has a column which
> is an encoded addition of one or more values in a second table.
> table 1:
> col1 col2 col3
> 1 a b 12
> ...
> table 2:
> col1 col2
> 1 x 4
> 2 y 8
> 3 z 16
> ...
> the record for table 1, maps to records 1 and 2 from table 2. I know that
> the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of thi
s
> math function that decodes the encryption.
> I have a function that is able to return a table that lists all the values
> in table 2 that correspond to the supplied value in table 1..ie if i call
> function fn_mappings(12) i get a table called @.result:
> table2value
> 1 4
> 2 8
> what i need to do is then combine these records together to get a view as
> follows
> col1 col2 table2value
> 1 a b 4
> 2 a b 8
> ...
> is this possible to do? i need this to happen for EACH row in table 1 to
> find corresponding records in table 2.
> thanks for any and all help!
> Ben|||Edgardo,
Thank you for your response...however, I do not have the liberty of changing
table2's structure. The data is provided by an external source and I am
creating custom reporting for it. Therefor i cant give it a foreign key typ
e
column.
do you have any other ideas?
Thanks again
Ben
"Edgardo Valdez, MCSD, MCDBA" wrote:
> In order to link both tables, I took the liberty of "re-create" a convenie
nt
> environment to get the results you asked. I hope it is ok.
> Let me know if it works for you
> -- Begin Script
> create table tbl1
> ( id int primary key
> , col1 varchar(10)
> , col2 varchar(10)
> , col3 int
> )
> create table tbl2
> (
> id int primary key
> , fkid int
> , col1 varchar(10)
> , col2 int
> )
> insert into tbl1
> values (1, 'a', 'b', 12)
> insert into tbl2
> values (1, 1, 'x', 4)
> insert into tbl2
> values (2, 1, 'x', 8)
> insert into tbl2
> values (3, 2, 'x', 16)
> -- View the output of both tables
> select * from tbl1
> select * from tbl2
> go
> create function dbo.fn_mappings(@.in int)
> returns table
> as
> return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
> col3 = @.in))
> go
> -- View the dbo.fn_mappings() output
> select * from dbo.fn_mappings(12)
> -- Output requested
> select t1.id
> , t1.col1
> , t1.col2
> , t2.col2
> from tbl1 t1
> inner join tbl2 t2
> on t1.id = t2.fkid
> -- Drop all objects
> drop function dbo.fn_mappings
> drop table tbl1
> drop table tbl2
>
> "Ben" wrote:
>
Wednesday, March 21, 2012
Help with a Query
I have a problem with a query.
Select distinct count(*)
from
Products B1,
categoryProducts B2
where
B1.ProductID = B2.ProductID and
B2.CategoryID in (
SELECT categoryID FROM Categories WHERE
Linkage LIKE (SELECT Linkage+'%' FROM Categories WHERE
CategoryID=4)
)
This query finds all of the CategoryID's from the Categories table, whose
base CategoryID is 4. The table structure of Categories is:
Categories
==========
CategoryID int
Name
Linkage varchar(100)
Data:
CategoryID Name Linkage
...
4 Mind. Body & Spirit 4.1
5 Spiritualism 4.1.1
6 Occult 4.2.2
7 Religion 4.2.3
8 Christianity 4.2.3.1
9 Islam 4.2.3.2
I have a book product which has been categorised into three sub-categories,
using a table called CategoryProducts.
Products
========
ProductID int
Title varcahr(250)
CategoryProducts
================
CategoryID int
ProductID int
Products Data:
-----
ProductID Title
1 The Sacred and the Profane
CategoryProducts Data:
-------
CategoryID ProductID
4 1
5 1
7 1
So The Sacred and the Profane has been categorised into 3 categories, all
falling within or under Mind. Body & Spirit. When I execute the query at
the top here, I get 3 results - this is correct. However, I only want 1
item, since they are all the same book. I was hoping the distinct SQL
keyword would work, but it doesnt, and I am lost - help!
jr.
I'm not sure I understand your query as it uses a count. I assume that was a test and you are really using something like:
Select distinct *
from ...
The asterisk will return all fields from BOTH tables which will be three separate records.
Instead, try something like:
Select distinct Products.ProductID, Products.Title
from ...
That should just give you the single record you want.
HTH.|||Hi,
Thanks for your suggestion, it works using the distinct the way yousuggest. I didnt explain myself correctly ealier, as I do needthe count(*). As part of my paging routine, the first step is tofind out how many records are available in the complete query, beforepaging is used, so I can say "Viewing 1 to 10 of 2000 records". So I need to count the contents of the full dataset using the links tothe categories using the materialized path. I managed to drop theProducts table as it wasnt adding anything to calculate the count value.
Select distinct count(B2.ProductID)
from
categoryProducts B2
where
B2.CategoryID in (
SELECT categoryID FROM Categories WHERE
Linkage LIKE (SELECT Linkage+'%' FROM Categories WHERE CategoryID=4)
)
The CategoryProducts table does have 3 entries in it, with differentcategory ids but the same product id. I cannot work out why asking toreturn the distinct on the single product id would give only 1 recordrather than 3. Any thoughts?
thanks,
jr.
|||ZLA,
I worked it out - I had a post on the MS boards, and it was highlightedto me that I need to put the distinct inside the count():
Select distinct count(distinct B2.ProductID)
from
categoryProducts B2
where
B2.CategoryID in (
SELECT categoryID FROM Categories WHERE
Linkage LIKE (SELECT Linkage+'%' FROM Categories WHERE CategoryID=4)
)
Thanks for your time in looking at this problem.
jr.
Monday, March 19, 2012
Help with a confusing and advanced query - If Exsists?
I need to select a list of products based on a complex criteria. First off, here is a list of the tables I am using and their function.
Dbo.Products – This table holds product names and information.
Dbo.Products_Attributes – This table holds a list of attributes for each product. It has a many to 1 relationship with Products. This table holds only 2 fields – ProductID and AttributeID
Dbo.Customers – This table holds basic customer information and some of the needed criteria for the product search.
Dbo.Customers_Attributes – This table contains a list of attributes that the customer needs in his/her products. This table has a many to 1 relationship with Customers and only has 2 fields, CustomerNum and AttributeID
Dbo.Attributes – This table contains all of the different attributes possible for our products to have. Each can be used by a customer when choosing criteria.
Background: Each attribute is a True / False. Either they want that attribute, or they do not care if they get it or not. This is where the hard part comes in. If a particular attribute is listed as needed in dbo.Customers_Attributes, then I do not want any records pulled from Products that DOES NOT have this attribute. On the other hand, if the attribute is NO listed in the customers_attributes list, it is assumed that the customer wants it. In other words, if there were NO attributes in the customers_attributes list, ALL products would be returned. Attributes listed in the customers_attributes table are "required" (no product will be returned that does not have what is in the list).
I hope I was clear enough… I had to change the table names a functions slightly because of some stupid policy about giving out too much proprietary information here.
If any of you know how to do the above with a subquery of some kind, please let me know.
Dave LarsonI'm not sure that I catch your task properly, but try something like that:
SELECT ProductId, ProductName
FROM dbo.Products
WHERE (NOT (ProductId IN
(SELECT dbo.Products.ProductId
FROM dbo.Products INNER JOIN
dbo.Product_Attributes ON dbo.Products.ProductId = dbo.Product_Attributes.Product
WHERE (dbo.Product_Attributes.Attribute IN
(SELECT dbo.Castomer_Attributes.Attribute
FROM dbo.Castomer_Attributes INNER JOIN
dbo.Customers ON dbo.Castomer_Attributes.Customer = dbo.Customers.CustomerId
WHERE (dbo.Customers.CustomerId = @.CustomerId))))))
where @.CustomerId is a parameter.
Friday, February 24, 2012
Help selecting Veritas products
other has SQL Server. The tape drive is in the domain controller. Am I OK
with Backup Exec 10d for Windows Servers and a remote agent for SQL Server
for Windows Server?
ThanksHi
You could back up your database to disc (a network resource) and then you
would not need the remote agent.
John
"r042wal" wrote:
> I have two Windows Server 2003 servers. One is a domain controller and the
> other has SQL Server. The tape drive is in the domain controller. Am I OK
> with Backup Exec 10d for Windows Servers and a remote agent for SQL Server
> for Windows Server?
> Thanks
>
>|||r042wal wrote:
> I have two Windows Server 2003 servers. One is a domain controller and the
> other has SQL Server. The tape drive is in the domain controller. Am I OK
> with Backup Exec 10d for Windows Servers and a remote agent for SQL Server
> for Windows Server?
> Thanks
>
Don't use a backup agent to do SQL backups, they tend to be problematic
at best, and take longer to backup than a "normal" SQL backup. Just use
the SQL command BACKUP to dump the database contents to a disk file,
then backup that disk file using your tape backup software.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy, can the backup command in SQL be automated? I want to do a scheduled
backup nightly.
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uXSKJRmuGHA.3392@.TK2MSFTNGP04.phx.gbl...
> r042wal wrote:
>> I have two Windows Server 2003 servers. One is a domain controller and
>> the other has SQL Server. The tape drive is in the domain controller.
>> Am I OK with Backup Exec 10d for Windows Servers and a remote agent for
>> SQL Server for Windows Server?
>> Thanks
> Don't use a backup agent to do SQL backups, they tend to be problematic at
> best, and take longer to backup than a "normal" SQL backup. Just use the
> SQL command BACKUP to dump the database contents to a disk file, then
> backup that disk file using your tape backup software.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hi
If you right click the database in the treeview of Enterprise Manager and
choose the backup option, then at the bottom of the general tab is an option
to schedule the backup. Alternatively you can look at using a maintenance
plan which will also allow you to schedule tasks for multiple databases and
allows you to do other things like index reorganization. To create a new
maintenance plan check out the Management branch, right click Database
Maintenance Plans and choose New Maintenance Plan.
John
"r042wal" wrote:
> Tracy, can the backup command in SQL be automated? I want to do a scheduled
> backup nightly.
> Thanks
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:uXSKJRmuGHA.3392@.TK2MSFTNGP04.phx.gbl...
> > r042wal wrote:
> >> I have two Windows Server 2003 servers. One is a domain controller and
> >> the other has SQL Server. The tape drive is in the domain controller.
> >> Am I OK with Backup Exec 10d for Windows Servers and a remote agent for
> >> SQL Server for Windows Server?
> >>
> >> Thanks
> >
> > Don't use a backup agent to do SQL backups, they tend to be problematic at
> > best, and take longer to backup than a "normal" SQL backup. Just use the
> > SQL command BACKUP to dump the database contents to a disk file, then
> > backup that disk file using your tape backup software.
> >
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com
>
>|||r042wal wrote:
> Tracy, can the backup command in SQL be automated? I want to do a scheduled
> backup nightly.
>
Absolutely, just setup a job in SQL Agent that runs the necessary BACKUP
command(s). You might also have a look at a script I've written that I
use to do backups:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Help selecting Veritas products
other has SQL Server. The tape drive is in the domain controller. Am I OK
with Backup Exec 10d for Windows Servers and a remote agent for SQL Server
for Windows Server?
ThanksHi
You could back up your database to disc (a network resource) and then you
would not need the remote agent.
John
"r042wal" wrote:
> I have two Windows Server 2003 servers. One is a domain controller and th
e
> other has SQL Server. The tape drive is in the domain controller. Am I O
K
> with Backup Exec 10d for Windows Servers and a remote agent for SQL Server
> for Windows Server?
> Thanks
>
>|||r042wal wrote:
> I have two Windows Server 2003 servers. One is a domain controller and th
e
> other has SQL Server. The tape drive is in the domain controller. Am I O
K
> with Backup Exec 10d for Windows Servers and a remote agent for SQL Server
> for Windows Server?
> Thanks
>
Don't use a backup agent to do SQL backups, they tend to be problematic
at best, and take longer to backup than a "normal" SQL backup. Just use
the SQL command BACKUP to dump the database contents to a disk file,
then backup that disk file using your tape backup software.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy, can the backup command in SQL be automated? I want to do a scheduled
backup nightly.
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uXSKJRmuGHA.3392@.TK2MSFTNGP04.phx.gbl...
> r042wal wrote:
> Don't use a backup agent to do SQL backups, they tend to be problematic at
> best, and take longer to backup than a "normal" SQL backup. Just use the
> SQL command BACKUP to dump the database contents to a disk file, then
> backup that disk file using your tape backup software.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hi
If you right click the database in the treeview of Enterprise Manager and
choose the backup option, then at the bottom of the general tab is an option
to schedule the backup. Alternatively you can look at using a maintenance
plan which will also allow you to schedule tasks for multiple databases and
allows you to do other things like index reorganization. To create a new
maintenance plan check out the Management branch, right click Database
Maintenance Plans and choose New Maintenance Plan.
John
"r042wal" wrote:
> Tracy, can the backup command in SQL be automated? I want to do a schedul
ed
> backup nightly.
> Thanks
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:uXSKJRmuGHA.3392@.TK2MSFTNGP04.phx.gbl...
>
>|||r042wal wrote:
> Tracy, can the backup command in SQL be automated? I want to do a schedul
ed
> backup nightly.
>
Absolutely, just setup a job in SQL Agent that runs the necessary BACKUP
command(s). You might also have a look at a script I've written that I
use to do backups:
http://realsqlguy.com/twiki/bin/vie...realsqlguy.com