|
ok, i am just climbing down the tree...
|
|
|
|
|
| Wheres that spoon icon? Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have three primary tables:
CustomerMaster
ContactMaster
AddressList
Each customer master has a one to many relationship with contacts.
Each contact has a possible 1:1 relationship with the AddressList. (ie. null addresses ok)
Each address in the AddressList has a 1:1 relationship with CityEnumerators and StateEnumerators.
I'm trying to get a view working that shows all contacts that are active whose CustomerMaster record is active. Unfortunately I only get the contacts that have addresses and not the ones without addresses. I thought the following query would do it....but no go. (BTW also tried RIGHT OUTER JOIN with no luck also)
Any help would be appreciated as to what I'm doing that is screwing the pooch.
SELECT TOP (100) PERCENT dbo.CustomerMaster.BSACustomerKey, dbo.ContactMaster.ContactName, dbo.ContactMaster.BusinessPhone, dbo.ContactMaster.MobilePhone,
dbo.ContactMaster.Email, dbo.ContactMaster.FirstName, dbo.ContactMaster.LastName, dbo.ContactMaster.EmergencyMaintenance,
dbo.ContactMaster.NormalMaintenance, dbo.ContactMaster.NetworkAbuse, dbo.ContactMaster.IssuedSwipeCard, dbo.ContactMaster.CardNumber,
dbo.AddressList.CustomerAddressLine1, dbo.AddressList.CustomerAddressLine2, dbo.AddressList.CustomerAddressLine3, dbo.AddressList.CustomerPostBox,
dbo.CityEnumerator.City, dbo.StateEnumerator.State, dbo.AddressList.CustomerCounty, dbo.AddressList.CustomerCountry, dbo.AddressList.CustomerZipcode,
dbo.ContactMaster.BmcCustKey
FROM dbo.ContactMaster INNER JOIN
dbo.CustomerMaster ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity LEFT OUTER JOIN
dbo.AddressList ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity INNER JOIN
dbo.CityEnumerator ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity INNER JOIN
dbo.StateEnumerator ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE (dbo.CustomerMaster.CustomerStatus = 1) AND (dbo.ContactMaster.Status = 1)
ORDER BY dbo.CustomerMaster.BSACustomerKey
|
|
|
|
|
Try changing the 2 inner joins after the left join to outer joins.
Remove the order by to your calling method, this gets rid of the top 100% as well.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SELECT TOP (100) PERCENT dbo.CustomerMaster.BSACustomerKey, dbo.ContactMaster.ContactName, dbo.ContactMaster.BusinessPhone, dbo.ContactMaster.MobilePhone,
dbo.ContactMaster.Email, dbo.ContactMaster.FirstName, dbo.ContactMaster.LastName, dbo.ContactMaster.EmergencyMaintenance,
dbo.ContactMaster.NormalMaintenance, dbo.ContactMaster.NetworkAbuse, dbo.ContactMaster.IssuedSwipeCard, dbo.ContactMaster.CardNumber,
dbo.AddressList.CustomerAddressLine1, dbo.AddressList.CustomerAddressLine2, dbo.AddressList.CustomerAddressLine3, dbo.AddressList.CustomerPostBox,
dbo.CityEnumerator.City, dbo.StateEnumerator.State, dbo.AddressList.CustomerCounty, dbo.AddressList.CustomerCountry, dbo.AddressList.CustomerZipcode,
dbo.ContactMaster.BmcCustKey
FROM dbo.ContactMaster INNER JOIN
dbo.CustomerMaster ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity RIGHT OUTER JOIN
dbo.AddressList ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity LEFT OUTER JOIN
dbo.CityEnumerator ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity LEFT OUTER JOIN
dbo.StateEnumerator ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE (dbo.CustomerMaster.CustomerStatus = 1) AND (dbo.ContactMaster.Status = 1)
ORDER BY dbo.CustomerMaster.BSACustomerKey
|
|
|
|
|
SELECT dbo.CustomerMaster.BSACustomerKey,
dbo.ContactMaster.ContactName,
dbo.ContactMaster.BusinessPhone,
dbo.ContactMaster.MobilePhone,
dbo.ContactMaster.Email,
dbo.ContactMaster.FirstName,
dbo.ContactMaster.LastName,
dbo.ContactMaster.EmergencyMaintenance,
dbo.ContactMaster.NormalMaintenance,
dbo.ContactMaster.NetworkAbuse,
dbo.ContactMaster.IssuedSwipeCard,
dbo.ContactMaster.CardNumber,
dbo.AddressList.CustomerAddressLine1,
dbo.AddressList.CustomerAddressLine2,
dbo.AddressList.CustomerAddressLine3,
dbo.AddressList.CustomerPostBox,
dbo.CityEnumerator.City,
dbo.StateEnumerator.State,
dbo.AddressList.CustomerCounty,
dbo.AddressList.CustomerCountry,
dbo.AddressList.CustomerZipcode,
dbo.ContactMaster.BmcCustKey
FROM dbo.ContactMaster
INNER JOIN dbo.CustomerMaster
ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity
LEFT OUTER JOIN dbo.AddressList
ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity
LEFT OUTER JOIN dbo.CityEnumerator
ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity
LEFT OUTER JOIN dbo.StateEnumerator
ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE dbo.CustomerMaster.CustomerStatus = 1
AND dbo.ContactMaster.Status = 1
ORDER BY dbo.CustomerMaster.BSACustomerKey ASC
|
|
|
|
|
I have a stored procedure that is used with a Search form we have. Search form has about 15 parameters it can take, all optional.
The database is a mess and im stuck working around it so bare with me while I try to explain my issue. There are more issues but want to focus on the Buyer/Seller issue for now.
The long and short of my problem is this - the data entry form has fields for { Buyer First Name, Buyer Last Name, Seller First Name, Seller Last Name}
These will be inserted into table BuyerSeller (for instance)
The schema for this:
BuyerSellerID | FirstName | LastName
this table is holds both Buyer and Seller information. So what we end up with is:
<br />
BuyerSellerID | FirstName | LastName<br><br />
100 | BuyerFirst | BuyerLast<br><br />
101 | SellerFirst | SellerLast<br />
But now the issue comes into play when he created an index lookup table called BuyerSellerInfo that includes:
<br />
BuyerorSellerInfoID (primary key) | MortgageID | BuyerID | SellerID<br />
What the original developer did was put the Buyer information as one record, and the Seller information in another record, each for the same data entry.
so we end up with:
<br />
BuyerorSellerInfoID (primary key) | MortgageID | BuyerID | SellerID <Br><br />
1 | 10 | 1 | NULL<br><br />
2 | 10 | NULL | 2<br />
So now with my question - how can I create a stored procedure that will produce results in the same row. I am getting duplicates for each row (which I know the way it is setup I would). But is there a way in my Stored Procedure to have the output look like:
<br />
BuyerorSellerInfoID (primary key) | MortgageID | BuyerID | SellerID<br><br />
1 | 10 | 1 | 2<br />
Hope this made sense, question me if it does not. The formatting on my code tags didn't go so well the "|" is seperator for columns.
The database is SQL 2000 as well.
Thanks.modified on Friday, March 12, 2010 4:02 PM
|
|
|
|
|
I'm no DB expert, however I would search along these lines:
SELECT * FROM BuyersSellers AS b JOIN BuyersSellers AS s WHERE b.MortgageID=s.MortgageID AND b.BuyerID IS NOT NULL AND s.SellerID IS NOT NULL
|
|
|
|
|
Thank you for the quick reply. I will give it a test and give feedback or mark as answer.
|
|
|
|
|
Swelborn wrote: so bare with me while I try to explain
Have you been reading The Nudist On The Nightshift?  Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
hehe can't say that I have.
|
|
|
|
|
I want to select all values from @table1 that doesn't exist in @table2 , for that see the below scenario and the query, although i get the correct result but i doesn't understand how query is evaluated , I know the concept of left join but how where is evaluated , i can't understand ?
Is query is evaluating like this
1 from @table1 & 1 from @table2
then , 1=1 and t2.id is null = 1=1 and false= no rows.
now , suppose
2 from @table1 and 1 from @table2
then , 2=1 and t2.id is null = 2=1 and false = no rows , according to me but here it is giving 2,
IS Where condition applies only to those rows that are equal in both table.
Please help me ?
DECLARE @Table1 TABLE
(
ID INT
)
INSERT @Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7
DECLARE @Table2 TABLE
(
ID INT
)
INSERT @Table2
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 7 UNION ALL
SELECT NULL
SELECT t1.ID
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2
ON t1.ID = t2.ID
WHERE t2.ID is null
|
|
|
|
|
From SQL Server books online, you may want to spend some time looking through it;
Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.
Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:
LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
RIGHT JOIN or RIGHT OUTER JOIN
A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.
FULL JOIN or FULL OUTER JOIN
A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.
|
|
|
|
|
| This article [^]may help as well Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I know that but what i am asking how that cartesian product is done when " where condition is used in left join"
|
|
|
|
|
I am thinking this tabbed form method in Access 2007 could be good...
If I set my forms to come up as tabbed forms, can i design a pop-up form to be visible only in the current tabbed form/window?
Aside from a complex set of checks on current view state, is there a property setting that locks a pop-up form to the current parent that launched this pop-up?
Or, is there a way to check what tabbed form is currently active? I'm trying to avoid the user getting buried in a sea of pop-ups, but still allow him to switch between forms if he needs to.
Thanks so much,
JJ
|
|
|
|
|
john john mackey wrote: Aside from a complex set of checks on current view state, is there a property setting that locks a pop-up form to the current parent that launched this pop-up?
Not sure if VBA supports it, but you could try to set the new windows as a Child-control of the ParentForm.
john john mackey wrote: Aside from a complex set of checks on current view state
Viewstate? Are you trying this from Access or from an ASP.NET application?
john john mackey wrote: Or, is there a way to check what tabbed form is currently active?
That's usually possible, there's a property called "ActiveTab", "SelectedTab" or "SelectedIndex".I are Troll 
|
|
|
|
|
I am doing this in MS Access 2007.
I found something about a property (Screen.ActiveForm) - this probably means that I will have to do my own Form, Parent/Child management.
Thanks for the advice on looking about a Child property. I will check into this.
|
|
|
|
|
I have project call Managmnet information system for finace,
I use the
1- oracle database 10g
2- oracle developer 10g
I am not have anything about two this software.
I want project alreay did by to this software
or toturial
|
|
|
|
|
I think you are doing your boss/client a disservice!
Get hold of a book on database design, read a bunch of articles, have a wander through SQLServerCentral.com.
Depending on the size if the business you are taking on a very large project, one that will take a skilled single developer a substantial amount of time to even understand the business requirements let alone the database design, etl management, data structuring, business logic layer and delivery interface.
Good luck, you are going to need it!Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Here: Library of free data models[^].
Also: OraFAQ[^]
Good luck: you're plainly going to need it - does your boss know you have no idea where or how to start?Tychotics
"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven
|
|
|
|
|
| What and excellent link - it could help a lot of people here. Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You mean it could let a lot of lazy idiots leverage someone else's work without understanding it?
|
|
|
|
|
Rob Graham wrote: lazy idiots
So you know anush 6 threads up, hopefully they can gain something from seeing a data structure, without any understanding I doubt a canned structure is going to really help someone.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This allows you to execute commands asynchronously (ADO 2) using BeginXXX and EndXXX in .NET in the traditional way.
But, if you want Async access, you could just use multiple threads for that (probably using seperate connections).
What does this entry in the connection string actually do? Is it a case of allowing more than one command to execute at a time against a single connection?Regards,
Rob Philpott.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
