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.
Author |
Topic |
Christech82
Starting Member
20 Posts |
Posted - 2013-04-24 : 18:40:28
|
Hi thereI have to make a query to select address details from two different tablesSELECT CLIENT.FIRST_NAME , CLIENT.LAST_NAME, ADDRESS, CITY , ZIP_CODEFROM CLIENTS, BUSINESSWHERE 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. |
|
|
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_CODEBarry 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-11Tables are:BUSINESS_REF_NO BNAME BTYPE STREET_NAME CITY ZIP_CODE 2205 BMI Trading 13Rose Road Boston 22-00-112338 CVC Export 7 Broadway New Jersey 33-00-11And 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 ? |
|
|
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 clientsunion 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 |
|
|
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_CODEFROM CLIENTSUNIONSELECT FIRST_NAME, LAST_NAME, ADDRESS, CITY, ZIP_CODEFROM 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 |
|
|
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 clientsunion 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 answeringI can't use UNION because I choose 5 columns from client table where as I only need to choose three columns from Business tableI 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! |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-04-25 : 16:25:40
|
You can do thisSELECT FIRST_NAME, LAST_NAME, ADDRESS, CITY, ZIP_CODEFROM CLIENTSUNIONSELECT ' ' as FIRST_NAME, ' ' as LAST_NAME, ADDRESS, CITY, ZIP_CODEFROM BUSINESS |
|
|
|
|
|
|
|