Posts by smantscheff

If a SELECT statement names multiple tables in the FROM clause with the names separated by commas, MySQL performs a full join.

At least on of your weekday tables contains no data, therefore the result join contains no rows.

And the database design is horrendous - having one table for each weekday instead of one table with the weekday as a column is contrary to very basic design principles.

Prepare and show a complete test case, with CREATE TABLE statements and INSERT INTO statements. Then apply your query to this test case and, if it does not work, show it to us.

Show some test data.

Presumably it's

select child.cat_name, child.cat_desc, parent.cat_name
from mytable child, mytable parent
where child.pk_cat_id = parent.fk_cat_id

Do not use PHP until you know how to code your MySQL queries.
List all tables which you need in the FROM clause and describe their relation in the WHERE condition - which fields have to match in one record?

select ... (desired fields here)
from tbllabchem, tbldoctor_info, tblpatient_info
where MR_No = RelationMR_No and ID_No = RelationDoc_ID

Consider this:

drop table if exists mytable;
CREATE TABLE mytable (
     id int, name char(20), parentid int, position int, rating int
    );
INSERT INTO mytable VALUES
(1, 'mike', 1, 1, 6),
(2, 'dave', 1, 2, 5),
(3, 'paul', 1, 2, 7),
(4, 'john', 1, 2, 3),
(5, 'mick', 5, 1, 8),
(6, 'owen', 5, 2, 2),
(7, 'rick', 5, 2, 9),
(8, 'jaye', 5, 2, 3);

 select t1.*,t2.*, parents.* from mytable t1
        left join mytable t2
        on t1.parentid = t2.parentid and t1.rating > t2.rating
        join mytable parents
        on parents.id = t1.parentid
        where parents.name rlike 'mike|mick'

+------+------+----------+----------+--------+------+------+----------+----------+--------+------+------+----------+----------+--------+
| id   | name | parentid | position | rating | id   | name | parentid | position | rating | id   | name | parentid | position | rating |
+------+------+----------+----------+--------+------+------+----------+----------+--------+------+------+----------+----------+--------+
|    1 | mike |        1 |        1 |      6 |    2 | dave |        1 |        2 |      5 |    1 | mike |        1 |        1 |      6 |
|    1 | mike |        1 |        1 |      6 |    4 | john |        1 |        2 |      3 |    1 | mike |        1 |        1 |      6 |
|    2 | dave |        1 |        2 |      5 |    4 | john |        1 |        2 |      3 |    1 | mike |        1 |        1 |      6 |
|    3 | paul |        1 |        2 |      7 |    1 | mike |        1 |        1 |      6 |    1 | mike |        1 | …

$query = "SELECT customer_id
FROM discounts
WHERE customer_id='{$customer_id}'";

This is tautological nonsense.

It is not clear how you deal with recurring customers. Do you give a discount for every 8.000 units? Or every time a customer buys something? Or only during a fixed period?

As I said before, if you have a function which gives a unique and reproducible dicount code for every incident and customer, you can set a unique index on the discount field and just insert all records which satisfy your discount conditions without checking for double entries. So your first task should be the design of this discount code generator.

Going around in circles.
In this test setup, you want email1 because it links to bim24. You want email3 because there has no mail yet been sent to email3. Why do you want email2?
Also, the subselect in your query still does not make sense. It leads to a tautology.
The subselect selects all from sent_table where cusid='bim24' . Because this select is right table in the left join, all right table rows have either cusid='bim24' or isnull(cusid) . So the subsequent Where clause WHERE cusid IS NULL OR cusid='BIM24' in effect selects all rows in the right table, and, because of the left join, all in the left table too. So you are just selecting all rows from main_table in a quite complicated way.

But this is exactly what my query where I introduced the ifnull function does: It selects first all records from the_main_table which do not have a matching record in sentdata, and second and additionally all records in sentdata.
Test case:

drop table if exists main_table;
create table main_table (email char(10));
insert into main_table values ('email1'),('email2'),('email3');
drop table if exists sent_table;
create table sent_table (email char(10),cusid char(10),date date );
insert into sent_table values ('email1','bim24','2012-02-01'), ('email2','bim56','2012-02-01');
select mt.email, ifnull(sd.date,'1970-01-01') as theDate, cusid
from main_table mt
left join sent_table sd
on mt.email=sd.email
where cusid is null or cusid='BIM24'
order by theDate;

