Advanced Data Shaping - Multiple Relate Clause

Jan 18, 2006


I'm using a shape query, but instead of using a simple clause "RELATE
field1 to field2" (relates the parent to the child), i wan't to use 2
relates. somthing like "RELATE field1 to field2 AND field3 to field4".

I want to receive in the children RS only the records who apply both

How do i do that ?

Thanks !

View 2 Replies


How To Relate Multiple Tables?

May 2, 2008

Lets sat that i have 3 tables that i want to relate together manually (no wizards) by using SQL statements.I am using Microsoft SQL Server 2005 in Visual Web Developer 2008 using VB programming (ADO.NET)The 3 tables are as follows:
Fields inside each of the tables can be found below:
Floor Table(PK) ID(Required) NameDisplayOrder
Room Table(PK) ID(Required) Name(PK, FK) FloorIDExchangeIDExtensionIPDeviceNameRemarks(Required) Equipment(Required) Floor2IDFloorLocationXFloorLocationY
Location Table(PK,FK) FloorID (Required) CountryName(Required) CityName(Required) BuildingName
My Intended relationships between the 3 tables are as follows:
ID field in Floor table is referenced by the field named FloorID in the Room Table
ID field in Floor table is referenced by the field named FloorID in the Location table
NOTE: Location Table serves only as an extension to Floor table thus they have a 1 to 1 relationship, due to My Projects Lead's instructions I am not allowed to consolidate the Floor Table and the Location Table as 1 whole table.
Floor Table has a 1 to Many Relationship with the Room Table.
I Have already done the physical relationships between each of the 3 tables in the Database Diagram.
All i want is the SQL statement that does the link/relationship between this 3 tables, that's all :)

View 6 Replies View Related

How To Relate Multiple Tables?

May 5, 2008

Hi all,
Lets say that i have 3 tables, they are already related together, information on each of the 3 tables and their relationship between eachother can be found below:
I am using Microsoft SQL Server 2005 Database in Visual Web Developer 2008 using VB programming (ADO.NET)
The 3 tables are as follows:


Fields inside each of the tables can be found below:

Floor Table
(Required) Name

Room Table
(Required) Name
(PK, FK) FloorID
(Required) Equipment
(Required) Floor2ID

Location Table
(PK,FK) FloorID
(Required) CountryName
(Required) CityName
(Required) BuildingName

The Relationships between the 3 tables are as follows:

ID field in Floor table is referenced by the field named FloorID in the Room Table

ID field in Floor table is referenced by the field named FloorID in the Location table

NOTE: Location Table serves only as an extension to Floor table thus they have a 1 to 1 relationship, due to My Projects Lead's instructions I am not allowed to consolidate the Floor Table and the Location Table as 1 whole table.

Floor Table has a 1 to Many Relationship with the Room Table.

I Have already done the physical relationships between each of the 3 tables in the Database Diagram, in Visual Web Developer 2008 Express Edition under the Database Explorer

So i have created this form to accept user input:

this form is in a aspx page called AddRooms.aspx

Country Name:______________ (data sent to location table)

City Name:_________________ (data sent to location table)

Building Name:______________ (data sent to location table)

FloorID:[This is a DropDownList which is databinded to the Floor Table]

Room Name:_______________ (data sent to room table)

*Just imagine that the above is a form to accept user input

*With a hit of a submit button all data will be sent to their respective tables in the database

Now i know that i have to JOIN tables in my database in order for my application to work, but i have no experience performing JOIN, i need great guys like you to help me apply JOIN to the 3 tables mentioned above, do let me know if you have any doubts in what i am trying to do.

Do give me that code that i can either apply in the (VB environment Visual Web Developer 2008) and/or in Microsoft SQL Server 2005 environment

Just need the SQL Statment that i will need to construct to perform the JOIN i need to.

Please Help

Many Thanks : )

View 1 Replies View Related

Advanced Select Based On Multiple Field Combinations

May 31, 2006

I have developed an ASP.NET form with 12 different fields that will allow end users the cability to query 3 tables that are relational to one another.

I was curious what is the best way to perform this in a Stored Procedure?

Can I use a UNION -- Not sure if this is the best choice

or account for evey kind of WHERE clause based off IF statements on the data that is passed through the parameters?

