|
Never touched mysql in my life, but I think you're looking for NOT IN and a subquery.
SELECT tbl_band_songs.id3_artist, tbl_band_songs.Artist, tbl_band_songs.id, tbl_band_songs.id3_title
FROM tbl_band_songs, tbl_song_lyrics
WHERE tbl_band_songs.id3_artist = 239
AND tbl_band_songs.id NOT IN (SELECT tbl_song_lyrics.Sng_ID FROM tbl_song_lyrics)
|
|
|
|
|
You should do a left outer join from tbl_band_songs to tbl_band_lyrics and then select where tbl_song_lyrics.Sng_ID is null.
If you search on Google you will find plenty of examples that explain what an outer join is and how to code it to get the results you want.
|
|
|
|
|
|
My preference for these kinds of queries is to use NOT EXISTS in the where clause.
select your_field_list
from table_one t1
where not exists ( select t2.id
from table_two t2
where t2.id = t1.id
)
;
I've found this to usually be the most performant, though YMMV.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
I had an old boss who swore by this route, in favour of my NOT IN solution above. He said it was much more efficient.
He was right, and I forgot about that whole conversation till I saw your answer.
|
|
|
|
|
I have a table which is some thing like below..
Date ID
2009-07-01 1
2009-07-01 2
2009-07-01 3
2009-08-01 4
2009-08-01 5
2009-08-01 6
2009-09-01 7
2009-09-01 8
2009-10-01 9
2009-10-01 10
2009-11-01 11
....
Now I need to write a query which will show a output like below.
Date Start End
2009-07 1 3
2009-08 4 6
2009-09 7 8
...
How can I do this..
Any help would be highly appreciated
Thanking In Advance
Johnny
|
|
|
|
|
Thats about the easiest type of aggregate you can hope for. Simple MIN and MAX over a grouped set:
SELECT [date],
MIN([id]),
MAX([id])
FROM [YourTableName]
GROUP BY date
|
|
|
|
|
He doesn't want to group by date, he wants to group by month so it is a little more complicated.
(And before you ask, no, I don't know what the solution is.)
|
|
|
|
|
So he did, I missed that.
Still easy though:
SELECT MONTH(date),YEAR(date),
MIN([id]),
MAX([id])
FROM [YourTableName]
GROUP BY MONTH(date),YEAR(date)
and if you really must mash that first column back to the format he wanted
SELECT
CAST(YEAR(date) AS VARCHAR(4)) + '-' + CAST(MONTH(date) AS VARCHAR(2)),
MIN([id]),
MAX([id])
FROM [YourTableName]
GROUP BY MONTH(date),YEAR(date)
|
|
|
|
|
|
Hi,
Try this
SELECT DISTINCT d.Date1 as `ActionDate`, (SELECT MIN(ID) FROM dates WHERE Date1 = `ActionDate`) as `Start`, (SELECT MAX(ID) FROM dates WHERE Date1 = `ActionDate`) as `End` FROM dates d;
Happy Coding
<edit>This assumes table is called dates and date column is called date1
|
|
|
|
|
This gives the same result as mine above, but is far less readable and far less efficient.
Get friendly with the GROUP BY functionality of sql if you want to aggregate data.
|
|
|
|
|
Hi,
I wanna ask How can I verify that my TCP/IP connection to the server is working well???
My code is:
string sConnection = "Provider=SQLNCLI.1;Persist Security Info=False;User ID=sa;Initial Catalog=GMAO;Data Source=127.0.0.1,1433;Password=sa;";
string sSQL = "SELECT com FROM energie; ";
SqlConnection conn = new SqlConnection(sConnection);
SqlCommand comm = new SqlCommand(sSQL, conn);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sSQL,conn);
SqlDataReader dr = null;
try
{
comm.Connection.Open();
da.Fill(ds, "SQL Temp Table");
foreach (DataRow d in ds.Tables[0].Rows)
{
MessageBox.Show("xxxx");
comboBox1.Items.Add(d.ItemArray[0].ToString());
}
}
catch (SqlException ex)
{
MessageBox.Show("connexion impossible");
MessageBox.Show(ex.Message);
return;
}
dr.Close();
comm.Connection.Close();
the error that must appears when connection is failed doesn't existe when running,IS it an indice that my connection goes well??
When googling,I found that the job activity monitor in the sql server management indicates that.is that true??how can I use that in checking my connection result???
Thanks
|
|
|
|
|
well firstly 127.0.0.1 is a ping back address..
to programatically ping an address have a look at this example ping example
what is the error message that you are getting?
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
Hi,
I wanna explain to u something:First,I work with the defualt instance of sql server 2005.So the server availabe is:Admin-PC\MSSQLSERVER that indicates a local IP@=127.0.0.1,1433.Is that true or I am wrong in my behavior?
Second,what u mean by ping back @??
Third,bebore attaching my database,the error"Specified sql server not found or access denied"annoys me.after the attachemment,no exception appears(in the catch block of my code)but still my combobox not filled by data from database.
Thanks a lot
|
|
|
|
|
127.0.0.1 is normally an address you use to test a machines network card.
if you run a command prompy and type ipconifg you will see your network address is different.
on your connection string i would change the ip address for the name of the machine and try that
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
|
Hi,
I am successfully stored & retrieved image files of size less than 64 KB.But when I am storing a image file of size 3 MB, then there is no error,but it only stores partial data in database.
Please refer the code:
------------------------------------------------------------------
1. File CMyDb.hpp----->
class CMyDb : public CDatabase
{
public:
CMyDb();
virtual ~CMyDb();
virtual void BindParameters( HSTMT hstmt );
BYTE* m_pabImage; // address of the data of the blob
int m_nImageLen; // sizeof the data in the image, in bytes
CString m_sStmt; // looks like "UPDATE MyTable SET myFld=?
WHERE idxFld=123"
};
------------------------------------------------------------------
2. File CMyDb.cpp----->
CMyDb::CMyDb()
{
m_pabImage = NULL;
m_nImageLen = 0;
}
CMyDb::~CMyDb()
{
}
void CMyDb::BindParameters( HSTMT hstmt)
{
SQLINTEGER nLenOrInd= m_nImageLen;
SQLRETURN rc;
if ( m_sStmt.Find("?") == -1 )
{ // no binding needed
return;
}
rc= SQLBindParameter(
hstmt, (SQLSMALLINT)1, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_LONGVARCHAR,
m_nImageLen, 0, (void*)m_pabImage, m_nImageLen, &nLenOrInd);
}
------------------------------------------------------------------
3. File Main.cpp->
CFile file (sFileName, CFile::modeRead);//|CFile::typeBinary);
CFileStatus fileStatus;
file.GetStatus(fileStatus);
int nCntImgBytes= 3 * 1024 * 1024;//3MB buffer
BYTE* pabData = (BYTE*)calloc(nCntImgBytes,sizeof(BYTE));
DWORD lTemp = file.ReadHuge((void*)pabData,fileStatus.m_size);
pApp->pServerDatabase->m_pabImage = pabData;
pApp->pServerDatabase->m_nImageLen = fileStatus.m_size;
pApp->pServerDatabase->m_sStmt= "INSERT INTO TEST1 (\"NAME\" , \"myBlobfield\") VALUES ('2',?)";
pApp->pServerDatabase->ExecuteSQL( pApp->pServerDatabase->m_sStmt );
------------------------------------------------------------------
Please reply me as soon as possible.
Regards
Navneet
|
|
|
|
|
There could be a size limit of the BLOB data type in the database engine you use here. I do not think that all database engines throw an exception when the data is more than the specified field can store, it seems your database just trims it down.
I guess a better solution is to store the file somewhere on the file system, and to store the filename (including path) only in the database.
|
|
|
|
|
what database are you using?
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
Hi,
I am using Interbase 6.0 database.
My database table structure is like:
Create table "TEST"
(
"NAME" VARCHAR(10),
"BLOB_DATA" BLOB
)
Interbase 6.0 database only supports BLOB for storing images.It don't support LONGBLOB & other datatypes.
|
|
|
|
|
The BLOB type in your database seems limited to 64KB. There may be a larger type though.
Example: MySQL has TINYBLOB, MEDIUMBLOB, BLOB, and LONGBLOB.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
i what to have an insert transact in ms access 2003
this insert statement should retrieve an ID from another table and then
insert informations
some thing like :
insert into myTable values(123,'123',123,123,
(select DISTINCT ID from ServiceGroupsInfo where NodeName='abc') )
but it does not work
what should i do?????
please help me!
|
|
|
|
|
reza assar wrote: but it does not work
what should i do?????
In the first instance I would suggest you read the forum guidlines, In the second I would define "Doesn't work".
|
|
|
|
|
Looks to me like you are missing the field list. Eg.
insert into myTable
(field1, field2, field3, field4, field5)
values(123,'123',123,123,(select DISTINCT ID from ServiceGroupsInfo
where NodeName='abc'))
though I would further modify it to:
insert into myTable
(field1, field2, field3, field4, field5)
Select Distinct
123,'123',123,123, ID
from ServiceGroupsInfo
where NodeName='abc';
I'm not convinced by the distinct either, as surely ServiceGroupsInfo is unique by NodeName , or it will give multiple results for ID anyway.
|
|
|
|