Joining Tables..

Hai all,
I am having three table and i need to join them...
First table : client [key field : clientid]
Second Table : Address [key field : addressid]
Third Table : contact [key field : forid]
the problem is having the third table like this :
ForId ContactTypeId ContactNo
-- -- --
ABC Phone 123
ABC Email abc@.abc.com
ABC Fax 00123456
XYZ Phone 123
XYZ Email xyz@.xyz.com
XYZ Fax 00123456
on joining i need the result should like
Clientid Address Phone Email Fax
-- -- -- -- --
123 asdcvb 123 abc@.abc.com 00123123
576 sdfsds 123 xyz@.xyz.com 00123456
Can anyone provide me the query plz?
Looking forward for the reply...
Thanx in advance..hi
send us the complete DDL and referencing key, so that we can give u an
accurate solution
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||>> Third Table : contact [key field : forid]
It is a huge problem so rather than masking the flaw with a complicated
query, restructuring the schema would be a better solution. Based on your
narrative, here is how:
CREATE TABLE new_tbl (
customer_id CHAR(3) NOT NULL PRIMARY KEY,
phone_nbr CHAR(10) NOT NULL,
email CHAR(40) NOT NULL
CHECK ( CHARINDEX( '@.', email ) > 1 )
fax CHAR(10) NOT NULL );
Now do:
INSERT new_tbl ( customer_id, phone_nbr, email, fax )
SELECT ForId,
MAX( CASE ContactTypeId WHEN 'Phone' THEN ContactNo
END ) AS "Phone",
MAX( CASE ContactTypeId WHEN 'Email' THEN ContactNo
END ) AS "Email",
MAX( CASE ContactTypeId WHEN 'Fax' THEN ContactNo
END ) AS "Fax"
FROM tbl
GROUP BY ForId ;
Once this is done sucessfully, dump the ill-designed table:
DROP TABLE contact ;
Once you have this new schema, your query should be as simple as having a
join. If due to some reason the table cannot be changed/deleted, then
consider using the SELECT portion in the above INSERT statement for warping
a short term kludge.
Anith

Joining more than one table

Hai,
I need the query for joining more than one table in views without
using where condition in sqlserver.Can anyone help me?
Looking frward for ur reply...
Thanx in advancePrince
SELECT <column lists> FROM Table1 JOIN Table2
ON Table1.pk=Table2.pk JOIN Table3 ON
Table1.pk=Table3.pk
But ,why don't you need a WHERE condition?
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1123762724.429184.8090@.g49g2000cwa.googlegroups.com...
> Hai,
> I need the query for joining more than one table in views without
> using where condition in sqlserver.Can anyone help me?
> Looking frward for ur reply...
> Thanx in advance
>|||Well, without more specific information,
SELECT t1.*, t2.*, t3.*
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.key = t2.key
INNER JOIN Table3 t3
ON t2.key = t3.key
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1123762724.429184.8090@.g49g2000cwa.googlegroups.com...
> Hai,
> I need the query for joining more than one table in views without
> using where condition in sqlserver.Can anyone help me?
> Looking frward for ur reply...
> Thanx in advance
>|||hai Uri Dimant,
i dont much about sqlserver...
i need a query without that ON condition bcoz my cloumns of first
table is not present in columns of second table and vice versa... can i
use union if yes please tell me how?
Thanx..|||hai Aaron Bertrand,
i dont much about sqlserver...
i need a query without that ON condition bcoz my cloumns of first
table is not present in columns of second table and vice versa... can i
use union if yes please tell me how?
Thanx..|||Prince
I'm not sure what you are doing ,so try this
SELECT col1,col2 FROM Table1
UNION ALL
SELECT col3,col4 FROM Table2
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1123764221.132592.18440@.g43g2000cwa.googlegroups.com...
> hai Uri Dimant,
> i dont much about sqlserver...
> i need a query without that ON condition bcoz my cloumns of first
> table is not present in columns of second table and vice versa... can i
> use union if yes please tell me how?
> Thanx..
>|||hai Uri Dimant ,
what u gave works fine...
but in this case it is showing error
SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
Thanx|||> i need a query without that ON condition bcoz my cloumns of first
> table is not present in columns of second table and vice versa...
Then how do you expect to join them? Maybe you could start by showing us
your table structure, some sample data, and desired results. Please check
out http://www.aspfaq.com/5006 for some help on the most effective way to do
this...|||> but in this case it is showing error
> SELECT * FROM Table1
> UNION ALL
> SELECT * FROM Table2
What is the error? What does Table1 look like? What does Table2 look like?
If these tables are unrelated and have different column structures, why on
earth do you need to union them? Can't your application deal with more than
one resultset?|||Prince
Perhapa a number of columns in Table1 is not the same as in Table2. It is
always a good practice not to use SELECT * statement ,particular in
production.
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1123765429.665606.217850@.g47g2000cwa.googlegroups.com...
> hai Uri Dimant ,
> what u gave works fine...
> but in this case it is showing error
> SELECT * FROM Table1
> UNION ALL
> SELECT * FROM Table2
> Thanx
>