MYSQL :: SUM Query And Comparing Values In WHERE Clause...

Comparing String Values In Two Different Columns
I am trying to do a string comparison between two columns located in two different tables. I tried the following SQL statement and it didn't work. This string comparison is for updating foreign keys in the referencing table.

sn-table (referencing)
e-table(referenced)

update sn left join e using(id)
set sn.Eid=e.id
where sn.name like e.name;

Although the command runs fine, it doe not produce the desired outcome!

I've seen SQL statements where comparison with a literal string works:

...
...
where sn.name like 'TX';

I want to compare variables not literals. I am trying to find a partial match between name on sn and name on e and then update FK in sn using corresponding id from table e.

View Replies !

Comparing Two Tables :: Find Similar Values
I was wondering if there was a SQL command I could use that would find similar values from two specific columns in two different tables.. possibly an intersect?

heres what I mean

Table 1
blogTitle="I love soccer"

Table 2
articleTitle="Soccer is fun"

Is there a SQL command that would be able to find the similarities between each column in each different table if, lets say, the user entered "soccer" as a query?

View Replies !

Query Help, Comparing Rows
Suppose I have the following data:

+----+----------+-----+-----+-----+-----+-----+-----+-----+
| Id | Time | Sun | Mon | Tue | Wed | Thu | Fri | Sat |
+----+----------+-----+-----+-----+-----+-----+-----+-----+
| 11 | 11:20:00 | F | T | T | T | F | F | F |
| 12 | 11:45:00 | F | T | T | T | F | F | F |
| 14 | 12:10:00 | F | T | T | T | F | F | F |
| 15 | 12:35:00 | F | T | T | T | T | T | F |
| 17 | 13:00:00 | F | T | T | T | T | T | T |
| 18 | 13:25:00 | F | T | T | T | T | T | T |
| 19 | 13:50:00 | F | T | T | T | T | T | T |
| 20 | 11:28:00 | F | T | T | T | T | T | F |
| 21 | 11:53:00 | F | T | T | T | T | T | F |
| 22 | 12:18:00 | F | T | T | T | T | T | F |
+----+----------+-----+-----+-----+-----+-----+-----+-----+
I would like to output the data by day pattern. I need some way to determine that in the above table, Mon-Wed is the same, Thu-Fri is the same and Saturday and Sunday are unique.

View Replies !

Comparing Dates Y, M, D , Time In Sql Query
ok.. ive finally got my nice little calender setup to output my dates

now

how do i query my database

for example ive got 2 variables
$startdate = 2006-1-1 00:00:01
$enddate = 2006-1-1 23:59:59

these represent the 1st and last second of the first of january 2006

i have a cell in my table (table called challenges) ( cell called time) that contains a date in the same format for each entry

how do i get all the entries between the start time and the end time..

can i use less than < and more than > as these are not really integar values

the column type is datetime but there is also another column of type datetime

View Replies !

IN Clause For Select Query
Can someone help with query syntax regarding IN/EXISTS..

I'm trying to do this:

insert into table2 (field1) select field1 from table1 where field1 not in
(select field1 from table2)
delete from table1 where field1 in (select field1 from table2)

1. Insert field1 from table1 into table2 if it doesn't already exist there...
2. Once moved, delete from table1.

View Replies !

Nested Query With In Clause
I have 2 tables, tbl_usr and tbl_client

tbl_usr
int_usr_id
str_password
int_client_id

tbl_client
int_client_id
str_login

I want to return int_user_id from tbl_usr with 1 query supplying just str_login and str_password. This is what I have:Code:

SELECT tbl_client.int_client_id AS clientID FROM tbl_client WHERE tbl_client.str_login = 'test' IN (SELECT int_user_id FROM tbl_usr WHERE str_password = 'password' AND int_client_id = clientID)

View Replies !

Where Clause In Select Query
I have this query atm: SELECT postid, body, posttime, commentcount FROM nz_posts WHERE sections = ':7:' ORDER BY 'posttime' DESC LIMIT 0, 5

this works, but "sections" can have something like ":7: :8:", ":4: :7:", etc... all the numbers are between : and there can be one or many numbers (all diferent).