View 3 Replies View Related

How To Relate Two Tables

Feb 16, 2013

I have two tables


How can I relate the two tables ,so that I know that who works for who.

View 4 Replies View Related

How To Relate Two Fields Of The Same Table

Apr 18, 2007

I am new to sql and is working with sql server managment 2005 +c# 2005.

My application needs to create a blockdiagram sort of thing say
if in my database i got a table 'Addition' with 'a', 'b', 'c',and the primary key addition_id, and c is related to a and b as c = a+ b.

there is stored procedure name usp_addition which contains this relation. Each time any insert or update is done this sp is executed and all the values are updated for accordingly.
My problem starts in the front end where i need to draw the graphical representation of table addition.

In this graphical representation, I need to draw the labels a, b, c and the arrows from a and b which will connect to c, showing that c has a, b as inputs.

I got the label using dataset and datacolumns but hte problem is how to create the arrows the name of labels (i.e my column names from which the arrow should start and end)

How does I get the information that c as two inputs a, b. I dont need the values since i just want to view the columns in table and which column is input to another column.

Since I need to do this dynamically because my tablename, and the number and name of column would differ does any body knows how to do this.


View 1 Replies View Related

Relate A Contact To Customer

Feb 8, 2007

I have a contact table and a customer table. The two tables will contain columns like
First name
Last Name,
Date of Birth
Post Code,
House Number
Street Name

I would like to find the different combinations in which I can relate the customer and contact data.
Like its is possible that the first name and last name are same but date of birth is different. This indicates that the contact and customer is the same. Now I do not know these combinations and I would like to have this set generated for me.
From Integration Service (Sql Server 2005) I get the data and I would like to know the patterns in which data will differ. Is there any way of achieving this?

I am very new to Data Mining and would like to have some direction as to how to progress with this.

View 1 Replies View Related

Multiple Where Clause?

Jun 11, 2007

where exigo_data_sync.orderdetail.itemcode in (B1001, B1001B, B1007, B1007B, B1008, B1008B, B1000, B1000B, B1006, B1006B, B1009, B1009B)

I keep getting a ADO error stating invalid column names...these are not column names they are the data that i want to use in the where clause. What am I doing wrong?

View 2 Replies View Related

Store Proc To Relate State-County, Etc....

May 30, 2002

Hello Everyone,

Please stick with me for a second...
If someone dealt with tables related to US State/County and ZIP.
We found a company on the Web which have ALL US counties, states, zips and cities in one big table.
We need somehow to break this table( about 76K records) into multiple related tables and estabsish Some sort of relationship among these tables.
1.State table(design)
2.County table( design)
For example if state = 'NY'; then all Counties falls in NY should have relationship '01'. For NJ '02' and so on.
3.Zipcode table( design-same as for county)

If someone has any idea how to solve this problem that would be really appreciated.
Thanks a lot in advance.

View 2 Replies View Related

How To Relate InstanceID Of RunningPackage Object To Logging?

May 23, 2006

Iterating through Running packages as in this code from MSDN:

RunningPackages pkgs = app.GetRunningPackages("yourserver");
// Enumerate through each package in the collection and display some data.
foreach(RunningPackage package in pkgs)
        Console.WriteLine("InstanceID: "+package.InstanceID);
        Console.WriteLine("PackageDescription: "+package.PackageDescription);
        Console.WriteLine("PackageID: "+package.PackageID);
        Console.WriteLine("PackageName: "+package.PackageName);
        Console.WriteLine("UserName: "+package.UserName);

How would I find any rows in sysdtslog90 related to that instance of RunningPackage (i.e. the 'package' variable)?  The InstanceID does not seem to relate to the executionID that is in the log table.


View 15 Replies View Related

Relate Two Tables Based On An 'active' Column

Feb 12, 2007

So I have a question and I will give a related example. Say I have a list of records in a table:

Product Cost Active

hat 1.00 false

coat 2.00 true

I have a DataGridView that shows purchases from a purchase table: Date, ProductID, Buyer

Is there a way to have a DataGridViewComboBoxColumn show only active items so that a user can only select active items in the puchases datagridview, but when they click a purchase from long ago when an item was active they have the ability to pick that old(unactive) item or a new one for an update.

