January 10, 2013 at 8:29 pm
I am trying to perform a query that takes the results of one query which joins a customer table a customer_address table and an address_detail table all with an ID# in common. The customer to customer_address and address_detail is a one to many relationship. I need to retun the name fields from customer, company name from customer_address and address lines from address_detail.
Once I have this list I need to match it against another query that determines who in the first query is a current member and remove the results of the second query from the first query.
A member is determined by joining 2 other tables; and order_detail table and a member_product table. This is a many to 1 relationship on product_id.
I don't have much experience in SQL and have been racking my brain trying to figure out a way to do this.
Any help is greatly appreciated.
January 10, 2013 at 8:46 pm
Welcome to SSC! First, you aren't going to get much help with the information in your initial post. We really do need more if we are going to assist. It really comes down to this, we can't see what you see.
To help us help you, the first thing you should do is read the first article I reference below in my signature block regarding asking for help. This article will walk you through what you need to post and how to post it in order to get the best possible answers.
If you have any questions after reading the article, just ask.
January 10, 2013 at 9:02 pm
I browsed tha article and to be honest I am not sure I know enough SQL to write what I am trying to do in a way that would be useful to you.
I was really hoping someone would be able to give me what I thought would be an easy answer to filter the results of one query with the results of another query from different tables.
Thanks anyway. I understand why this would be difficult. I appreciate your response.
January 10, 2013 at 9:09 pm
Without seeing what you are working with, there really isn't a lot we can do to help. There are ways to accomplish the task you are attempting, but with out at least knowing the structure of the tables involved, the queries you are working with, anything we provide is really just flat out shots in the dark and hope we hit the target.
At least try to give us more information so that we can narrow down what you may need to do to accomplish your task.
January 10, 2013 at 11:47 pm
scotdg (1/10/2013)
I browsed tha article and to be honest I am not sure I know enough SQL to write what I am trying to do in a way that would be useful to you.
Fine. No issues.
Lets try and proceed step by step. Can you at give us the structure of the tables( a CREATE TABLE script ) involved or the names of the columns in those tables.
We will then try to add some sample data based on your columns and then proceed with the query you want.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 11, 2013 at 1:06 am
CELKO (1/10/2013)
Since a table is a set, your table is “Customers” -- unless you have only one (sorry business is bad). It is called Strong entity; it can exist by itself. Likewise, “Street_Addresses” is a strong entity. The “Street_Address_Details” (you got this wrong) is a weak entity because it needs a strong entity to exist.
can you please explain ? strong entity and weak entity terms ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 11, 2013 at 5:50 am
You can use what is called a derived table. This means your first select statement is treated like a table and then you can join that to your next table. Something along these lines:
SELECT mt.Dude, dt.Blah, dt.Blah2
FROM MyTable AS mt
JOIN (SELECT mot.ID, yat.Blah, mot.Blah2
FROM MyOtherTable AS mot
JOIN YetAnotherTable AS yat
ON mot.SomeValue = yat.SomeValue
WHERE yat.Something = Somethingelse) AS dt
ON dt.ID = mt.ID
WHERE mt.Whatever = Why;
Another mechanism is CROSS APPLY. It might work for you there too.
To get a more specific answer, you'd need to supply the stuff that Lynn asked for.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2013 at 10:21 am
Nobody is looking for a magic ID#. I am not creating this database it is already created. It is a database for an association management program and the members ID number does exist in all 3 tables which is why there is a 1-many relationship between the customer table and both the address and address detail table.
Yes there IS an address detail table.
Honestly I won't comment on the rest of your post because you come across as extremely arrogant even to go as far as correcting my typos.
To the others - thank you for your responses. I will read them over and hopefully I will learn something.
I was however able to get the data I needed by creating 2 tables from queries and then creating a view and filtering out the current members. As I said originally I thought I could give some information and have the question answered. I don't have much SQL experience and was convinced it was something simple I overlooked.
Thanks again to those that tried to help in a less self-serving manner.
CELKO (1/10/2013)
I am trying to perform a query that takes the results of one query which joins a customer table a customer_address table and an address_detail table all with an ID# in common.
What your narrative describes is wrong. The belief in a generic, magical “id#” is Kabbalah magic, not RDBMS or data modeling. Since a table is a set, your table is “Customers” -- unless you have only one (sorry business is bad). It is called Strong entity; it can exist by itself. Likewise, “Street_Addresses” is a strong entity. The “Street_Address_Details” (you got this wrong) is a weak entity because it needs a strong entity to exist.
RDBMS is “Relational” because we need relationship among the entities (Look E-R diagrams and modeling). You need table for the customers-addresses relationship, which we might call “Locations” or something meaningful.
The customer to customer_address and address_detail is a one to many relationship.
No. Address lines are attributes of an address; they do not get their own details table. Now, orders and order details are separate entities in a 1:m relationship.
CREATE TABLE Customers
(duns CHAR(10) NOT NULL PRIMARY KEY,
customer_name VARCHAR(35) NOT NULL,
..);
CREATE TABLE Addresses
(san CHAR(10) NOT NULL PRIMARY KEY
street_line VARCHAR(35) NOT NULL,
city_name VARCHAR(20) NOT NULL,
state_code CHAR(2) NOT NULL,
zip_code CHAR(5) NOT NULL,
..)
CREATE TABLE Locations
(duns CHAR(10) NOT NULL
REFERENCES Customers(duns),
san CHAR(10) NOT NULL UNIQUE
REFERENCES Addresses(san),
..);
I need to return the name fields [sic] from customer, company name from customer_address and address lines from address_detail.
This is important: fields are nothing whatsoever like fields in the file systems. I will let you write that from these three tables. Also, good DB people know about industry standard identifiers (DUNS, SAN, UPC, etc)
Once I have this list I need to match it against another query that determines who in the first query is a current member and remove the results of the second query from the first query.
No, procedural code does things in steps; in SQL, we write one query. I would probably make the membership status an attribute of the customer, but you did not ..
A member is determined by joining 2 other tables; and order_detail table and a member_product table. This is a many to 1 relationship on product_id.
No. How is a member different from a customer who has a status of current? You did not tell us how to determine membership status. Also, the customer/member is not an attribute of a product; he is in the Orders table, not the products in the Order_Details.
Want to try again?
January 11, 2013 at 10:23 am
Thanks Grant - I might give this a try when I have a chance. As I mentioned I was able to get the data I need so there is no immediate need.
January 11, 2013 at 10:50 am
scotdg (1/11/2013)
Honestly I won't comment on the rest of your post because you come across as extremely arrogant even to go as far as correcting my typos
You hit the nail right on the head. Although Joe occasionally makes a good point, and he is obviously knowledgable, I think most folks here just ignore everything he says becuase of your statement above.
Now, as Kingston suggested, in addition to the table structure, it would help if you could make a mock up of what you want your results to look like.
[Edit] - Sorry, I did not realize you were able to get your data.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply