0

I need help with a complex select statement in SQL. I have these two table here:

Table user:

enter image description here

Table contacts_from_user:

enter image description here

When I make a select 

SELECT name, vorname, gebdat, bezeichnung, wert 
FROM user 
    JOIN contacts ON u_id = user_u_id

I get multiple lines for one user because he has more then one contact options but I need to put it in just one line:

enter image description here

The line should be looks like this:

name, vorname, gebdat, bezeichung_1, wert_1, bezeichnung_2, wert_2.......

How ca I do this?

Thanks a lot!

  • What would you like the one line to contain? Is it name, vorname, gebdat, bezeichung_1, wert_1, bezeichnung_2, wert_2 .... bezeichnung_n, wert_n ?? Or just display the first contact option? – paqash Feb 22 '17 at 21:16
  • yes the line example from you is right! I forgot to add it to my question. I'll do this now –  Feb 22 '17 at 21:19
  • take a look at this: https://stackoverflow.com/questions/1067428/combine-multiple-child-rows-into-one-row-mysql – paqash Feb 22 '17 at 21:21
  • @paqash That looks like he need multiple data in one row but I need to do it like in my example row in the question –  Feb 22 '17 at 21:23
  • Some of the other answers look like your example row, don't they? See the answer by the Wouter guy. Hopefully you don't have more than 2-3 contact options, or that query is going to start getting very ugly. – paqash Feb 22 '17 at 21:24
  • @paqash Understand what you mean but how can I do this dynamicly? So when I decide to add another bezeichung and wert? In your given example he hast finaly two options –  Feb 22 '17 at 21:27
  • @JohannesG. you cannot dynamically extend the resulting columns of a SELECT statement; any change to the number (or type, etc...) of columns in the result must come from a change in the query itself. The most dynamic way this situation can be handled by a single query is with GROUP_CONCAT(). – Uueerdo Feb 22 '17 at 21:29
  • I don't think having this in one row is a great solution -> can you consume it in some other way if you return multiple rows, or one (two) array columns like in the linked answer? – paqash Feb 22 '17 at 21:29
  • Okay understand so if we go out from 4 contact options.. how can I do this? I'm not so good in SQL so its hard to understand for me –  Feb 22 '17 at 21:30
  • As paqash has just mentioned, is there a particular reason getting multiple result rows is causing a problem? – Uueerdo Feb 22 '17 at 21:30
  • Every additional contact option will require an additional join (and complicate identification of "first", "second", "third", etc... contact). – Uueerdo Feb 22 '17 at 21:31
  • @Uueerdo because I want to parse it in android so I want to make it a little bit easyier –  Feb 22 '17 at 21:32
  • @JohannesG. it is generally easier just to have a "previousUser" variable (or set of variables) in the loop that processes the results to detect when the "user" part changes. _(In which case, you would want U_ID in your results.)_ – Uueerdo Feb 22 '17 at 21:35
  • I've tried this here: SELECT DISTINCT username, vorname, name, gebdat, strasse, hausnr, ort, plz, einsatzort, einsatzland, berechtigung JOIN (SELECT wert FROM contacts JOIN user ON u_id = user_u_id WHERE bezeichung = 'telefon') as telefon FROM user JOIN contacts on user_u_id = u_id JOIN account on a_id = account_a_id JOIN berechtigungen on b_id = berechtigungen_b_id JOIN ort on o_id = ort_o_id JOIN einsatzgebiet on e_id = einsatzgebiet_e_id –  Feb 22 '17 at 21:39
  • But I'm getting an error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JOIN (SELECT wert FROM contacts JOIN user ON u_id = user_u_id WHERE bezeichung =' at line 1 –  Feb 22 '17 at 21:40
  • You cannot JOIN to a result field. To be perfectly clear, there is no way at all to change the number and/or type of fields in the results of a SELECT statement that does not involve changing the actual statement executed. – Uueerdo Feb 22 '17 at 21:43

1 Answers1

0

In pseudo-code, the best way to handle such scenarios is:

query = SELECT A.ID, A.stuff, B.stuff FROM A JOIN B ON A.ID = B.A_ID
results = run query
prev_A_ID = impossible_A_ID
for each result
  if prev_A_ID not equal result_A_ID
      create new A and set as current A
  add B.stuff to current A
  set prev_A_ID to result_A_ID
end for
Uueerdo
  • 15,723
  • 1
  • 16
  • 21
  • It's so hard to understand for me :( Need this for a school project tomorrow –  Feb 22 '17 at 21:46