?
Solved

Query Syntax

Posted on 2013-06-10
8
Medium Priority
?
255 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month16 days, 3 hours left to enroll

850 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