MYSQL :: Returning Rows In Left Table Based On Mutliple Criteria In Right

Select Data From 1 Table Based On Criteria From Another Table
is it possible to select all the data in one table based on a criteria from another table?

for instance i want to select all the therapist from massage_therapist WHERE massage_schedule.finish > 0.
i don't want merged results. i just need to list all therapist based on the where criteria from a different table.

these two tables have the therapist_id in common.

View Replies !

Problem With Left Join And Count, Returning More Rows Than What It Should
Am having a problem with a query, strangely ...

PHP

SELECT *
FROM table1 AS mt
LEFT JOIN table2 AS pt ON mt.p_id = pt.p_id
WHERE my_field = 'somevalue'

Is returning a much bigger number (12 rows) for me, then what it should.

PHP

SELECT *
FROM table2
WHERE my_field = 'somevalue'

Is returning only 2 rows

View Replies !

Concat Columns Based On Certain Criteria Using Only SQL?
I have a big table (15 million records). Each row has a parsed address (number, prefix, street, type, suffix) and I need to take these separate pieces and concatenate them. However, often times a few of those pieces will be blank, so I want to prevent any unnecessary spaces.

I'm using MySQL 4.1. I found some documentation for using if statements for MySQL 5.0, tho I dont know if any of it works in 4.1.

The basic psuedo code of what I want to do is this:

View Replies !

Multi-table Queries Returning 0 Rows
I'm working on a Windows client program in C++, using Qt and OleDbPro (for
database access). Everything was working great until I had to use a query
for the first time in the code that pulled data from two tables. The query
executes, but returns 0 rows. I verified that the query should return rows
by running the query both through another client using an ODBC DSN, and
using phpMyAdmin; both times, I got the result I expected.
I'm using the following connection string:
Driver={MySQL ODBC 3.51
Driver};server=2ksvr;database=mydb;uid=user;pwd=pa sswd;option=16386
I tried with and without using the JOIN format, someone suggested some ODBC
drivers don't work well with JOINs, but didn't make any difference
So, the question I have is, is there something special I have to do for a
DSN-less connection using an ODBC provider?

View Replies !

Returning Several Random Rows From A Large Table
I have a table with userinfo like user name, age and so on...

I want to be able to return 10 random rows from this table.

I know that the MySQL RAND() is quite slow for large tables.

So here's what I've come up with:

How about generating a random number in PHP based on the tables MAXID and using

SELECT * FROM the_table WHERE primary_key >= $randNr

and then redo this 10 times to get example 10 results.

this doesn't solve the problem with holes in the table though.

but here's another one:

SELECT primary_key FROM the_table

use mysql_num_rows() in PHP to see how many rows are available.
select 10 random numbers based on the above result.

FOR loop through the 10 random numbers:

SELECT * FROM the_table WHERE primary_key = $rand_number

Are any of these a good idea or should I just smack my head and rethink my solutions.

on a side note
just how slow would it be to use
SELECT * FROM the_table

then creating random numbers based on
mysql_num_rows()

and then moving the data pointer to each individual point using
mysql_data_seek()

Just how slow will this be on large tables with say a million rows?

last one would be horrible I imagine.

I have one PRIMARY KEY column and I'm using INNODB tables. I'm afraid of using MYISAM because the table will be read intensive an hopefully contain > 0,5 mil users. Maybe I'm mistaken about this? Maybe there's a way to use COUNT() and MYISAM?

View Replies !

How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2?

TABLE 1:
+---+------+
| i | name |
+---+------+
| 1 | item1 |
| 1 | item2 |
| 7 | item3 | <-- delete all rows with i=5,6,7
| 6 | item4 | <-- delete
| 5 | item5 | <-- delete
| 5 | item6 | <-- delete
| 7 | item7 | <-- delete
+---+------+

TABLE 2:
+---+---+------+
| j | i | name |
+---+---+------+
| 1 | 1 | item1 |
| 1 | 3 | item2 |
| 1 | 2 | item3 |
| 2 | 5 | item4 | <---- j=2 => i=5
| 2 | 6 | item5 | <---- j=2 => i=6
| 2 | 7 | item6 | <---- j=2 => i=7
| 3 | 8 | item7 |
+---+---+------+

View Replies !

Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary)

I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's.
I need to do this several times and have tried it several ways to no avail.

Table A
---------
companyID int(10) pKey
legacyID int(10) old legacy pkey

Table B
---------
bAID int(10) pkey
companyID int(10)
legacyID int(10)

