?
Solved

Query Syntax

Posted on 2013-06-10
8
Medium Priority
?
252 Views
Last Modified: 2013-06-10
I am trying to find a way to use a variable within a query but grabbing the variable and using the variable from within the same query.  I have a DB that stores Event ID's and Event Dates in two different tables.  I need the Event ID to find the Event Date.  If I do two queries I cant order the queries by date because that query is not inside of the original WHILE statement.  I am trying to do something like this:
(The ID is the part I am trying to figure out.)

$q ="SELECT magentocatalog_category_entity.entity_id AS id,
magentocatalog_category_entity_datetime.value AS end_date
FROM magentocatalog_category_entity,
magentocatalog_category_entity_datetime
WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id='id'
ORDER BY end_date ASC";

Open in new window

0
Comment
Question by:rgranlund
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 4

Expert Comment

by:BAKADY
ID: 39235704
how looks your tables??? some data example...

is this code correct???
...WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id='id'...

Open in new window

or you mean :
...WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id=magentocatalog_category_entity.parent_id...

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39235740
Maybe something like this?

$id = "175";

$q =
"
SELECT 
   magentocatalog_category_entity.entity_id AS id,
   magentocatalog_category_entity_datetime.value AS end_date
FROM 
   magentocatalog_category_entity,
   magentocatalog_category_entity_datetime
WHERE 
   magentocatalog_category_entity.parent_id='$id'
AND 
   magentocatalog_category_entity_datetime.entity_id='$id'
ORDER BY 
   end_date ASC
"
;

Open in new window

0
 
LVL 7

Author Comment

by:rgranlund
ID: 39235746
I don't know if it is correct.  Well, I know it is not correct cause it does not work,  But what I am trying to write is closer to your second example:

WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id='magentocatalog_category_entity.entity_id'

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Expert Comment

by:BAKADY
ID: 39235762
WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id=magentocatalog_category_entity.entity_id

Open in new window

maybe without quotes???
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39235765
It would be without the quotes if your intent is to match a numeric data element.
0
 
LVL 4

Accepted Solution

by:
BAKADY earned 2000 total points
ID: 39235789
It would be without the quotes if your intent is to match a numeric data element.

WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id=magentocatalog_category_entity.entity_id

Open in new window


Table A: magentocatalog_category_entity
Table B: magentocatalog_category_entity_datetime

if TableA.entity_id = TableB.entity_id not need to be numeric, maybe they need to be of the same datatype, this is field's values comparison. Maybe i'm wrong and the result isn't what he wants, but that is what i mean...
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39235791
some data from your tables will be helpfully, as a example...
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39235879
Line 2 of this snippet is the one that has the quotes out of place.

WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id='magentocatalog_category_entity.entity_id'

Open in new window

It is OK to quote number strings in a query.  It is not necessary to do so.  But if you put the quotes around the wrong things, it can be assured that failure is not left to chance!

;-)

~Ray
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question