0

I've created this SQL query in order to connect key variables and extract info from a database.

Firstly I used this:

SELECT * 
FROM `users` a,`field_data_field_ypefthinos` b, `field_data_field_branch_chain` c, `field_data_field_taxonomy_etairia` d, `node_revision` e
WHERE a.uid = b.field_ypefthinos_uid
AND a.uid = 1
AND b.entity_id = c.entity_id
AND d.field_taxonomy_etairia_tid = c.field_branch_chain_tid
AND d.bundle = 'kouponia'
AND d.entity_id = e.nid

and then I converted it to this in order to be able to use stmt bind_result function

SELECT
users.uid, 
field_data_field_ypefthinos.field_ypefthinos_uid, 
field_data_field_ypefthinos.entity_id,
field_data_field_branch_chain.entity_id,
field_data_field_taxonomy_etairia.field_taxonomy_etairia_tid,
field_data_field_taxonomy_etairia.bundle,
field_data_field_taxonomy_etairia.entity_id,
node_revision.nid
FROM `users`,`field_data_field_ypefthinos`, `field_data_field_branch_chain`, `field_data_field_taxonomy_etairia`, `node_revision`
WHERE users.uid = field_data_field_ypefthinos.field_ypefthinos_uid
AND users.uid = 1
AND field_data_field_ypefthinos.entity_id = field_data_field_branch_chain.entity_id
AND field_data_field_taxonomy_etairia.field_taxonomy_etairia_tid = field_data_field_branch_chain.field_branch_chain_tid
AND field_data_field_taxonomy_etairia.bundle = 'kouponia'
AND field_data_field_taxonomy_etairia.entity_id = node_revision.nid

What I'd like to do next is to:

1) use table/fields aliases in order for the code to be easier maintained as I do in the first code.

2) Important: be able to limit the results from table field_data_field_ypefthinos to LIMIT 1.

My goal is to have a code like this:

SELECT a.uid, b.field_ypefthinos_uid, b.entity_id, c.entity_id, d.field_taxonomy_etairia_tid, c.field_branch_chain_tid, d.bundle, d.entity_id
FROM `users` a,`field_data_field_ypefthinos` b LIMIT 1, `field_data_field_branch_chain` c, `field_data_field_taxonomy_etairia` d, `node_revision` e
WHERE a.uid = b.field_ypefthinos_uid
AND a.uid = 1
AND b.entity_id = c.entity_id
AND d.field_taxonomy_etairia_tid = c.field_branch_chain_tid
AND d.bundle = 'kouponia'
AND d.entity_id = e.nid
Improve this question
4
  • You state that you want to limit the results to one record from table field_data_field_ypefthinos. Before writing any code, you have to decide which record you want. Commented Jul 14, 2015 at 11:13
  • THe first record (match) that comes from field_data_field_ypefthinos, once a.uid = b.field_ypefthinos_uid has a match the script should continue Commented Jul 14, 2015 at 11:22
  • Is there a field that identifies the first record? Lowest value in primary key field? Earliest value of some date field? Commented Jul 14, 2015 at 11:54
  • That's what I am trying to achieve: SELECT b.entity_id FROM users a,field_data_field_ypefthinos b WHERE a.uid = b.field_ypefthinos_uid AND a.uid = 1 LIMIT 1 (OUTPUT: 626) SELECT e.title FROM field_data_field_branch_chain c, field_data_field_taxonomy_etairia d, node_revision e AND c.entity_id = '626' AND d.field_taxonomy_etairia_tid = c.field_branch_chain_tid AND d.bundle = 'kouponia' AND d.entity_id = e.nid Commented Jul 14, 2015 at 12:40

1 Answer 1

Reset to default
0

DISTINCT did the job, the solution:

SELECT DISTINCT e.title
FROM `users` a,`field_data_field_ypefthinos` b, `field_data_field_branch_chain` c, `field_data_field_taxonomy_etairia` d, `node_revision` e
WHERE a.uid = b.field_ypefthinos_uid
AND a.uid = 1
AND b.entity_id = c.entity_id
AND d.field_taxonomy_etairia_tid = c.field_branch_chain_tid
AND d.bundle = 'kouponia'
AND d.entity_id = e.nid
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.