SQL Query Logic 2

Question

I have already asked here Query Logic SQL and didn't get a response(could be with the presentation of my data) I really hope someone can take a look at this and provide their input on how to get this done. would highly appreciate any help.

I have a sql table data that looks like

users table

id | name     |
_______________
 1 | John     |
 2 | Mary     |
 3 | Charles  |
 4 | Mike     |
 5 | Lucy     |
 6 | Debbie   |

pairing table:

main_id | pair_id   | 
_____________________
  1     |   2       |      
  1     |   3       |   
  2     |   4       |   
  2     |   5       |   
  3     |   6       |     
  3     |   1       |

when rendering output to user, my html table would look like this, using group_by groupconcat in sql.

main_name | paired_names
  John    |   Mary, Charles
  Mary    |   Mike, Lucy
  Charles |   Debbie, John

Now,the problem is during searching(wildcard search)

say the user will input "Charles"...

The output needs to be:

main_name | paired_names
  John    |   Mary, Charles
  Charles |   Debbie, John

since its going to do a wildcard search in both columns in the pair table.

For now, what i do is i manipulate the result set from the database but this has pagination and been advised that it will affect system performance if i query all data then manipulate after.

I hope someone will be kind enough to provide their advice on how to get this done.

I can provide further details if needed.

Looking forward to hear from you.

here it is: select user_1.name, group_concat(user_2.name SEPARATOR “, “) as paired_names from pairing_table left join users as user_1 on pairing_table.main_id = users.id left join users as user_2 on pairing_table.paid_id = users.id group_by pairing_table.main_id but how do i add a query to get name from both this columns?since sql doesn't recognize alias in where — derping, Nov 26 '14 at 03:42

Rafiqul Islam · Answer 1 · 2014-11-25T11:11:58.060

0

Checks the fiddle

SELECT users.id,GROUP_CONCAT(pair_id) FROM (SELECT users.id,
 users.name,pairing.main_id,pairing.pair_id 
    FROM users, pairing 
 WHERE pairing.main_id=users.id

 ) AS t1 JOIN users ON users.id=t1.id GROUP BY  users.id;

edited Nov 25 '14 at 11:11

answered Nov 25 '14 at 11:06

Rafiqul Islam

1,636
1
12
25

Hi, here is the query i built: here it is: select user_1.name, group_concat(user_2.name SEPARATOR “, “) as paired_names from pairing_table left join users as user_1 on pairing_table.main_id = users.id left join users as user_2 on pairing_table.paid_id = users.id group_by pairing_table.main_id but how do i add a query to get name from both this columns?since sql doesn't recognize alias? – derping Nov 26 '14 at 03:42
@derping You can get name by php [hash map](http://stackoverflow.com/a/6841577/2151290) – Rafiqul Islam Nov 26 '14 at 03:59
@derping for further you can see [PHP:Hash Maps](http://progzoo.net/wiki/PHP:Hash_Maps) here – Rafiqul Islam Nov 26 '14 at 04:07
yes, the what i did to get this issue resolved is to manipulate the result array through php, going through the query and using strpos and push the value if the characters exist. is there away i can do it in the query itself? – derping Nov 26 '14 at 05:11

SQL Query Logic 2

1 Answers1