LebGeeks

mahdoum

Member

Advanced SQL statements

Does anyone know a site where I can learn about advanced sql statements like looking up 2 items from 2 tables in 1 statemtent, what to do if a duplicate is found in one statement etc.

duckster

Member

Re: Advanced SQL statements

I can help you. give me the structure of the 2 tables and what do you want to fetch.

battikh

Member

Re: Advanced SQL statements

is'nt it:
     select table1.name, table2.age
     from table1, table2

and for the duplicates, isn't it by using "distinct"?
sorry, i don't know if i understood you well, but that's why i understood  i think mir can help you more than me(bil a7ra, it's not that i think, i'm sure of it )

anyways, if you're interested in "sql for dummies", let me know.

PS: ur PM is full, 3azzela shway

Last edited by battikh (February 23 2007)

mahdoum

Member

Re: Advanced SQL statements

PS: ur PM is full, 3azzela shway

lol battikh didn't know that, will do

for the selection of two items from table you might be right, but duplicate i think is ON DUPLICATE or something. The reason i'm asking is I want a site for continous refrence.

Duckster thanks for your offer but there is nothing I can't do with regular statements, and I need to see what other options I have infront of me to know what I can do quicker....

duckster

Member

Re: Advanced SQL statements

you can use instead of from table1, table2
from table1 inner join table2 on table1.commonfield= table2.commonfield

mir

Member

Re: Advanced SQL statements

Hi mahdoum ,
first i believe in keeping things simple and not complicated
i had a long long list of commands that can be used.. i will see if i can find it for u..i wrote it for my uni on a paper
it is really nice list

check the w3schools tutorial for sql :
http://www.w3schools.com/sql/default.asp

what to do if a duplicate is found in one statement etc.

How to Delete SQL Server Database Duplicate Rows from a Table Using a Specified Column List and No Temp Tables
http://www.sql-server-performance.com/d … icates.asp

check those scripts :
http://www.databasejournal.com/scripts/

here are some of the sites i visit often :
www.sql-server-performance.com
www.databasejournal.com
www.sqlservercentral.com

Last edited by mir (February 26 2007)

mahdoum

Member

Re: Advanced SQL statements

thanks mir, great list of resources

mahdoum

Member

Re: Advanced SQL statements

Here's something I can do, but looking for a quicker/ more efficient way.

I've got a table with 2 fields
field1 is set to primary and auto increment
field2 is set to unique

I want to insert into field2 (field1 is autoincrement) and retrieve the ID that was given to that record. If a duplicate is found (value of field2 was inserted previously) then I want to get the ID of that field.

So how would you do it?

I've got this, but it acts funny sometimes (I copied the ON DUPLICATE from somewhere and not sure how it works). The below code sometimes works and sometimes inserts duplicates.

$query = 'INSERT INTO table SET field2=\''.$value.'\' ON DUPLICATE KEY UPDATE field2=\''.$value.'\'';
$result = $this->query($query);
$genID = mysql_insert_id();

rolf

Member

Re: Advanced SQL statements

You can download the official mysql documentation from the mysql site...

battikh

Member

Re: Advanced SQL statements

1-what you could do is start by doing a select for $value and get the ID. if there are no results, then there is no duplicate, you insert it and get the ID. if you get a result, then there is a duplicate, and you have it's ID.

2-another thing you could do is just directly inserting the value, and getting then getting the id. there will never be duplicates as it is set to unique.

3-the on duplicate key update thing does teh following: you try to insert a row, if it would cause a duplicate for a field set to unique, it will update this row with what you specify after the "on duplicate key update" (you can use that to count for example how many times you have tries to insert this value, by incrementing a field.

4-should'nt the "insert..." thing be without the "set"? is'nt "set" used only with "update" and not with "insert"?
should'nt it be like that: $query="insert into table (field2) values ('$values')";

Last edited by battikh (March 4 2007)

mir

Member

Re: Advanced SQL statements

Ditto what battikh said :)
he's 100% right
and mahdoum beware of sql injection (very intrestin stuff)
battikh did u secretly cross to the software side
or are those the early signs

battikh

Member

Re: Advanced SQL statements

heheheh mir, shhhhh, ma tefda7ineh, it's a secret side of me, i always liked programming, but just as a hobby, not as my job. but keep quiet, ma tefda7ineh, beste7eh bi 7aleh