[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Query Syntax

Posted on 2013-06-10
8
Medium Priority
?
254 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

649 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