Please help me combine these queries


Original Query:

SELECT * FROM `tx_categories_build` WHERE `galleries` >= 20 ORDER BY `name`

But I also need to do this as well.. but in ONE query.

SELECT tag FROM `tx_categories` WHERE name LIKE 'the name column'
SELECT COUNT(*) as MOVIES FROM `tx_galleries` WHERE format='movies' AND categories LIKE '%tag%'
SELECT COUNT(*) as PICS FROM `tx_galleries` WHERE format='pictures' AND categories LIKE '%tag%'
Posted On: Wednesday 24th of October 2012 01:31:07 AM Total Views:  135
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Error in sql, please help

I have folowing sql Code: CREATE TABLE ask_user (id INT AUTO_INCREMENT, nickname VARCHAR(30), first_name VARCHAR(50), last_name VARCHAR(50), created_at DATETIME, PRIMARY KEY(id)) ENGINE = INNODB; CREATE TABLE ask_relevancy (id BIGINT AUTO_INCREMENT, answer_id BIGINT, user_id BIGINT, score BIGINT, created_at DATETIME, INDEX answer_id_idx (answer_id), PRIMARY KEY(id)) ENGINE = INNODB; CREATE TABLE ask_question (id INT AUTO_INCREMENT, user_id BIGINT, title VARCHAR(50), body VARCHAR(200), created_at DATETIME, updated_at DATETIME, INDEX user_id_idx (user_id), PRIMARY KEY(id)) ENGINE = INNODB; CREATE TABLE ask_interest (id BIGINT AUTO_INCREMENT, question_id BIGINT, user_id BIGINT, created_at DATETIME, INDEX question_id_idx (question_id), PRIMARY KEY(id)) ENGINE = INNODB; CREATE TABLE ask_answer (id INT AUTO_INCREMENT, question_id BIGINT, user_id BIGINT, body VARCHAR(200), created_at DATETIME, INDEX question_id_idx (question_id), INDEX user_id_idx (user_id), PRIMARY KEY(id)) ENGINE = INNODB; ALTER TABLE ask_relevancy ADD FOREIGN KEY (answer_id) REFERENCES ask_answer(id); ALTER TABLE ask_question ADD FOREIGN KEY (user_id) REFERENCES ask_user(id); ALTER TABLE ask_interest ADD FOREIGN KEY (question_id) REFERENCES ask_question(id); ALTER TABLE ask_answer ADD FOREIGN KEY (user_id) REFERENCES ask_user(id); ALTER TABLE ask_answer ADD FOREIGN KEY (question_id) REFERENCES ask_question(id); When I run it in phpmyqdmin, I get following error: SQL query: ALTER TABLE ask_relevancy ADD FOREIGN KEY ( answer_id ) REFERENCES ask_answer( id ) ; MySQL said: Documentation #1005 - Can't create table '.\askeet\#sql-15dc_1f9.frm' (errno: 150) Can you please tell me what I am doing wrong here
VIEWS ON THIS POST

108

Posted on:

Friday 12th October 2012
View Replies!

Mysql Query Join help please