Table A has values for both companyID (unique key) and legacyID.
Table B has values for bAID (unique key) and legacyID but companyID is empty.

I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship.

I need a query that will update ALL rows.

View Replies !

Multi Table Queries Returning 0 Rows With DSN-less Connection ViaOLEDB
I'm working on a Windows client program in C++, using Qt and OleDbPro (for database access). Everything was working great until I had to use a query for the first time in the code that pulled data from two tables. The query executes, but returns 0 rows. I verified that the query should return rows by running the query both through another client using an ODBC DSN, and using phpMyAdmin; both times, I got the result I expected.

I'm using the following connection string:
Driver={MySQL ODBC 3.51
Driver};server=2ksvr;database=mydb;uid=user;pwd=pa sswd;option=16386

I tried with and without using the JOIN format, someone suggested some ODBC drivers don't work well with JOINs, but didn't make any difference

So, the question I have is, is there something special I have to do for a DSN-less connection using an ODBC provider?

View Replies !

Select Based On Two Rows In Mapping Table
I have a many to many relationship between a resource and tag:

resource - 1:M - tagged_resource - M:1 - tag

So a resource can have many tags and a tag can belong to many resources. The data would look like this:

RESOURCE TABLE
id: 1
name: resource1

TAG TABLE
id: 1
tag: new
id: 2
tag: tested

TAGGED_RESOURCE
resourceID: 1
tagID: 1
resourceID: 1
tagID: 2

How can I select the single row from the RESOURCE TABLE which has the tags 'new' and 'tested' in the TAGGED_RESOURCE TABLE?

I have tried using 'IN' but it acts like an OR operator rather than AND.

View Replies !

Getting Number Of Rows From Left Join Table
PHP

SELECT bulletin.id AS bid, bulletin.bulletinname AS bname, bulletin.bulletindesc AS bdesc, bulletin_post.id AS postid, DATE_FORMAT(bulletin_post.postingtime,'%M %d, %Y %l:%i%p') AS postdate, bulletin_post.bulletintitle AS ptitle, member.screenname AS mname, member.id AS posterid FROM bulletin LEFT OUTER JOIN bulletin_post ON bulletin.id = bulletin_post.bulletinid AND bulletin_post.postingtime = (SELECT MAX(postingtime) FROM bulletin_post WHERE bulletinid = bid) LEFT JOIN member ON member.id = bulletin_post.memberid WHERE bulletin.active = 1 ORDER BY bulletin.bulletinname ASC

My question is how would I get the count of rows if any from the bulletin_post table?

View Replies !

Order By :: Different Criteria For Different Rows
If i have a table with say 50 rows, is it possible to display the data of the first 20 rows
by, as an example...ORDER BY age and the remaining 30 rows by, as an example...ORDER BY name

View Replies !

LEFT JOIN Returning NULL Values
I am trying to perform a LEFT JOIN on a table which may or may not have
matching rows.

If there are no matching rows, it returns NULL's for all the missing fields.

Is there anyway of returning the default values for that table instead of
NULL's, in a portable way?

My query currently looks like this:

SELECT i_product.name, i_product.price, `i_tax-rate`.rate
FROM i_product
LEFT JOIN `i_tax-rate` ON (`i_tax-rate`.`tax-rate-id` =
i_product.`tax-band-id`)
WHERE i_product.id = 123';

View Replies !

Filter Out Rows That Match 2 Criteria
There's a snippet of the table of my data. What I'm trying to do is filter out row 2.

If ID="unknown" <0000000> and lastAPP=s, then ignore it. I tried:

SELECT * from... WHERE ID!='"unknown <000000>' AND lastAPP!='s'..;

But it filters out anything with an 's' or 'unknown'.

View Replies !

Search Criteria From Multiple Rows?
I'm trying to create a php search script that will return items that meet certain criteria .... I'm not too sure how to explain this but let me try me best ...

View Replies !

Date Based Left Join
I have two tables that I want to join to find groups that do NOT have transactions for a particular date range. The left hand table holds the groups. The right hand table holds transactions which have an associated date. The link between the tables requires 2 fields, group_id and storecode, and there is a one-to-many releationship between groups and transactions.

I essentially want the left join between these two queries:

select g.name, g.group_id, g.storecode from groups g

select t.group_id, t.storecode, count(t.trans_id) from transactions t
where t.proc_date >= '2005-10-01'
group by t.group_id, t.storecode

Unfortunately, when I join them together the date seems to be interfering with the join. Naturally, you can't check the date of a transaction that doesn't exist, and there are transactions for the groups somewhere in the transactions table.