I want to select all the rows that has :7: in the sections field.

View Replies !

Using STRCMP In WHERE Clause For A Query
Code:

SELECT group_name
FROM groups_table
WHERE STRCMP('group_name', 'GUESTS')
AND STRCMP('group_name', 'BOTS');

But that didn't work. I worked around the problem in php by putting the group names I don't want in an array and then using !in_array() to filter out the unwanted groups, but i assume that is less efficient. Am I correct that it is less efficient to do this in php, and what was I doing wrong on the mysql query?

[edit - it just occurred to me that maybe 0 is not false in mysql so that I should have said

Code:

SELECT group_name
FROM groups_table
WHERE STRCMP('group_name', 'GUESTS') <> 0
AND STRCMP('group_name', 'GUESTS') <> 0;

Is that the secret (I'm not around my pc now so I can't check yet).

And even if that is the answer, is it better to use SMPCMP or php's !in_array() to do this filter?

View Replies !

Which Query Is Used For Minus Clause
Can u give me the ans which query is used for minus clause. in MySql?

View Replies !

SQL Query JOIN And LIKE Clause
I need to run a query which uses a like query like the one below, no problem:

SELECT * FROM footballers WHERE sname LIKE '%$criteria%'

however, if I want to join two tables and still use the LIKE clause how would the syntax look??

My guess below was incorrect. any help would be appreciated. thank you

WHERE sname LIKE '%$criteria%' AND (footballers.footballerID = PremTeams.footballerID)

View Replies !

Selecting Rows While Using Values From These Rows In The Where Clause
I have a table which includes 3 columns:

id: just an id
name: a name
parent: the id of the row which this row is a child of.

In my example I want to select the rows with the id 5, it's parent, and the parent's parent. I found a syntax that workes in this case:

SELECT `name`,`id` FROM `locations` WHERE `id`='5' or `id`=(SELECT `parent` FROM `locations` WHERE `id`='5') or `id`=(SELECT `parent` FROM `locations` WHERE `id`=(SELECT `parent` FROM `locations` WHERE `id`='5'))

However my question now is if this syntax could be shortened while still working, and also if there is a way to allow for rows to be selected that do not have a parent( value 0 in my table) or that only have a parent but no grandparent.

View Replies !

SQL Query Fails When Adding A Text Field To The WHERE Clause
I just started to learn MySql and php and am relatively new to how databases work. I setup my database and wrote the following code and I get the error:

View Replies !

What Query To Check If Any Rows Exist Satisfying WHERE Clause?
I'm looking for a query that will check if any rows exists in a table according to a WHERE condition. I know I can use COUNT(*) but then mysql will do unnecessary task of counting all the rows whereas I just need true or false. So far I did this:

SELECT COUNT(*) AS exists FROM mytable WHERE ...
Sometimes I just select the first row and check later in php how many rows have been returned:

SELECT some_col FROM mytable WHERE ... LIMIT 1
But I cannot do this check (or can I?) in sql alone and I have problems when I want to use this in a subquery, for example:

SELECT id,
name,
(SELECT COUNT(*) FROM mytable WHERE ...) AS exists
FROM othertable
WHERE surname='xxx'
Can I do the same without using COUNT(*)? I would like a query that returns 0 or NULL if no rows were found, or 1 (or some other value) if 1 or more rows were found.

View Replies !

Reference To Table Alias In From Clause To Be Used By Subquery InSelect Clause
I am using 5.0.26-NT on Windows 2000.

I have need to use a reference in the outer from clause in a subquery in
the select clause. Consider the following example:

Select (select b.baitID from b where b.entrydate curdate()) as
wantedBaitIDs from bait_tbl b;

My actual need is more complex than this as part of it is a rough cross
tab. If I try to define the table in the alias, not only do I lose
whatever benefits there are in the particular join I would use in the
outer from clause but would also require the join to be defined in each
subquery, requiring it to be examined each time it is used.

To be absolutely clear, in this example I want to use bait_tbl with the
alias of b in the subquery. In my actual query I reference the same
table twice with a different join set for each. I need to reference a
particular alias as that has the join set I need.

View Replies !

How To Sum Two Aliased Values In A Query
I need to sum two aliased values in a query in order to order the results.

The query looks like this:

View Replies !

Reference To Already Retrieved Values In Query
As a simple example, say there is table 'namelist' with column 'names'
as char(20). I would like to do something akin to:

select namelist.names as mynames, left(mynames,2) as initials;

In this example, I could just do left(namelist.names,2), but in more
complex cases a value retrieved may have had a more complex logic behind
it, e.g., if a bunch of nested if() statements. It would seem logical
that if a value is already retrieved then I should be able to refer to
it within the same query, assuming that the original value does not need
to be recalculated. I know I could use a view to do this, but it adds
another layer of complexity, and I am not sure that the values would not
be recalculated each time the value is needed.

If someone could point out to me the correct terminology for this kind
of thing I believe I should then be able to look up the information
myself. My efforts to find this information on google and the mysql
website were unsuccessful.

View Replies !

How To Generate Sequence Values During Query
I have query like below:

SELECT part_no,part_nm,qty FROM tb_stok_out ORDER BY part_no

as result:

part_no part_nm qty

aaa asdfd 3
abab sdfsdf 4
abab adfdf 5

Is it possible in mysql to generate sequence number using query, so the result will be like below :

1 aaa asdfd 3
2 abab sdfsdf 4
3 abab adfdf 5
... etc

View Replies !

Update Query Where Values Will Come From Other Table
I'm creating an update query which the value will come from another table.

I have here my current query which unfortunately makes the system hangs. Probably because of the query itself is not properly coded.

update boxes b inner join messages m
on b.ctnnumber = m.ctnno
set b.consigneerecv = m.CName,
b.consigneerecvdate = m.DateRcv,
b.phrecventered = "Y",
b.PhilStatus = "delivered",
b.prevreleasestatus = b.releasestatus,
b.releasestatus = "delivered",
b.PhilStatusDate = m.smsrecvdate,
b.phdelprice = "0.00",
b.phdelamt = "0.00",
b.recvrelation = m.Relation,
b.APRecventered = m.smsRecvDate
where b.consigneerecv = ''
or b.consigneerecv = 'NA'
or b.consigneerecv is null;

I'm thinking revising it so that it will not cause the system to hang but I don't know how. Guys please help me with this one. I also have this another idea which probably will not work. My idea was something like this:

Update table1 set table1.column1 = (select table2.column1 where table2.column1 = table1.column1),
table1.column2 = (select table2.column2 where table2.column1 = table1.column1), .....

View Replies !

Accumulating Values Inside A Query
I only have experience of simple queries, and this one is a bit beyond me. I've done some research, and I think it might be possible to do within a single query, but I'm not sure.

I have a single table. It contains columns (among others): date, number. I want to produce a table of DATE, and new_number_count, where 'new_number_count' is the number of numbers on day with date DATE that do not have rows on previous DATEs.

For example, if this is my table:

View Replies !

Distinct Values In Mysql Query
I have mysql query but i am not sure about it,bcoz it return many rows..
I want like distinct values from three tables with ic number.
Which is posted by user...

"SELECT distinct loguser.icnumber,access.acccode,dealerdtl.name,loguser.fullname,access.period,loguser.staffcode,
DATE_FORMAT(access.actdate, '%d-%m-%Y ')As formatteddate1,
DATE_FORMAT(loguser.creationdate, '%d-%m-%Y %H:%i:%s')As formatteddate
FROM dealerdtl,loguser,access where access.icnumber=loguser.icnumber AND loguser.icnumber = '$custic'";
This query return many rows i dont know why, i want only one row which is
inserted by the user as customer id.

View Replies !

A Query To Select A Column When A Percentage Of Values Non Zero?
I wounder whether some of the experts out there might be able to help
me with a problem I'm having. I do not know whether this is possible or
not...

I have a large table of stock price data which is straight-forward
enought. I can select prices based on a ticker and date ranges.
However, what I'd like to do is to select prices only when, say 75% of
them are non-zero (with the goal of eliminating new/suspended/delisted
stocks).

Of course I could just select where price > 0, but then I might get
only a few rows where this is the case. What I would like to do is
always get the full date range of prices, but only if >75% are there.

View Replies !

How Can I Return (x,y) Query Values To Create A Graph?
Im trying to build a graph of values from a counterTable. (Eg - get a list of how many times a page was requested in a week period with each y value being a day).

If the table looks like this...

View Replies !

Valid Query? (Insert Into, Values, Select, Where)
Valid Query? (Insert into, values, select, where) ....

View Replies !

Select Query, Unique Ids With Matching Values
trying to figure out how to select the pid's from the following table which have matching wid's. ie. the table below would return a result set of 8, 22 and 68 because they are shared by wid 1 and 2.

PHP Code:

Â widÂ Â Â Â Â Â Â Â |Â Â Â Â pid
---------------
1Â Â Â Â Â Â Â Â 3
1Â Â Â Â Â Â Â Â 8
1Â Â Â Â Â Â Â Â 22
1Â Â Â Â Â Â Â Â 56
1Â Â Â Â Â Â Â Â 68
1Â Â Â Â Â Â Â Â 92
2Â Â Â Â Â Â Â Â 2
2Â Â Â Â Â Â Â Â 8
2Â Â Â Â Â Â Â Â 15
2Â Â Â Â Â Â Â Â 22
2Â Â Â Â Â Â Â Â 68
2Â Â Â Â Â Â Â Â 77Â

View Replies !

Query To Insert 2 Foreign Key Values In Table
I want to insert 2 foreign key values along with some other value into table ....

View Replies !

Select One Distinct And Several Random Values In One Query
I'm wondering if it's possible to select an id (let's say its 13) and several random id's between 1 and 100 in one query, excluding for example the id's 14 and 15.

I've tried different ways with UNION - but when I include 'order by rand()' in my query, I'll never get the id 13 back. Could anybody help me please?

Here's a query which did not work, because i will always get the the same result, without random values:

SELECT * FROM table WHERE id = '13' UNION DISTINCT SELECT * FROM table WHERE id != 14 AND id != 15 AND (id BETWEEN 0 AND 100) LIMIT 3

View Replies !

Query Browser: Setting NULL Values
with a Query Browser resultset in Edit mode, how can the NULL value be set? If the word NULL is entered, it is interpreted as the string 'NULL'.

View Replies !

Difficult Query - Need To Group Results By Id And Sum Values
I have got the meat of this query done but I am facing a problem. I am doing a VAT analysis whereby I have every shoe that is over size 7 I pay tax on and every shoe below that I don't pay tax on. I also pay tax on accessories.

What I want the query to return is this:

Date | ShopperID | VATable Amount | Non VATable Amount|
2006-3-1 | 802135 | 146.95 | 54.00 |

Basically each shopperID will only appear once which is why I am grouping the results (which I have managed). But I also need it to total up all the VATable amounts that it finds too for both the fields on the right above.

I can get the date and order number without difficulty and I have managed to get the data like this so far where I have the same ShopperID where a shopper has bought more than 1 product:

+------------+----------+-----------------+---------------------+
| Date | ShopperID| VATable Amount | Non VATable Amount |
+------------+----------+------------+---------------+
| 2006-09-04 | 805284 | 0 | 64.00 |
| 2006-09-04 | 805287 | 2.95 | 0 | <-- Here a
| 2006-09-04 | 805287 | 3.25 | 0 | customer has
| 2006-09-04 | 805287 | 3.45 | 0 | bought 4 products
| 2006-09-04 | 805287 | 4.95 | 0 | - I need total
| 2006-09-04 | 805327 | 0 | 53.95 | under each
| 2006-09-04 | 805335 | 0 | 58.95 | ShopperID
| 2006-09-04 | 805414 | 0 | 64.95 |
| 2006-09-04 | 805414 | 3.25 | 0 |
| 2006-09-04 | 805414 | 0 | 64.00 |
| 2006-09-04 | 805414 | 0 | 69.00 |
| 2006-09-04 | 805423 | 0 | 64.95 |
| 2006-09-04 | 805423 | 0 | 69.00 |
| 2006-09-04 | 805423 | 0 | 64.00 |
| 2006-09-04 | 805423 | 3.25 | 0 |
+------------+-----------+----------------+--------------------+

Heres the query currently getting these results (without the GROUP BY ShopperID).

SELECT Date,orders.ShopperID,
CASE
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9][7-9]( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]10( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]11( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',1) REGEXP 'Insole|Helmet|Laces|Wheels|Removal|Protection|Bag'
THEN items_ordered.price -- If it is an accessory add VAT
ELSE 0
END AS 'VATable Amount',

CASE
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^1-9][1|2|3|4|5|6]( )?('
THEN items_ordered.price -- If size 1 - 6 add price to other column
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(UK)?( )?( )?Kids( )?( )?(UK)?( )?12|13( )?( )?('
THEN items_ordered.price -- If size kids 12/13 add price to other column
ELSE 0
END AS 'Non VATable Amount'
FROM items_ordered,orders WHERE (items_ordered.ShopperID = orders.ShopperID) AND (Date >= &#55614;&#57158;-1-30') AND (Date <=

&#55614;&#57158;-9-31');

View Replies !

Using A Limit Clause, But Return The Number Of Rows Of The Query Without The Limit
I have heard of a cool feature that mysql provides a way to return the number of rows of an sql statement that contains a LIMIT as if the LIMIT had not been there.

I search the mysql manual, but could not find anything.

View Replies !

Showing Query Result With Default Values When No Data Available
I have a scenario that user can ask for certain data say income & expense for last 3 months on monthly basis. I have wrote following query which works well but it has an issue:

Let say I have asked for data of FEB 2007, JAN 2007, DEC 2006 using the following query it shows me correct result when all three months have some data but it doesn't show me the desired result when you don't have data in one or more months.

Any suggestion to get result when there is no data:

View Replies !

Truncated Time Values Using TIMEDIFF With ORDER BY Query
I'm using the following query:

SELECT glider, timestamp, TIMEDIFF(timestamp, UTC_TIMESTAMP()) AS last_contact
FROM surfacings
INNER JOIN
(SELECT MAX(timestamp) AS most_recent FROM surfacings GROUP BY glider)
AS tmp
WHERE surfacings.timestamp = tmp.most_recent;

to calculate the amount of time that has elapsed since the last inserted timestamp for each glider. Everything works fine: ....

View Replies !

Query To Match Values, Send Email, Mark Sent
I have a database called customer_information on localhost which contains a table called customer_records that has a unique identifier field for each customer called 'widget'.

I also have a second MySql database on the same localhost called catalog that contains a table called catalog with a field also called called widget which is constantly being updated. The widget value might or might not be of the same value between the two databases.

This is what I need to do:

Run a query against the customer_records table in customer_information db that compares every instance of a widget value to the corresponding widget field in the catalog database's catalog field and if it finds a match, mark the catalog record as having been matched, then grab the customer name, email address and another field called 'message_value' from the customer_records table in the customer_information database and pull them all together to create a php smtp authenticated email message and send it out the door to the email address found in the customer_record table for that customer. The query also needs to then mark the catalog record database record that it matched against as having been matched so any subsequent queries don't send a second email to the customer again.

this needs to run as a scheduled task through a cron job.

I would appreciated any feedback on the query and also are there any open source tools out there that help build queries such as this?

View Replies !

How To Construct Query For Results With Limit (Grouped By Values In Another Field)
Field 1 : GroupName
Field 2 : IndexNumber

The sample data could be {(HR, 1), (HR, 3), (HR, 5), (MANAGER, 10), (MANAGER, 12)}

I wanted to pick the first two values for each GroupName sorted by IndexNumber ascending.

So if I wanted 2 results for each GroupName, the result would be
{(HR, 1), (HR, 3), (MANAGER, 10), (MANAGER, 12)}

This will exclude the third set of data (HR, 5).

View Replies !

Trying To Get Total Count Plus Count From WHERE Clause In One Query
I am fairly familiar with mysql, have been using it with php for about a year now for my development work. One query I am stuck on is the following. I am doing reporting, and need to find the count from a WHERE clause compared to the total count for that group. For the sake of an example, let's say I have a prize table that contains a complete list of prizes. There is a column to list if the prize has been won or not (prizeUsed - Y or N) and another that has the prize name (prizeName - string). I want the final result to list something like:

Total Prizes ____________________ 100 of 850 ......

View Replies !

Comparing Age
So the birthdays in my MySQL table are in a column called DOB and in the format of say: March 8 1985.
Now i have my own PHP function that will convert that to a nice numerical value of age relative to today, which is perfect. it is called getAge(). However, i can't seem to be able to parse an SQL query with that function inside...is there any way to parse my own PHP functions inside an SQL query?

View Replies !

Comparing
I'm currently working with two tables; A scheduling table and a document table. Basically I'm trying to make a query that says this -- "give me everything from this month and day in the Document table that's not in the Schedule"

View Replies !

Comparing Versions Of Sql
I'm taking up a new position and have never used sql before although use
and code in lots of other languages. When doing a bit of survey I find
many many versions of commercial and public versions of sql. Can anyone
tell me if the syntax is generally similar or are they completely
different. If I invest time learning mysql on XP or Linux will that be
useful if I end up using something like Oracle later on? The only one I
would avoid is MS.like things that work occasionally.

View Replies !

Comparing Two Tables
I have these tables:

table02
Id | tab01_field
1 | 00258
2 | 00041

Code:

table01
Id | tab01_field
1 | 00258
2 | 00123
3 | 00825
4 | 00041
5 | 00005

table02
Id | tab01_field
1 | 00258
2 | 00041
When run a query to see the common records in both tables it returns a right result
select table01.tab01_campo from table01,table02 where table01.tab01_field=table02.tab01_field

Code:

00258
00041

But when I try to see the the uncommon records it returns
select tab01_campo from table01,table02 where table01.tab01_field<>table02.tab01_field

Code:

00258
00123
00123
00825
00825
00041
00005
00005

You can see, it returns the uncommon records twice, but also returs the common records. How could make a query tah returns only the uncommon records?

Code:

00123
00825
00005

View Replies !

Comparing Times
I'm creating a voting system, and want to prevent people from cheating. Therefore I'm logging their IP, and the time they voted, and what I'm planning to do is have a script which will delete any records which have been in the database for say 24 hours.

To get the length of time I was gonne use:

HOUR(TIMEDIFF(NOW(),time))

View Replies !

Comparing Dates?
i have an 8 digit date string that i want to compare to a column of type 'date' in
a MySQL db. the 8 digit string is in the format 'CCYYMMDD'. the question is: do
i need to put it into the 'CCYY-MM-DD' format in order to get correct output from
the datediff function, or can i simply leave it how it is?

View Replies !

Data Comparing
i'm writting a program that uses my sql to compare a date time feild to a date that i have to count call on one date the statment looks like this:

SELECT Count(*) FROM wab_answers WHERE wabScreen=1 And DateDiff(answerTime,'" & curDate & "')=0;

where curDate is string containing the date that i would like to compare. I'm not to sure on the correct way to do this. Just need a push in the right direction.

View Replies !

Comparing Differences
I want to find the fields that are in table A but not in table B.
If I have:

table A: id, name, scoreA
table B: id, name, scoreB

I want to find the scores that are in table A but not in B, by example:

table a: 1,john,12|2,mike,14|3,neal,17

table b: 1,hellen,14|2,nolhan,12|3,vicky,10

After make the query:

Score in A that are not in B: 17

View Replies !

Comparing Columns Using LIKE
I can write "column1 LIKE column2" to compare two columns in a fuzzy way, but what about "%" to indicate what should be compared?

column2 contains a list (separated by spaces) of the single values that can be found in column1, so I need to use %value%. I've already validated the values, and they are always a single word, so there's no risk using LIKE per se.

Any more elegant way to do this without having to resort to a PHP loop?

View Replies !

Comparing Two Dates?
I am trying to compare dates in MySQL, but one of my dates from the curdate()-1 method, returns a string that looks like this 20081210 curdate() method returns '2008-12-11'

Both are not dates, so they will not compare.

Here is my query:

SELECT Order_Number
FROM <TableName>
WHERE date(Order_Date) BETWEEN curdate() AND curdate() -7;

View Replies !

Comparing Strings
I can't work out but i'll try to present it simply here.
Assume the table "Names".

FirstName, SecondName
Robert Smith
John Jones
Richard Robertson
Mike Richardson
Ian Stevenson

I need to write a query where by it selects all records where the First Name appears as part of any second name. the output should be

Robert Smith
Richard Robertson

Now, I know I can do
SELECT FirstName, LastName
FROM Names
WHERE LastName LIKE '%Richard%';

This will find all records where Richard appears as part of a last name. But I want it to work for any first name not just a specific one. i basicly need the last line to be something int he order of
WHERE LastName LIKE '%FirstName%';
only a version that works.

View Replies !

Comparing Within A Column
i have a table 'packets' with the following structure : ip_src varchar(20),mac_source varchar(20) .now i want to list all those ip_src which would have more than one mac_source.
how do i go about doing this?

View Replies !

Comparing String To Int
I would like to know what is the performance behavior in reading an writing string and int in several database tables.

What i should use in terms of performance, int or string?

View Replies !

Comparing Dates
I have a problem with me. I am describing the whole situation. I have a table, the structure of the table is as follows:

Table Name: Announcement
FieldName Data Type(Size)
Announcement Varchar(50)
St_Hr Decimal(10,0)
St_Min Decimal(10,0)
St_Day Decimal(10,0)
St_Mon Decimal(10,0)
St_Year Decimal(10,0)
End_Hr Decimal(10,0)
End_Min Decimal(10,0)
End_Day Decimal(10,0)
End_Mon Decimal(10,0)
End_Year Decimal(10,0)

The data stored is:
Test Announcement,10,20,6,10,2005,11,0,8,10,2005

Now, I have to write a query to retrive announcements after filtering them as per the mentioned Starting and Ending Date & Time. The condition should use the local date and time.

View Replies !

Comparing Two Fields With LIKE
Here's the sql:

SELECT *
FROM boxer.lpars a, boxer.libraries b
WHERE b.lpars LIKE '%a.lpar%'
AND a.deleted = 0
ORDER BY node, lpar

b.lpars will commonly look like: "WIN1#WIN2#WIN3#WIN4#"
a.lpar will commonly look like: "WIN1"

When I run this query I get zero matches. What do I need to do to match fields using LIKE?

View Replies !

Comparing Two Table
I have two tables:

CREATE TABLE `FootballerScore` (
`footballerID` mediumint(4) NOT NULL default '0',
`GameWeekID` mediumint(4) NOT NULL default '0',
`GameRuleID` mediumint(4) NOT NULL default '0',
`Goals` mediumint(4) NOT NULL default '0',
`Assists` mediumint(4) NOT NULL default '0',
`CleanSheets` mediumint(4) NOT NULL default '0',
`YellCards` mediumint(4) NOT NULL default '0',
`RedCards` mediumint(4) NOT NULL default '0',
`WeeklyScore` mediumint(4) NOT NULL default '0',
`AccScore` mediumint(6) NOT NULL default '0',
PRIMARY KEY (`footballerID`,`GameWeekID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `footballers` (
`footballerID` mediumint(15) NOT NULL default '0',
`fname` varchar(15) collate latin1_general_ci NOT NULL default '',
`sname` varchar(15) collate latin1_general_ci NOT NULL default '',
`value` decimal(15,1) NOT NULL default '0.0',
`PremTeamID` mediumint(15) NOT NULL default '0',
`position` varchar(15) collate latin1_general_ci NOT NULL default '',
`Importance` varchar(4) collate latin1_general_ci NOT NULL default '',
PRIMARY KEY (`footballerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

The footballers table basically has a list of all the players in the premiership and the footballerscore table has a list of the player who played in a given week. For any one week I need to find out who DIDNT play. I can do this by comparing all the footballerIDs in both tables and the difference is the group I need. However I'm not sure how to put this into an SQL statement.

View Replies !