hi, I am creating a banner advertising site script... What i need is when a visitor reach my site, find his ip address, find the country, find advertisers banner ordered for same country if they have balance credit, display the banner only if they didnt received banner impression from the same country. Advertisers id aid (advertiser ID) bannerimg bannerurl advcountry totalcredit creditused creditbalance Tracker id aid (advertiser ID) referral ip country PHP Code: $check=mysql_query("SELECTaid,bannerimg,bannerurlFROMadvertiserWHEREadvcountry==$countryANDcreditbalance
VIEWS ON THIS POST

88

Posted on:

Friday 12th October 2012
View Replies!

Help with date calculation please

The task I am trying to accomplish is to calculate a members age and then display it on a page. The member's birthdate is stored correctly within the database, however with the below code it does not calculate correctly and gives the wrong age of the member. I did not right this code it came with the script and I do not understand how to modify it to make it work, any suggestions would be much appreciated.
VIEWS ON THIS POST

85

Posted on:

Friday 12th October 2012
View Replies!

Help w/ sql LIKE condition please

I have a field in a table called Title. I have a record whose Title field contains a value of Ford Mustang. When I run a LIKE on my table such as LIKE 'Mustang' my record does not shop up. I am assuming LIKE is for an exact match. What if I want any record that has EITHER Ford or Mustang in the Title
VIEWS ON THIS POST

89

Posted on:

Friday 12th October 2012
View Replies!

Help with some join statements please

I am in the process of normalizing my database and would appreciate some help with the following. I have a database that has a table called directory. Each row in this table is a record for an auto repair shop. The ID field in this table is referenced in other tables as "shop". I have three other tables I am working with called services, warranties, and affiliations. With the guidance of Rudy I have created three more tables each with two columns. One column of each table holds the value of the shop id, the other column holds the value of service, warranty, or affiliation. So my question is how do I write a select statement to get all services OR warranties OR affiliations from their respective tables based upon the two columned table previously described. Here is my table structure.
VIEWS ON THIS POST

148

Posted on:

Friday 12th October 2012
View Replies!

Still Need help with linking strings to tables please - with diagram!

Gusy i am still struggling with this problem. I have added a diagram now to help ppl see the issue.. THe company holds a string, which i compare against the postcode table to get a match and a zone. hope this help. please help me, i will pay gold to the person who does! i am currently upto this... but its not working... $query_qryPostCodes = "SELECT ZoneID FROM postcodes WHERE PostCode LIKE SUBSTRING(" . $row_qryCompanyDetails['CompanyPostCode'] . ",1,CHAR_LENGTH(PostCode))";
VIEWS ON THIS POST

116

Posted on:

Friday 12th October 2012
View Replies!

mysql help please

Im totally new to php. mysql etc, but eager to learn. I have installed wamp from this site and now I thought I was ready to start learning, but I have a problem. When I open mysql to enter command, its just asking for password, I have just clicked enter and i get a bit of blurb but even when I enter in code to set new password, it doesnt do anything and sometimes the command box just dissapears. I think I may have something missing on installation, or Im entering code in wrong\t can anyone please help\t Im running windows 7
VIEWS ON THIS POST

135

Posted on:

Sunday 21st October 2012
View Replies!

Help with a join query please

Hi Im wondering if its possible to do what I want to do all in one query - Im guessing it is because i dont think its that complicated but its too complicated for me to figure out anytime soon. I've got two tables, equipment and services: Each equipment may have several services, and these services might be past services or upcoming services. There is a column in the services table `upcoming` which if set to 1 tells you that it is an upcoming service and if 0 a past service. I want to select all from equipment, and along with each row select the date of last service and the date of the next upcoming service. Code: table `equipment` `equipmentid`, `name`, `otherdetails` table `services` `serviceid`, `equipmentid`, `date`, `service_description`,`upcoming` Bear in mind though that a certain equipment may not have any past services or it may not have any upcoming services.
VIEWS ON THIS POST

128

Posted on:

Sunday 21st October 2012
View Replies!

Inner join help please

, Wondering if anyone could help me with a inner join query which is confusing me Code: SELECT photographic_schedule.user_id, photographic_users.name, photographic_schedule.appointment_type, photographic_schedule.live, photographic_schedule.the_date, photographic_schedule.event_begin_time, photographic_schedule.event_end_time FROM photographic_schedule INNER JOIN photographic_users ON photographic_schedule.user_id = photographic_users.user_id WHERE photographic_schedule.appointment_type =98 OR photographic_schedule.appointment_type =99 AND photographic_schedule.live =1 AND photographic_schedule.the_date = '2011-03-17' My problem being this part AND photographic_schedule.the_date = '2011-03-17'. Which it seems to ignore If I change this to any date, ie 2011-03-18, 2011-11-21 it still returns all results in my database regardless of the date
VIEWS ON THIS POST

101

Posted on:

Sunday 21st October 2012
View Replies!

query pointer please

I am querying the table of images per gallery. I want to get those of category 'a' but if there are none in that category to get those in cat 'b'. Not sure if I should use a case statement or maybe to join on the table twice looking for a non-match. maybe there is a simpler way that seems inadequate right now, to me. (bit like meself ) Code MySQL: CREATE TABLE image_galleries ( business_id int(11) NOT NULL, gallery_category varchar(64) collate utf8_unicode_ci NOT NULL default 'Main Photographic Gallery', gallery_name varchar(32) collate utf8_unicode_ci NOT NULL default '', image_id int(11) NOT NULL, sequence_number int(11) default NULL, active tinyint(4) NOT NULL default '0', PRIMARY KEY (business_id,gallery_category,gallery_name,image_id), KEY gallery_header_fk (business_id,gallery_name), KEY galerry_images_fk (image_id), KEY business_id (business_id,gallery_category,gallery_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; sample data: Code: business_id | gallery_cat | gallery_name | image_id | seq_no | active_io | | 170 | secondary | food | 810 | 1 | 1 | | 170 | secondary | food | 812 |NULL | 0 | | 170 | secondary | general | 812 | 1 | 1 | | 170 | secondary | general | 814 | 2 | 1 | so my query is like this right now: Code MySQL: select image_id from image_galleries AS ig where business_id = '170' and gallery_name = 'secondary' and gallery_name = 'food' and active_io = 1 order by seq_no I need to get the food gallery images but if there is none, to get the (default) general images. bazz
VIEWS ON THIS POST

92

Posted on:

Sunday 21st October 2012
View Replies!

query optimisation help please.

, I am querying as shown below. query takes 2.6 seconds and there are +/- half million records in the statistics table and 200 odd in the business_details table indexes are as shown. what need I do to make the query faster, please create statement Code MySQL: CREATE TABLE statistics ( Statistic_ID int(11) unsigned zerofill NOT NULL auto_increment , Session_ID varchar(64) , Browser varchar(256) , Country_of_Connection char(3) , Time_Into_Site datetime NOT NULL default '0000-00-00 00:00:00' , Time_of_view datetime NOT NULL default '0000-00-00 00:00:00' , Site_The_Visitor_Came_From varchar(64) , Referer varchar(128) , Domain_Viewed varchar(64) , Page_Viewed varchar(64) , `Subject` varchar(50) , `File` varchar(32) , Main_Business_ID int(11) , Nearby_Business_ID int(11) , PRIMARY KEY (Statistic_ID) , KEY Domain_Viewed (Domain_Viewed) , KEY Session_ID (Session_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; query Code MySQL: select stat.Domain_Viewed from statistics AS stat inner join business_details AS bd on bd.web_url = stat.Domain_Viewed where bd.business_id = 1 group by stat.Session_ID
VIEWS ON THIS POST

159

Posted on:

Sunday 21st October 2012
View Replies!

geo-spatial query help please.

I have a made a query which as far as I can tell, returns the data that it should. The issue I have is that it is telling me an incorrect distance. This is the join part of the query: Code MySQL: select business_id, sqrt( ( latitude_miles - 54.6665 )^2 + ( longitude_mpd * (longitude - 5.66341 ))^2 ) as distance from address WHERE 1 ORDER BY distance; I can't see anything wrong there so, maybe my statement which inserted the values for latitude_miles and longitude_mpd was wrong Code MySQL: UPDATE address SET latitude_miles = 69.1 * latitude, longitude_mpd = 69.1 * COS(latitude * 3.14159265 / 180) any pointers are welcome. bazz
VIEWS ON THIS POST

111

Posted on:

Sunday 21st October 2012
View Replies!

Help with a query please

I have three tables 1) products w/ productID 2) catprod_rel with categoryID and productID as FKs 3) secprod_rel with secondarycategoryID and productID as FKs I'm looking for specific products that fall under a certain category as well as a subcategory. So how would I join two tables with one in a query when I already know the categoryID and secondarycategoryID
VIEWS ON THIS POST

94

Posted on:

Sunday 21st October 2012
View Replies!

Need Help with INSERT query please

I have a table that has a unique index, int initiator, int target and a tinyint field called ok. I want that when I insert a set of data, not to have a redundant data in the table. The following two condition define redundancy for my case: case one: new data: initiator = 1, target = 5 existing: initiator = 1, target = 5 in this case I want to simply have the database ignore the insert. case two: new data: initiator = 1, target = 5 existing: initiator = 5, target = 1 in this case, I want the database to set the field "ok" to 1. Do you know any tricks how to accomplish this in a query instead of handling it at PHP level which might add a couple of more queries...
VIEWS ON THIS POST

81

Posted on:

Sunday 21st October 2012
View Replies!

Time query - please help?

Im completely baffled by a query that I thought I had working yesterday, but I cant get it working today. Heres the screnario: Im publishing a story to the web and I dont wont it to be published live until: - The current date (type: date) is less or equal to now - The current time (type: time) is less or equal to now So heres my query: Code: select * from cms_stories WHERE section = 'news' AND published_web_date
VIEWS ON THIS POST

212

Posted on:

Monday 22nd October 2012
View Replies!

Sql Error Message..please Help!!

Update Customer Set InactiveDate = sysdate, CustomerStatus = 'I', EmployeeID = 1, --assuming this employee has marked him inactive InactiveReason = 'Bill Not Paid' WHERE CustomerID IN( Select c.CustomerID FROM Customer c, Subscription s, Billing b WHERE c.CustomerID = s.CustomerID AND b.SubscriptionID = s.SubscriptionID AND b.BillingPaidDate is null AND b.BillingDueDate < (select sysdate - 30 from dual) ); I am getting error message in sql "CUSTOMER ID" invalid identifier please help me where i am wrong.
VIEWS ON THIS POST

161

Posted on:

Wednesday 24th October 2012
View Replies!

Help with quotations marks please?

I have a form that allows a user to enter text the problem is if they enter quotation marks because they want them displayed in the ad it does not insert, so how do I allow users to enter quotation marks and then print these quotation marks as well in the ad.
VIEWS ON THIS POST

158

Posted on:

Wednesday 24th October 2012
View Replies!

Sort tables/columns help please

can someone please tell me how i would do the following: I have a music website with a top ten charts page. Currently the top artist is displayed by highest rating. I want to remove the rateing system and show top artists by how many listeners (frineds) they have. So the top artists would be the artists with the most listenrs(friends) added to there profile. the rating colum is in a seperate table called users or user_profile users_profile.mid=users.id order by users.rating desc limit $limit"); My members friends that have been added to their profile are stored in user_friends table with the columns... id, *mid, *fid and more that don't matter. mid is the id of the account that hold the friends and the "fid" is the id of the friends approved to add in the account. so to be on the top of the charts first it needs to find the artist (*mid) and than sort anddisplay the artists with the most friends (*fid). this mostly makes no snese to me so I hope you can figure out what I am getting at and help me.
VIEWS ON THIS POST

140

Posted on:

Wednesday 24th October 2012
View Replies!

Help please!!!

What does this mean I can't get on a site because I keep getting this message when I try to go to the page. Table './scaadb2/smf_members' was created with a different version of MySQL and cannot be read
VIEWS ON THIS POST

247

Posted on:

Wednesday 24th October 2012
View Replies!

Function in query not working, help please

i have a queries: select num, DATE_FORMAT('dat','%d') AS datval FROM tblname. select num, MONTHNAME('dat','%d') AS datval FROM tblname. there are values in the fields dat. dat is a date type field. the result of datval is null.
VIEWS ON THIS POST

130

Posted on:

Wednesday 24th October 2012
View Replies!