Basically if I just have the ComboBoxColumn bound to a dataset with just active items, the datagrid will fail to load cause old purchases are not in its item list, but I also don't want all the items to be in the list.

View 6 Replies View Related

Advanced Lookup In A Data Flow

Oct 4, 2006

Here is the deal - I have a flat file with 2 fields:
ProdNum FeatureCodes
1 A01, B22, F09
2 C13,C24,E05,G02,G09,J07,J09,M03,M17
3 J07,M01,M17,N02,N11,N13,X15

The I have Excel file like:
Code Description
A01 Handicap features
B22 Smoke-Free
F09 Tinted Windows
C13 Picnic Area
C24 Extra Storage
J07 Tile Flooring
M01 Central Airconditioning

The result in a database needs to be like:
ProdNum Features
1 Handicap features, Smoke-Free, Tinted Windows
2 Picnic Area, Extra Storage, .....
3 Tile Flooring, Central Airconditioning, .....

How can I do this in a data flow - any ideas???

View 8 Replies View Related

Sql WHERE Clause With Multiple Parameters

Jun 28, 2006

hello. I have a database that a client developed that I need to pull data from. it consists of articles that fall into a range of 3 main categories. each article will have up to 7 different subcategories they fall into. I need to be able to sort by main category as well as by subcategory. But when I create the SQL query it gets really messy. I tried using WHERE  Cat1= comm OR leg OR and so on, but there are seven categories so this gets very cumbersome and doesn't quite work. Is there a way to create an array or a subquery for this? I am a total newbie, so any help is much appreciated!

View 2 Replies View Related

Multiple Join Clause

Feb 12, 2004

I have a table "Users" like this:


I need to query the users getting the company's and group's names, but I only know how to join one table. Example:

Select UserId, GroupId, Groups.Name, CompanyId, Companies.Name
From Users JOIN Groups ON Users.GroupId = Groups.Id

Hon can I add the companies table in the Join ?


View 2 Replies View Related

Multiple Criteria In WHERE Clause (was T-SQL)

Oct 5, 2005

How can you handle multiple criteria query in T-SQL ? i wrote selection query and in my where clause i have about 7 different criteria and for some reason when i run the query i do not get any error but i do not get any data return.

So is there any other way to handle multiple criteria in T-SQL ?

View 12 Replies View Related

Where Clause With Multiple Cases

Nov 28, 2012

I have a table with a field that contains an integer which represents the state of a record. This field "intType" may contain values 0-4.

A parameter in my stored procedure "@intUserType" may contain values 0-3

If @intUserType = 0, I need to select the records where intType = 0 or 3 but if @intUserType = 3, I need to return all records where intType > 1, all other values of @intUserType should return no records

The query I am working with seems a bit forced and I feel like it could be simplified, but I can't seem to wrap my head around it.

This is what I am working with:

SELECT * FROM tblEmployees
WHERE (intType = (CASE WHEN @intUserType = 0 THEN 0 ELSE NULL END)
OR intType = (CASE WHEN @intUserType = 0 THEN 3 ELSE NULL END)
OR intType > (CASE WHEN @intUserType = 3 THEN 1 ELSE NULL END))

Maybe it is as good as it needs to be ... I don't know .. I've only been using SQL regulary for a couple of months and I have not had the time to really study it in depth.

View 4 Replies View Related

WHERE Clause With Multiple ANDs

Apr 2, 2015

I have a table (tblAttributes) that looks like this:


I need to return all the distinct IDs where the combination of AttrID and AttrVal matches multiple criteria for that specific ID.

I have the following:

FROM tblAttributes AS a
WHERE a.AttrID = 90 AND a.AttrVal = 7) AS x
FROM tblAttributes AS a
WHERE a.AttrID = 91 AND a.AttrVal = 8) AS y
ON x.ID = y.ID)

View 5 Replies View Related

Using Multiple Values In IN CLAUSE

Jul 26, 2006

how can i use multiple values in IN CLAUSE in a SQL query, that too when the number of values are changing at runtime.

complete SQL code required...............

I have following picture in mind but the values(in IN clause) are changing at Runtime

DECLARE @groups TABLE (group_id int)

SELECT * FROM abc WHERE abc_id IN (SELECT group_id FROM @groups)

View 4 Replies View Related