Result:

+--------+------------+-------+
| email  | theDate    | cusid |
+--------+------------+-------+
| email3 | 1970-01-01 | NULL  |
| email1 | 2012-02-01 | bim24 |
+--------+------------+-------+

I do not understand any more what you're trying to get at. Could you prepare a test case with just 2 or 3 rows which shows which ones you would like to include and which not?
I also do not understand the subselect. Why not like this:

SELECT mt.recid, mt.email, sd.datetime, cusid
FROM the_main_table mt
LEFT JOIN sentdata  sd
ON mt.RECID = sd.RECID 
WHERE `cusid` = 'BIM24'
ORDER BY sd.datetime ASC
LIMIT 5000000;

This runs on my test system in 3 seconds.

Yes you can.
To be more explicit, show us the code for generating the discount code and the condition under which it should execute.
And before you code in PHP use the MySQL command line for testing until you know what you want to code.

Maybe this could be an approach:
Make a left join from the_master_table to sentdata.
For any missing rows on the right insert a date way back in the past and sort by this virtual date as well as by actual dates. That way you get all the records which are missing from the right side as well as the oldest ones from the right side.
Like in:

select mt.recid, mt.email, ifnull(sd.datetime,'1970-01-01') as theDate, cusid
from the_main_table mt
left join sentdata sd
on mt.RECID = sd.RECID 
where cusid is null or cusid='BIM24'
order by theDate 
limit 50000;

Which indexes do you have?
You don't need the doubling of select clauses.
And do not select all columns (*) - it will slow things additionally.

Along those lines:

SELECT mt.emailid, sd.datesent,MAX(sd.datesent),sd.cusid
FROM the_main_table mt
LEFT JOIN sentdata sd
ON mt.emailid=sd.emailid
AND campaign_id = 'send2'
AND bademail = 0 
AND source IN ('source1','source2') 
AND oksend = 0

GROUP BY mt.emailid  
HAVING MAX(sd.datesent) = sd.datesent
ORDER BY MAX(sd.datesent) ASC 
LIMIT 50000

Have a look at EXPLAIN <yourQuery> for both cases. Maybe you can add indexes to speed up your LEFT JOIN query.

You can move the WHERE conditions into the left join clause to exclude the undesired rows. Why don't you prepare a nice little test case with complete table structures, test data and your query for us? It might clear things up.

That's correct. In a left join all values in the right table for which there is no matching record are NULL.

Order by the fields sent ASC and datesent DESC and use the first 500 rows.

You cannot have aggregate and non-aggregate columns from the same table in your query. MySQL does a bad job in not noticing this common error.
If you want the single reserve_rec items together with the total of shares, you need two table clauses in your FROM clause. Consider this:

drop table if exists my_members;
drop table if exists my_reserve_rec;
create table my_members (mem_id integer);
create table my_reserve_rec (mem_id integer, shares integer, expire_date datetime);


insert into my_members values (1),(2);
insert into my_reserve_rec values (1,2,'2011-01-01');
insert into my_reserve_rec values (1,3,'2012-01-01');
insert into my_reserve_rec values (1,4,'2012-01-01');
insert into my_reserve_rec values (1,5,'2012-01-01');

SET @expire_date = '2012-01-01';
SELECT m.mem_id, r.shares, 
(select(sum(shares)) from my_reserve_rec as r2 
    WHERE m.mem_id=r2.mem_id AND r2.expire_date >= @expire_date
) as sum_shares
FROM my_members as m, my_reserve_rec as r
WHERE m.mem_id=r.mem_id AND r.expire_date >= @expire_date;

Maybe you might use the INSERT IGNORE statement.
Generate a unique and reproducible discount code for customers with a certain amount of payments. Create a stored function for it (here called myCodeGenerator). Make the code column a unique key (maybe in conjunction with the customer id). Then use something like

INSERT IGNORE INTO discount_codes (id_customer,code)
  SELECT id_customer, myCodeGenerator(id_customer)
  FROM payments
  GROUP BY id_customer
  WHERE SUM(payment) > 100000

SELECT *
FROM tblpatient_pass p, tblpatient_info i
where p.RelationMR_no = i.MR_no
and username='$username'

I assume that you are working with php.
To feed your values into a database, use something like the following model and feed the output to mysql:

<?php
$x = 'a:5:{i:0;a:2:{s:6:"answer";s:2:"CA";s:5:"votes";s:1:"1";}i:1;a:2:{s:6:"answer";s:4:"CIMA";s:5:"votes";s:1:"1";}i:2;a:2:{s:6:"answer";s:4:"ACCA";s:5:"votes";s:1:"0";}i:3;a:2:{s:6:"answer";s:5:"CIPFA";s:5:"votes";s:1:"1";}i:4;a:2:{s:6:"answer";s:24:"MBA(specify in comments)";s:5:"votes";s:1:"1";}}';

$a = unserialize($x);
foreach( $a as $id => $entry )
	echo "insert into mytable (id, answer, votes) values ($id,'$entry[answer]', $entry[votes]);\n";

Make sure that all parts work in the same character set - database, tables, fields, connection, html encoding, javascript encoding. The position differences most likely are due to one side counting the utf8 2-byte codes as one character, while the other side counts them as two characters.

Replace "sum" with "avg":

SELECT movies.movienum, movies.moviename, avg(reviews.ratingpoints) as ratings
FROM movies INNER JOIN reviews 
ON movies.movienum = reviews.movienum 
WHERE reviews.ratingpoints > 0
GROUP BY movienum

SELECT DISTINCT * FROM table WHERE date_add(`Date_Column`, interval 14 day) < now()

If you mix aggregate and non-aggregate functions in MySQL you get invalid results.
What you want is either:

select * from test
where (grp_code, root_desc) in
(select grp_code, root_desc from test group by grp_code, root_desc having count(grp_code) > 1)

+------------+----------+-----------+
| RIGHT_CODE | GRP_CODE | ROOT_DESC |
+------------+----------+-----------+
|          1 |        1 | 000001#   |
|         28 |        1 | 000001#   |
+------------+----------+-----------+

or

select * from test
where right_code in 
(select right_code from test
where (grp_code, root_desc) in
(select grp_code, root_desc from test group by grp_code, root_desc having count(grp_code) > 1)
);

+------------+----------+----------------+
| RIGHT_CODE | GRP_CODE | ROOT_DESC      |
+------------+----------+----------------+
|          1 |        1 | 000001#        |
|         28 |        1 | 000001#        |
|         28 |        2 | 000001#000001# |
+------------+----------+----------------+

Show some test data.
For using the IN clause you definitely don't need the group_concat function.

Get rid of the quote marks.

select 1 in (concat("'","1","'")); 
-- result: false
select 1 in (concat("'1'")); 
-- result: false
select (1 in ('1'));
-- result: true

And you don't need the group_concat neither:

select * from table_name where RIGHT_CODE IN 
(SELECT RIGHT_CODE 
 FROM table_name 
 GROUP BY `GRP_CODE` , `ROOT_DESC`
 HAVING count( `RIGHT_CODE` ) >=2
);

Alternatively sort by votes and use the first record:

select * from portfolio order by votes desc limit 1;

there is already data in the table...

If you want help, show it. Or submit a test case which shows the undesired behaviour.

drop table if exists plants;
create table plants (date char(10), plant integer, value integer);
insert into plants values 
('date1','1','10'),
('date2','1','12'),
('date3','1','15'),
('date4','2','30'),
('date5','2','34'),
('date3','2','43');
select p1.date, p1.plant, value from plants p1 where p1.date = (select max(date) from plants where plant=p1.plant group by plant ) 
;

Show some test data (in form of INSERT statements) which should give a non-empty result.

Look for an mysql primer which explains the uses of the INSERT, UPDATE and DELETE stamements to you. For specific help, post the relevant table structures you're using.

Oh, that looks promising. Thanks for sharing.

A cleaner table structure would have made this easier.
I'd propose to have a separate position table as a n:1 child table of members which records the positions:

create table positions (mem_id integer not null, position enum(1,2,3,4,5,6,7), foreign key (mem_id) references members (mem_id));

Then you could state your query as:

select mem_id from members where not (mem_id in( select (mem_id from positions));

or

select m.mem_id from members m left join positions p on m.mem_id=p.mem_id where p.mem_id is null;

If your idea is to replicate the development steps on the production server by paralleling database statements, then I too deem this as highly insecure. Your development path should instead include a routine which runs all necessary database commands against the production database after they have been thoroughly tested in the development environment. Otherwise you wouldn't need a development environment at all and could develop directly on the production server - bugs and downtime included.
Still, to answer your question, you could install the database on the production server as db2 with the same credentials as db1 and then duplicate your transactions.

I cant do this if they are on different servers?

AFAIK, no.

If not (which would slow things down alot....), any way I can emulate it in a Java servlet at least?

Slow down how many things? How many transactions per second do you expect? Why can't you use standard mysql replication? And as I said,

execute the DML statements from your scripting environment by use of transactions which you rollback if one command fails

If you do it with Java or PHP or whatever, does not matter. You just have two connections to which you feed the same statements.

Yes you can, but only if db1 and db2 share the same connection parameters: are on the same server and have the same user credentials (username, password).

I don't think there is an API or function to connect to an external database from within a stored procedure.
You could alternatively set up a complete database replication or execute the DML statements from your scripting environment by use of transactions which you rollback if one command fails.

Oracle bought MySQL to get rid of the free competitor. That may suffice to prove that MySQL in standard situations (like running a website) can do anything which Oracle can, only for free. If you want the bleeding edge, try MariaDB instead.

1. Don't use PHP to learn MySQL. Install the mysql command line client instead. Otherwise you will have a hard time to tell apart mysql and php mistakes and errors.
2. Im mysql you assign a value using the INSERT and UPDATE commands.
To insert key/value pairs in table1, use

INSERT INTO table1 (staff_id, staff_role) VALUES (0,'corr'),(1,'princi'),(3,'staff');

3. Don't use abbreviations. What is "corr", what is "princi", and who will know it when he has to revise your code in years to come?
4. staff_id and staff_role seem to be redundant in one table. If one staff_id point to exactly one staff_role, then staff_role should be eliminated from table1.
5. When you use the ENUM data type there is no need for numerical codes into the values. With ENUM types you can use the literal string constants 'corr', 'princi' etc. directly in your code without bothering with numerical codes for them.

Your query tries to compare the equality of a single value with a whole set. Try instead:

SELECT * FROM usergroups u, links l where u.`user_id` = '1' and find_in_set(u.group_id,l.group_id);

As the error message asks:

Is there a MySQL server running on the machine/port you are trying to connect to?

Install the mysql command line client and test if it can connect to your mysql server on your local machine. Or, if you're running windows, have a look at the services: is mysql active? Or, in linux, look which processes are running. Is mysqld running?

If you have a concrete question, a piece of code which doesn't work or a query which goes amiss, post it and we will try to help.

Use an enum field with literal values. Define it as
myField enum('admin','manager','staff')
instead of numerical values.

Have a look at the LOCAL option in the upload statement and make sure that upload.txt is readable by the mysql daemon (not by the mysql user).
See http://dev.mysql.com/doc/refman/5.1/en/load-data.html

You have to drop the foreign key relation before you can drop the index. Have a look at the output of

SHOW CREATE TABLE likes

It will show you the internal name of the foreign key constraint which you set up with your first statement. You have to drop this foreign key with

ALTER TABLE likes DROP FOREIGN KEY key_name

before you can drop the index on the foreign key field. InnoDB relies on indexes on all key columns in a foreign key constraint.

A comma is missing at the end of the previous line.
address TEXT NOT NULL,

I don't mind that you don't know how to tackle your problem - that's how we all got here in the first place. What upsets me is that you got an error message from mysql which tells you where the trouble with my solution is:

#1052 - Column 'mem_id' in field list is ambiguous

Your first explanation was fine already. I believe that my two attempts at a solution - if used as intended - should give you the desired result. So, did you include the clause "AND position_1.mem_id IS NULL" in your query or not?

It might help if you knew what you are doing.
I omitted the clause AND position_1.mem_id IS NULL in the SQL syntax formatting in my query above. Add it, and you will get more sensible results.
And yes, it can be done without a left join: see my example #1.
A left join contains all rows from the left table and all matching rows from the right table. It contains even rows where there is no match in the right table. For those rows any right table column expression evaluates to NULL.

Prepend the ambiguous field names with the table names:

SELECT members.mem_id, user
FROM members
LEFT JOIN position_1 ON members.mem_id = position_1.mem_id
WHERE (
bypass_purchase = 'P'
OR bypass_purchase = 'Y'

)
AND position_1.mem_id IS NULL