Joined like this, they return everything I want except the groups with 0 transactions for the date range:

select g.gname, g.group_id, g.storecode, count(t.trans_id) from groups g left join transactions t using (group_id, storecode)
where t.proc_date >= '2005-10-01' or (t.group_id is null or t.storecode is null)
group by t.group_id, t.storecode

I could easily add a HAVING clause at the end to get the rows with count = 0, but it is only returning one zero row, when I know there should be about 100.

Anyone have any suggestions, or know what I'm missing?

View Replies !

Deleting Rows That Meet Criteria Involving Two Tables
I need to delete all rows from Table1 for which some criteria also involving Table2 are met. I tried something like DELETE FROM Table1 WHERE id in ( SELECT id from Table1, Table2 WHERE Table1.this = Table2.that). Unfortunately this gives me an error suggesting that I can't modify a table that's in the subquery.

I have a workaround involving a temporary table but would prefer a one-shot query to achieve this result.

View Replies !

Use Table Column Csv As Criteria For IN()
Currently, I am using two queries to determine the results, but I don't see a reason why it can't be combined in to a single query. My current query selects the column, then just plops that column into the second query that gets the results using products.id IN ($column).

Here's the query I thought would work, but it only matches the first item in the csv: .....

View Replies !

Returning Of Delete Rows
Is there any way to return deleted rows?
I mean I've delete a rows in mysql5 with " delete from ... " query is there any way to restore those rows?

View Replies !

Returning All Rows In Ruby
fetch_row() returns one row at a time.

View Replies !

Sorting A Table According To User Criteria.
I was looking for a some kind of simple solution to this issue. The application requires that certain tables should be showed ordered by the user criteria, for example categories. the user will insert a new category and then he could move up or down the item to the position he wants to show it.

In order to do this, I was thinking to add a column, let's call it 'sort_id' this column will save the position of the record. (this is the best i could come up with, I would love any suggestions). This might create some concurrency problems, but since this is an administration module, I don't think more than one would modify the same table at the same time. Anyway, I couldn't find an easy way to insert a new record in the table with the last position + 1.

View Replies !

Returning Limited Sequential Rows
I have a query that returns to me all the seats that are available,
which is great but the ideal solution would be to return X number of
seats based on a seatnum (Integer).

For example, if seats 10, 11, 12 and 15-20 are available and I need 4
of them, I should get back seats 15,16,17,18.

I'm pretty sure that some sort of IF statement with a variable in the
query is the answer, but I can't seem to push through on it.

NOTE: seatnum is NOT a primary key and is not autoincrement. An
autoincrement col wouldn't work for this project as the sections, rows
and seats might get edited as things change in the hall. So I'm doing
the seat numbering myself.

If anyone could give me shove in the right direction, I'd appreciate
it. After many Google searches for "sequential" rows or records and
the like, all I get are people wanting to return record counts.

View Replies !

MySQL Returning Redundant Rows
I have 36 records in three tables, each row is related to one row in each table. Up until I add the last where condition (staff_descr.certs LIKE ...), it's fine. But with that last condition, it suddenly returns 108 rows, each row repeated 3 or 4 times.

PHP Code:

SELECT staff.obj_id , staff.timestamp , staff.full_name , staff.city , staff.state FROM `staff` , `staff_descr` LEFT JOIN `user` ON user.username = staff.username WHERE user.is_confirmed = 1 && staff_descr.certs LIKE '%Basic Lifeguarding%'

View Replies !

Simple Select Returning More Than One Rows
I would like to ask if it's possible to do a select 1,2,3 and to be returned 3 rows and 1 column with values 1 for the first row,2 for the second etc. select 1,2,3 returns me a row with 3 columns.

View Replies !

Returning Rows Older Than 6 Months.
I have a DB packed with orders, and am trying to do a query that returns all the results that are older than 6 months, I wrote a query to find all results between now and six months ago but don't know how to return results older than six months and nothing more..

the date part of the query i had was...

DATE_SUB(CURDATE(),INTERVAL 6 MONTH) <= orderDat

View Replies !

Returning Multiple Rows From A Stored Procedure..
I have a country table with code and name columns and create a stored
procedure 'get_countries()' but have no idea what is the syntax to
return multiple rows.

I have searched the newsgroups and understand that the solution is to
use cursors. Searching for the mySQL solution is to no avail.

View Replies !

Select Users That Match Multiple Criteria From The Same Table?
tbl_training
id--user_id--training_id

