Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Address from multiple tables

Author  Topic 

Christech82
Starting Member

20 Posts

Posted - 2013-04-24 : 18:40:28
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

549 Posts

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

Christech82
Starting Member

20 Posts

Posted - 2013-04-24 : 19:52:08
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-24 : 20:29:05
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

549 Posts

Posted - 2013-04-24 : 20:31:33

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

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

[/CODE]

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

20 Posts

Posted - 2013-04-25 : 16:07:47
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

549 Posts

Posted - 2013-04-25 : 16:25:40
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
   

- Advertisement -