Multiple Where Clause On One Report

Aug 14, 2007

Hello People, Please help. I have a basic report with a parameter in the 'Where" clause called (@Stat) from the statement below:
" WHERE contractinfo.termdate >= GETDATE()
AND provider.status= 'Active' AND provider.credentialstatus = (@Stat)"

This variable has one of two values: 'A' or 'B' that the user selects, how do I set it up so that if user selects say 'A' then the Where clause would go to one set of constraints ie
"WHERE contract.description NOT LIKE 'NON%' "

But if the user selects 'B' then the Where clause would go to a different set of constraint ie
"WHERE contract.description LIKE 'NON%' "

View 4 Replies View Related

ORDER BY Clause With Multiple Tables

Aug 25, 2005

Hi AllI am having a problem with an ORDER BY clause when selecting information from multiple tables. EgSELECT i.InvoiceId, pd.PayDescription, u.UserNameFROM   Invoice i LEFT OUTER JOIN tblPay ON i.PayId = pd.PayId                       LEFT OUTER JOIN tblUsers ON i.UserId = u.UserIdORDER BY pd.PayDescriptionthis is just an example my query is a lot more complex. Is there any simply way you can do an order by in this way?I am writing this for MSSQL Server 2000ThanksBraiden

View 6 Replies View Related

Like Clause - Preventing Multiple Cases

Sep 9, 2013

I have a like clause like this:


I want to know if it is possible just have one like clause from 1-9:

CAT1, CAT3, ...., CAT9

View 3 Replies View Related

Stuck With WHERE Clause With Multiple Elements

Jul 20, 2005

HiI'm a bit stuck with a SELECT query. This is a simplified version ofwhat I need. I've had a look in a few books and online but I'mdefinitely missing something. I'm trying to avoid looping and cursors.I'll be running this in a stored procedure on SQL 7.I have a separate query which returns a series of numbers, A, say 101103 107 109 113.I have a table (tableB) with a field myFieldB where I have anotherseries of numbers, B. I want return each row in tableB wherei - ALL values in A existii- ANY values in A existFor ii, I can use WHERE myFieldB IN AHow about for i?Is there a good guide on the web or a book on WHERE clauses and/ormore complex SQL?Thanks in advance!Sam

View 1 Replies View Related

To Use Multiple Value Paramater In The IN List Of A Where Clause

Jan 8, 2008

I have a parent and child package. i pass a parent package variable called @abc with a value of (1,2,3,4,5,6) to the child package. here in the oledb source i have a select statement like,
select *
from A
where id in (@abc)

It gives me an error. any way to make this work.

View 14 Replies View Related

Multiple Conditions In An UPDATE Clause

Dec 12, 2007

I have a table (GLTRANS) with thousands of lines.
1 column in the table (ACCNO) has 300 different values which all need to change to a new value.

ie. 11100 all change to 8100
11200 all change to 8200

I know how to do a simple UPDATE
SET ACCNO = '8100'
WHERE ACCNO = '11100'

But how can i combine into 1 script rather than having to continually change this script 300 times??


View 6 Replies View Related

Multiple Values For Single Column In Where Clause

Jun 25, 2004

how does one specify multiple values for a single column in a where clause?


SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')

if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:

View 2 Replies View Related

SQL Server 2012 :: Where Clause On Multiple Columns?

May 16, 2014

Right now I have to do something like this and it is time consuming every time I have to query a specific table...

SELECT lots_of_columns
FROM table
WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1')
AND other_query_critiera_here

Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).

View 4 Replies View Related

Problem With Where Clause When Multiple DESC Indexes

May 12, 2007

I have come across a problem executing a select with a multi-part where clause that only shows up if there are multiple indexes on the table. The situation using a simplified table is shown below

create table tblTest( utcTimestamp datetime NOT NULL, testType int NOT NULL)go

insert into tblTest (utcTimestamp, testType) VALUES('6/1/2003 0:0:0', 100)go

Now, without adding any indexes to the table, I can execute the following select and it works fine, returning the single row in 2003:

select * from tblTest where utcTimestamp < '1/1/2004 0:0:0' and utcTimestamp > '1/1/2003 0:0:0' and testType = 100go

Furthermore, if I introduce a single descending index on just the utcTimestamp:

CREATE INDEX IX_tblTest_Timestamp ON tblTest (utcTimestamp DESC)go

the search still works.

HOWEVER, if I now introduce another index:

CREATE INDEX IX_tblTest_EntryType_Timestamp ON tblTest ( testType, utcTimestamp DESC)go

the search does **not** return the row.
However, if I change the where clause to remove the test of testType:

select * from tblTest where utcTimestamp < '1/1/2004 0:0:0' and utcTimestamp > '1/1/2003 0:0:0'go

it works.

Also, strangely, if I populate the table with a number of records with different dates and execute the following search:

select * from tblTest where utcTimestamp > '1/1/2004 0:0:0' and testType = 100go

I get records from **earlier** than 1/1/2004 (i.e. like the sense of the compare is wrong)

Finally, as I was writing this report, I discovered that all of these problems go away if the DESC is removed from the indexes - so that's my workaround, but it still looks like a bug.


View 1 Replies View Related

Sql Syntax For Multiple Likes Phrases In Where Clause

Nov 16, 2007

You know how you can go:

Code Block
where control_id in (11111,22222,33333,44444)


Code Block
where TextName in ('11111','22222','33333','44444')

and you can do this:

Code Block
where TextName like '11%' or TextName like '22%'

well how do you do this? Or can you... or can we right a function to do it... or are we just hosed writing like after like.

where TextName like in ('11%','22%',... and so on)? is anything like that possible?

or better yet
where TextName like in ( select substring(columnName, 1, 2) + '%' from whatever )

can you imagine the dynamics if that syntax actually worked?

Can a function be written to mimic this functionality? so I can do something like this:
where TextName = function('11%,22%,33%')

View 13 Replies View Related

How To Pass Multiple Values To An IN Clause Through Stored Procedure

Jun 11, 2004

I created a stored procedure like the following in the hope that I can pass mulitple company_id to the select statement:

CREATE PROC sp_test @in_company_code nvarchar(1024)

select company_code, name, description
from member_company
where company_code in (@in_company_code)

However, I tried the following :

exec sp_test 'abc', 'rrd', 'bbc'

Procedure or function sp_test has too many arguments specified.

and SQLServer doesn't like it.

Did I specify this stored procedure correct?
If so, how can I can pass multiple values to the stored procedure then to the sql statement?
If not, is it possible to specify a stored procedure like this?


View 2 Replies View Related

Update Statement - Correlation Name Is Specified Multiple Times In FROM Clause

Jun 11, 2014

I have this update statement that I need to have joined by MSA and spec.

I keep getting an error.
Msg 1011, Level 16, State 1, Line 3
The correlation name 't1' is specified multiple times in a FROM clause.

Here is my statement below. How can I change this?

SET [Count on Billed Charges] = (Select Count(distinct[PCS Number])
From MonthEnds.dbo.vw_All_Products t1
Inner Join MonthEnds.dbo.vw_All_Products t1 on t1.[MSA Group] = t2.[MSA Group] and t1.[Spec 1] = t2.[Spec 1])

View 3 Replies View Related

Handling Multiple Columns Returned By A Subquery With An IN Clause

Mar 25, 2008


I would like to know how i can handle multiple columns returned by a subquery via IN clause in case of sql server 2005. I can do that in oracle by using the following statement:


Thanks and Regards

View 9 Replies View Related

Master Data Services :: Advanced Filtering Of Domain Based Attributes

Jul 31, 2015

Data Scenario – Sitemaster entity has two attributes (Region and Country).

Region and Country are both domain-based attributes on separate entities [Region and . The Country entity has a domain based attribute on REGION.

Data relationship looks like this… REGION > COUNTRY
Customer Requirement

Customer would like the MDS UI to filter the Country drop-down options based on the Region selection.

To my knowledge – the only filtering that can be done is based on user security using Hierarchy Membership Permissions.

View 2 Replies View Related

T-SQL (SS2K8) :: Selecting Data From Table With Multiple Conditions On Multiple Columns

Apr 15, 2014

I am facing a problem in writing the stored procedure for multiple search criteria.

I am trying to write the query in the Procedure as follows

Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
where Model=@Model1 or Model=@Model2 or Model=@Model3
where City=@City1 or City=@City2 or City=@City3

I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved