|
You can join the same table several times. Just use different aliases. Something like:
select table1.contactId,
table1.Str_name,
alias1.str_phonenumber,
alias2.str_phonenumber
from table1,
table2 alias1,
table2 alias2
where alias1.contactId = table1.contactId
and alias2.contactId = table1.contactId
and alias1.Str_phonetype = 'M1'
and alias2.Str_phonetype = 'L1'
Most likely you want to use outer join if there are no matching rows in table2.
|
|
|
|
|
Thank u very much for ur suggestion which it works
|
|
|
|
|
You're welcome
|
|
|
|
|
sir when there is no matching rows in table2, i have to retrieve the names from table1 where str_phonenumber are null. I tried with outer join getting errors please suggest me how to do
Thanks for Your Help
|
|
|
|
|
Nath wrote: I tried with outer join getting errors please suggest me how to do
What is the query like, could you post it? Also what's the error?
|
|
|
|
|
error was cleared but is not displaying record which str_phonenumber values are null
|
|
|
|
|
I don't see why str_phonenumber would affect since it wasn't part of the join. Without seeing your query, it's quite impossible to say what's wrong with it.
|
|
|
|
|
by this query i am getting records with the phone numbers, but if any one phonenumber is null then that record was not displaying, if the table2 contains no data it should display records like left outer join is it possible to do joins to the above query given by you. could you please help me regarding to this as i was new to this database
|
|
|
|
|
Nath wrote: the table2 contains no data it should display records like left outer join is it possible to do joins to the above query given by you
Yes, it's possible. I wrote the query for you so that you can get to start. I'm asking you to post the modified query you have written (the one which has problems) simply because I want to see that you have put effort into this. It doesn't matter if your query isn't working and has problems. The main thing is that you've tried.
|
|
|
|
|
select t.str_firstname,t.str_lastname,l.str_phonetype,l.str_phonenumber,
m.str_phonetype,m.str_phonenumber from
trans_contacts t outer join trans_address_phones l,trans_address_phones m
where t.int_contactid=l.int_contactid and t.int_contactid=m.int_contactid and
l.str_phonetype='L1' and m.str_phonetype='M1'
error is incorrect near the join
and I tried with single alias also
select t.str_firstname,t.str_lastname,l.str_phonetype,l.str_phonenumber from
--m.str_phonetype,m.str_phonenumber from
trans_contacts t outer join trans_address_phones l on
t.int_contactid=l.int_contactid where l.str_phonetype='L1'
here is the same problem thats the reason, whether it is possible to work with join, Help me sir
|
|
|
|
|
You're on the right track. If you modify the query like the following, do you get right results
select t.str_firstname,
t.str_lastname,
l.str_phonetype,
l.str_phonenumber
from trans_contacts t
left outer join
trans_address_phones l
on ( t.int_contactid = l.int_contactid
and l.str_phonetype = 'L1')
|
|
|
|
|
How to detect column that raises this error in PLSQL ? ORA-01401: inserted value too large for column
|
|
|
|
|
i am using oracle 9i, in which it is restricted to 'ORA-01401: inserted value too large for column' where as in orale 10g the information is provided 'ORA-12899: value too large for column "STC_USER"."DEPTTAB"."DNAME" (actual: 15,maximum: 14)'
|
|
|
|
|
First thing that comes in mind is that you catch the error using WHEN and the print out the values for example using DBMS_OUTPUT package.
|
|
|
|
|
Hi All,
I am creating an application in vb.net 2008 and Sql server 2005
I want to create a shared folder in the Server system, Than can be acceasable from all the Other System.
And This file using all the User Access Rights.
How can I create the Access Rights
Arindam Banerjee
Sr. Software Developer
Rance Computer Pvt Ltd.
Kolkata (India)
|
|
|
|
|
Do not post the same question in multiple forums. This question is not an appropriate topic for this forum, and will be ignored.
|
|
|
|
|
Hello,
I'm missing something very basic with databinding to a dataset. I have a dataGridView working and updating from the UI correctly. The dataGridView also populates itself correctly from the SQL database. However, changes made in the grid persist in the grid but are not saved back to the database even on the button click. Here is the entire code. The only thing I do in a new project is add a dataGridView and a button in the designer. Then add this code to the Form1.cs file.
public partial class Form1:Form
{
private BindingSource BindingSource1 = new BindingSource();
SqlDataAdapter dataAdapterFinca = null;
DataSet dataSetFinca = null;
public Form1()
{
InitializeComponent();
this.Load += new System.EventHandler(Form1_Load);
}
protected void Form1_Load(object sender,System.EventArgs e)
{
dataGridView1.DataSource = BindingSource1;
GetData();
}
protected void GetData()
{
SqlConnection connection = new SqlConnection("MyConnectionString");
dataSetFinca = new DataSet();
dataSetFinca.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapterFinca = new SqlDataAdapter("select * from Finca",connection);
dataAdapterFinca.Fill(dataSetFinca,"Finca");
BindingSource1.DataSource = dataSetFinca;
BindingSource1.DataMember = "Finca";
}
private void button1_Click(object sender,EventArgs e)
{
if( dataSetFinca.HasChanges() )
{
this.Validate();
this.BindingSource1.EndEdit();
this.Update();
dataSetFinca.AcceptChanges();
dataAdapterFinca.Update( dataSetFinca, "Finca" );
}
}
}
|
|
|
|
|
Since you are calling dataset.AcceptChanges () before DataAdapter.Update (), so the changes are lost. Try this: (sequence of the two statements reversed)
dataAdapterFinca.Update( dataSetFinca, "Finca" );
dataSetFinca.AcceptChanges();
By the way, I also think that DataAdapter.Update () itself calls DataSet.AcceptChanges () after committing the updates, in which case the last statement will not have any effect and you can safely remove it after confirming.
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
Thanks Mehroz,
I knew it was something stupid but damn did it take a long time. I kept trying not-enough or too-much. Sure would've helped to have a basic example from MS, but I may as well howl at the moon.
Now I've got the UpdateCommand to deal with. It seems that many people say the way to deal is to implement an UpdateCommand manually in SQL. That seems like it would defeat the purpose...every time I change the database, I'll have to get the changes correct in the SQL and won't know if I got it correct until runtime. Any recommendation?
In any case, thanks for stepping in and keeping some sanity here in Costa Rica.
Brice
|
|
|
|
|
can anyone help mi write a code in sql for a store{supermarket}
|
|
|
|
|
|
I've never seen this before. I'm working on a database where the AutoNumber/Identity ID column is being reset when the given table has no data in it and the Sybase Server (SQL Anywhere v7 to be exact) is restarted.
Anyone have any advise as to why this is happening and how it can be corrected?
Thank you in advance and happy holidays to all.
|
|
|
|
|
Are you doing TRUNCATE on the table? That could be one reason.
"If the table contains a column defined as DEFAULT AUTOINCREMENT or DEFAULT GLOBAL AUTOINCREMENT, TRUNCATE TABLE resets the next available value for the column"
Refer to: TRUNCATE TABLE statement [^]
|
|
|
|
|
Hi all,
I am Using Sql reporting Services.In that how to format date to dd/mm/yyyy ??
I have given format expression as dd/mm/yyy in properties .but it displays the mm/dd/yyyy format
pls give any solution
modified on Tuesday, December 23, 2008 8:17 AM
|
|
|
|
|
Format expression: dd/MM/yyyy should work. Are you sure that the field data type is datetime and not an string .
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|