0

I have a query i have been working on trying to get a specific set of data, join the comments in duplicate phone numbers of said data, then join separate tables based on a common field "entry_id" which also happens to be the number on the end of the word custom_ to pull up that table.

table named list and tables containing the values i want to join is custom_entry_id (with entry_id being a field in list in which i need the values of each record to replace the words in order to pull up that specific table) i need entry_id from the beginning part of my query to stick onto the end of the word custom for every value my search returns to get the fields from that custom table designated for that record. so it will have to do some sort of loop i guess? sorry like i said I am at a loss at this point

this is where i am so far:

SELECT * ,
group_concat(comments SEPARATOR '\r\n\r\n') AS comments_combined
FROM list WHERE `status` IN ("SALEA","SALE")
GROUP BY phone_number

//entry_id is included in the * as well as status // group concat combines the comments if numbers are same

i have also experimented on test data with doing a full outer join which doesnt really exist. i feel if you can solve the other part for me i can do the joining of the data with a query similar to this.

SELECT * FROM test
LEFT JOIN custom_sally ON test.num = custom_sally.num
UNION
SELECT * FROM test
RIGHT JOIN custom_sally ON test.num = custom_sally.num

i would like all of this to appear with every field from my list table in addition to all the fields in the custom_'entry_id' tables for each specific record. I am ok with values being null for records that have different custom fields. so if record 1 has custom fields after the join of hats and trousers and record 2 has socks and shoes i realize that socks and shoes for record 1 will be null and hats and trousers for record 2 will be null. i am doing all this in phpmyadmin under the SQL tab. if that is a mistake please advise as well. i am using it because ive only been working with SQl for a few months. from what i read its the rookie tool.

i might be going about this all wrong if so please advise

an example

i query list with my query i get 20,000 rows with columns like status, phone_number, comments, entry_id, name, address, so on.

now i want to join this query with custom fields in another table. the problem is the custom tables' names are all linked to the entry_id. so if entry_id is 777 then the custom table fields are custom_777

my database has over 100 custom tables with specials fields for each record depending on its entry_id.

when i query the records I don't know how to join the custom fields that are entry_id specific to the rest of my data.i will pull up some tables and data for a better example

this is the list table: this is the list table

this is the custom_"entry_id" this is the custom_"entry_id"

Full Outer Join in MySQL for info on full outer joins.

Francisco
  • 10,918
  • 6
  • 34
  • 45
lostt3
  • 1
  • 2
  • I can't figure out what you're trying to do. Can you add some sample input tables and the result you're trying to get? – Barmar Oct 21 '16 at 22:15
  • You're missing a comma between `*` and `group_concat` in the first query. – Barmar Oct 21 '16 at 22:17
  • To stick `entry_id` onto the end of the word `custom`, use `CONCAT('custom', entry_id)`. – Barmar Oct 21 '16 at 22:17
  • i added the comma where you suggested. sorry about that. i am trying to end up with table list and tables custom_"entry_id" joined but this is for about 20,000 records with all different custom_"entry_id" names – lostt3 Oct 21 '16 at 22:34
  • Still don't understand what you're trying to do. You really need to clarify it with example data. – Barmar Oct 21 '16 at 22:35
  • did the additions help? – lostt3 Oct 21 '16 at 22:42
  • No, they didn't help at all. I can't picture it without an example. – Barmar Oct 21 '16 at 22:43
  • You need to join with different column names, not column values? That can't be done in SQL, it requires generating the query dynamically. – Barmar Oct 21 '16 at 22:44
  • It's very bad table design, because it makes joining very difficult. – Barmar Oct 21 '16 at 22:45
  • i agree the table design is awful but i inherited this beast. i need to join columns to the original list table based on the values in the entry_id field. its values with the custom in from are a series of tables with extra fields specific to that lists records. – lostt3 Oct 21 '16 at 22:46
  • Since it can't be done with a join, it will have to be a loop that performs a separate dynamically-generated query for each row, so it will be horribly slow with thousands of rows. – Barmar Oct 21 '16 at 22:46
  • You won't be able to do this in phpMyAdmin. You'll need to write a stored procedure or do it in an external programming language like PHP. – Barmar Oct 21 '16 at 22:47
  • no wonder i was not able to do this. can you refer me to any documentation on it? i only need to run this query once thank goodness – lostt3 Oct 21 '16 at 22:49
  • sorry i am having trouble figuring out how to use my table to show you an example without showing sensative info. would the structure of both the list table and custom table help? – lostt3 Oct 21 '16 at 23:06
  • Replace the senstive data with dummy data. We only need 3 or 4 rows of each table to get the idea. – Barmar Oct 21 '16 at 23:11
  • i added a few images. the list table has way more fields also you see how entry_id can correspond to the numbers attached to the custom table names on the left? i need the columns form custom to attach to list based upon the entry_list. the key between the 2 tables is lead_id. which is how i would join them. – lostt3 Oct 21 '16 at 23:25
  • Don't see `entry_id` in the `list` table, do you mean `entry_list_id`? Why is it so hard to use the actual name in your question? – Barmar Oct 21 '16 at 23:34
  • Anyway, you need to look up how to write dynamic SQL. Like I said before, you can't do this in normal SQL, because table names can't come from variables. – Barmar Oct 21 '16 at 23:35

0 Answers0