SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Address from multiple tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Christech82
Starting Member

Hungary
20 Posts

Posted - 04/24/2013 :  18:40:28  Show Profile  Reply with Quote
Hi there
I have to make a query to select address details from two different tables
SELECT CLIENT.FIRST_NAME , CLIENT.LAST_NAME, ADDRESS, CITY , ZIP_CODE
FROM CLIENTS, BUSINESS
WHERE CLIENTS.C_ID = BUSINESS.C_ID;
However my problem is I have the address details in two tables as and I need to show the details of all customers in one query

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/24/2013 :  19:15:10  Show Profile  Reply with Quote
Can you provide your table definitions and some example data and expected output.
Go to Top of Page

Christech82
Starting Member

Hungary
20 Posts

Posted - 04/24/2013 :  19:52:08  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

Can you provide your table definitions and some example data and expected output.



Result should be like this:
FNAME LNAME STREET_NAME CITY ZIP_CODE
Barry Truck 71 Peters Botston 22-00-22
James Trimble ParkWay New York 11-00-11
Scott Jones 13Rose Road Boston 22-00-11
Michael Oliver 7 Broadway New Jersey 33-00-11


Tables are:

BUSINESS_REF_NO BNAME BTYPE STREET_NAME CITY ZIP_CODE
2205 BMI Trading 13Rose Road Boston 22-00-11
2338 CVC Export 7 Broadway New Jersey 33-00-11


And the other table contains names of other clients
However, this table is a child of the other clients table. This table
contain the addresses just for clients who can hire more than once as business. But in Clients table, I have another set of address for personal clients.
My aim is to show the first name, last name, address ( for both business and personal clients) from Clients and Business tables.
By the way in Business table I have the columns (ADDRESS, TOWN ) and I have the same columns in Clients table too, as I want to select them ?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 04/24/2013 :  20:29:05  Show Profile  Reply with Quote
I didn't 100% follow your explanation, but based on what you said about "Business table I have the columns (ADDRESS, TOWN ) and I have the same columns in Clients table", may be this?
select first_name,last_name,address,city,zip_code from clients
union 
select first_name,last_name,address,city,zip_code from business
If that does not do it, can you post the table schema? Take a look at this blog - that might help you post sample data and table schema in manner that someone can make use of. http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/24/2013 :  20:31:33  Show Profile  Reply with Quote

You may want to use UNION to get the data you need; something like this:


SELECT FIRST_NAME, LAST_NAME, ADDRESS, CITY, ZIP_CODE
FROM CLIENTS
UNION
SELECT FIRST_NAME, LAST_NAME, ADDRESS, CITY, ZIP_CODE
FROM BUSINESS



If you want to select client data based on some conditions you may want specify those condtions in a where clause.


It was difficult to read your table descriptions and data.
In the future you may want to follow the instructions at this site regarding posting a question
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

Christech82
Starting Member

Hungary
20 Posts

Posted - 04/25/2013 :  16:07:47  Show Profile  Reply with Quote
quote:
Originally posted by James K

I didn't 100% follow your explanation, but based on what you said about "Business table I have the columns (ADDRESS, TOWN ) and I have the same columns in Clients table", may be this?
select first_name,last_name,address,city,zip_code from clients
union 
select first_name,last_name,address,city,zip_code from business
If that does not do it, can you post the table schema? Take a look at this blog - that might help you post sample data and table schema in manner that someone can make use of. http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx





Thank for answering
I can't use UNION because I choose 5 columns from client table where as I only need to choose three columns from Business table

I to show the result as the following:

FIRST_NAME, LAST_NAME, ADDRESS, CITY, ZIP_CODE (FROM TABLE CLIENT)
ADDRESS, CITY, ZIP_CODE( FROM TABLE BUSINESS)

The reason why cause business clients have business address (that are in business table) to avoid duplication in my date!



Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/25/2013 :  16:25:40  Show Profile  Reply with Quote
You can do this



SELECT FIRST_NAME, LAST_NAME, ADDRESS, CITY, ZIP_CODE
FROM CLIENTS
UNION
SELECT  ' ' as FIRST_NAME,  ' ' as LAST_NAME, ADDRESS, CITY, ZIP_CODE
FROM BUSINESS

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000