Now I have a larger query going on, so this is part of my subquery where I'm trying to get all the user_ids that match multiple training_ids.

So for instance if the user selects training_id #1,#4,#6

I want to select all user_ids that have training_ids #1,#4, and #6.

View Replies !

Return Different Columns As Rows / 2 Selects Returning 1 Resultset
I have an existing table that (highly simplified) looks like this:

item_id | order_id
1 | 4
2 | 5
3 | 6

but I want to write a query that will make it output its data like this:

columnName | value
item_id | 1
item_id | 2
item_id | 3
order_id | 4
order_id | 5
order_id | 6

(theres actually 5 id rows not 2 in my table, but im sure if I get it working for 2 it wont be much to make it work for 5)

I am then going to use that table to join to another one, so I need this to be done inside mysql in one resultset (not afterwards in a programming loop)

I feel like it should be _something_ like running these 2 queries but getting all the results in one resultset rather than 2
(SELECT 'item_id' as columnName, item_id as value FROM table)
(SELECT 'order_id' as columnName, order_id as value FROM table)

I'll also be ordering by a date field so yeah... needs to be one resultset.

View Replies !

Subquery Result Returning Many Rows As Single String
Is it possible to return multiple rows from a select statement as a single string?

Example:

View Replies !

Rows Based On First Letter
I'm trying to figure out the fastest/best way to return rows based on the first letter of the title column in my table. For example, I want to get all the article titles that start with the letter "a".
Is there any difference between the two select statements? Any reason to use one over the other? Memory? Speed?

SELECT title FROM table WHERE LEFT(title, 1) ='a';
or
SELECT title FROM table WHERE title like 'a%';

View Replies !

Filtering The Rows Based On Its Timestamp
I'm trying to write a backup script that takes the last modified rows of a table in x days. I can do it if I have a timestamp column for every table in the database, but my aim is to write a script for general use as anyone can use it with his/her database without the need to add that column.

This will be possible if there a property gives the last modified timestamp of a row.

does anybody has any way to reach that target?

View Replies !

Determine The Size Of A Set Of Rows Based On A Where Clause
I have tried looking for a solution to this problem but no luck. The
thing is, in my system, I have a set of users who have a 'quota'
limiting their usage. They can create n number of tables and put m
number of rows in it, but at the end, their 'usage' can not exceed,
lets say, x MB. My problem is, how to I evaluate the space being used,
given the fact that I can pull all data for a user by including a user
ID in the where clause. In other words, I need to find size of the
result set containing all user's rows.

I am using mysql jdbc driver and not sure if there
is any API that deals with the physical size of the resultset.
resultset.getFetchSize() talks in terms of rows v/s the actual space
they take.

View Replies !

Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?
I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's.

$sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )";
return $sql2;
$res = mysql_query($sql2, $connDB) or die(mysql_error());
$row = mysql_fetch_assoc($res);
based on a test zip code gives a result like

SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 )
the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table.

In my db table lon and lat are decimal(10,6) type with a default value of 0.000000

View Replies !

Selecting Multiple Rows In The Based Column With AND Condition
I am facing a very big headache for a few days. I have looked through the forum for solution but found no satisfactory answer. Essentially my problem is the same as the one found in another thread, hence I use some parts of that thread to illustrate my point. Assuming the following scenario: ...

View Replies !

LEFT JOIN Produces Extra Rows
The `suggestions` table contains 2,265 rows. The `ALL_PHS_SCHEDULES` table contains over 22,000 rows. I have done my homework and I read that the Left Join return ALL rows from the first table and the matching ones from the other table. I am expecting exactly 2,265 but the query is returning 2,734 rows.

Quote: SELECT *
FROM `suggestions`
LEFT JOIN `ALL_PHS_SCHEDULES` ON `suggestions`.course_number = `ALL_PHS_SCHEDULES`.course_number
AND `suggestions`.section = `ALL_PHS_SCHEDULES`.section AND `suggestions`.id = `ALL_PHS_SCHEDULES`.id
ORDER BY `ALL_PHS_SCHEDULES`.`course_number` , `ALL_PHS_SCHEDULES`.section ASC

View Replies !

Selecting Data Form One Table That Is Based On An Entry In Another Table
This is the code I am using:

***********
SELECT co_name,city,country,logo_small FROM $info WHERE $info.co_name=$categories.co_name AND WHERE $categories.everyday_wear='y' ORDER BY co_name ASC
***********

I am using a while loop in php to loop through all of the records but no records are being displayed. What am I missing? Do I need to use a JOIN statement?

View Replies !

Updating Table Based Upon Matching Field In Second Table
I have a database of books that was originally created as a flat file.
Each record has a number of fields, including the authors name. I'm
trying to convert the database to something a little more efficient.
I've created a new table (called Authors) of unique authors names and
assigned each one a unique ID. I've added a new field in the original
table (called Books) for the author's ID. Now, I need to update the
original table with the author ID from the Author's table.

Something like this:

UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName =
Authors.AuthorName

This obviously doesn't work. Any assistance on how to forumulate this
query (or, if I'm headed down the wrong path, the correct way to do this
operation) greatly appreciated.

View Replies !

How Do I Generate Results Based On Totals Of Another Table But For 1st Table?
This is what I want to do:

1- I have Two tables: polls_created and votes

2- Table polls_created is like:

poll_id
owner
poll_subject

3- Table votes has the votes issued for a given poll, like this:

vote_id
poll_id
vote
vote_date

So what I need to do is to look at these 2 Tables and generate results based on values of these 2 tables.

How do I then generate this result:

MySQL Code:
SELECT poll_id, owner, poll_subject, COUNT(vote_id) AS number_of_votes FROM polls_created, votes
"sorted by polls that have gotten most number of Votes"

Of course "sorted by polls that have gotten most number of Votes" is not real MySQL

View Replies !

Returning Values That Are In One Table, But Not In Another
I'm looking for a query to return values which occur in one table, but not in the other.

Say we have two tables (table_1, table_2) each with a column titled "appointments".

I want a list of results for appointments which are in table_1, but not in table_2

I have tried the following, but all I get is a huge list of duplicate appointments which occur in both tables:

SELECT table_1.appointments
FROM table_1, table_2
WHERE table_1.appointments<>table_2.appointments;

I've also tried 'NOT IN' for this but the result was just the same.

View Replies !

Update Table Based On Email In Another Table
I'm having trouble updating the entries from a table. The situation is as follows:

Customer table contains:
1) customer_email_address
2) customer_newsletter (value 0 or 1)

Visitor table contains:
1) email

The visitor table contains email addresses from customers that have signed up through another system.

I would like to update the customer table and set customer_newsletter to 1 where customer_email_address matches email from the visitor table.

View Replies !

Selecting From A Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables,

Table A- has the field "id" which is the primary key
Table B- has the field "id" which is the primary key

Select * from tableA where Table A.id is not in tableB.id

How can i form a statement that will accomplish the above.

View Replies !

Returning 1 Tables Results From Two Table Query
I am executing a query which finds a criteria from certain records in table A and returns all values from table B if the criteria in table A is true. I want to return only the data from table B but can't find an easy way to do that? E.g the MySQL 'From' expression contains both tables.

View Replies !

Returning All Unique Values From A Table Field
I wrote a select that works fine but I am looking at it and thinking that the query might require mysql to scan EVERY row before giving me my answer.

Lets pretend I have a table with fruits and want to return all the available colors of those fruits that I currently have in the database.

Is this an efficient or horribly inefficient statement?

"select color from fruits group by color"

It seems to return what I want:

fruits:
------
blue
green
yellow
...

I am just wondering if it goes through EVERY row and groups them?
If I have the table indexed by color am I allright?

View Replies !

UPDATE Based On Value In Another Table
How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like:

UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN
table2.ID;

The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go there.

View Replies !

Update Based On Another Table
Is it possible to do an update in MYSQL based on another table? I have version 3.23 and when I try to run this statement:

UPDATE ApplicationTbl
INNER JOIN AcademicTbl
ON ApplicationTbl.CampusID = AcademicTbl.CampusID
AND ApplicationTbl.Application_Period = AcademicTbl.Application_Period
SET ApplicationTbl.App_Status = 'Qualified'
WHERE AcademicTbl.Sem_OnCampus >= '1'
AND AcademicTbl.GPA >= '2.4'
AND AcademicTbl.Judicial_Sanction IS NULL

It keeps saying its wrong. Even though I know its not

View Replies !

Selecting From One Table Based On Another?
I have a table for products

and another table for the type of product it is like this:

I need this is be in a seperate table because one product can be several different types like ring,band,wedding,three-stone,etc.

but how can I then select a product based on criteria from the types table

say I wanted to select all products that are type = "Band" and type != "Fancy" ?

View Replies !

Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables,

Table A- has the field "id" which is the primary key
Table B- has the field "id" which is the primary key

Select * from tableA where Table A.id is not in tableB.id

How can i form a statement that will accomplish the above.

View Replies !