0

I need to do a query on Publications, and also on Authors linked with Publications.

Kr1
  • 1,269
  • 2
  • 24
  • 56
  • When LEFT JOIN, put the right side table conditions in the ON clause to get true left join behavior. (When in WHERE, you get regular inner join result.) – jarlh Jul 06 '16 at 15:01
  • Hi, thanks for the answer, but how can I do that? – Kr1 Jul 06 '16 at 15:04
  • Simply move the table node's conditions from WHERE to ON. I.e `...LEFT JOIN node as author_reference_node ON author_reference_node.nid = author_reference.field_author_nid AND (node.title LIKE :title AND node.language = :language2 AND node.type = :type)...`. – jarlh Jul 06 '16 at 15:05
  • Ok I tried to do that, but in this case all Publications are returned, and there are still duplicates.. I am not sure I updated the code correctly, I am going to update my post to show you my current query. – Kr1 Jul 06 '16 at 15:12
  • Which DBMS are you using? –  Jul 06 '16 at 15:27
  • Drupal... Sorry about that :) The website I am refreshing was already built on Drupal – Kr1 Jul 06 '16 at 15:29
  • In MS-SQL there is no simple way to do this because you are looking to combine multiple rows into a single field. This can be done using some of the XML parsing and formatting commands to end up with a comma seperated list of authors in a single field but it isn't simple. – Evan Steinbrenner Jul 06 '16 at 16:25
  • Yeah I see... I thought it was much easier to achieve something like that.. – Kr1 Jul 06 '16 at 17:14

2 Answers2

0

UPDATE: I get it now, what you need is something like this: Concatenate many rows into a single text string?

-- old answer -- You can filter out multiple results with using the DISTINCT clause (http://www.w3schools.com/sql/sql_distinct.asp)

e.g. SELECT DISTINCT node.title, node.type ...

Community
  • 1
  • 1
c0delama
  • 663
  • 6
  • 9
  • 2
    With duplication, `distinct` is seldom the answer. The duplication is likely caused by an incorrect `join`, or data which doesn't meet expectations. `Distinct` removes the symptom, not the cause. – HoneyBadger Jul 06 '16 at 15:09
  • And yet, distinct is sometimes the answer when dealing with a large amount of columns and left/right joins versus inner joins. That pesky null can do all sorts of things – MageeWorld Jul 06 '16 at 15:11
  • Are you sure the the result has true duplicates? – c0delama Jul 06 '16 at 15:16
  • I would prefer to avoid the DISTINCT clause actually, and understand what is bad in my query, so I can improve my SQL queries for further developments.. – Kr1 Jul 06 '16 at 15:16
  • @MageeWorld, are you saying if things get complicated, use a shortcut instead of doing it correctly? `DISTINCT` can of course be the right way, but not before you are 100% sure the rest of the query is correct. – HoneyBadger Jul 06 '16 at 15:18
  • @nidhoeggr09, I am almost sure they are true duplicates, look at this page http://www.obersonabels.com/fr/r%C3%A9sultats-de-la-recherche?keywords=test (username: demo / pass:12345) – Kr1 Jul 06 '16 at 15:19
  • Thats a honorable way you are going :) It always depends on your use case - sometimes DISTINCT is the better (or only) option. I don't know where to login on the website you sent. – c0delama Jul 06 '16 at 15:41
  • @nidhoeggr09, I already tried the DISTINCT clause (actually at this moment the query has it), but it didn't solve the issue unfortunately. Actually there is no password on this website, just scroll down.. – Kr1 Jul 06 '16 at 15:44
  • @118218 That's not a very safe way to identify duplicates. Write a util function and print out a string with every field per row in the result. Then you can compare it properly. – c0delama Jul 06 '16 at 15:47
  • @nidhoeggr09, I had disabled the debug print just when you accessed the website, can you check again please? – Kr1 Jul 06 '16 at 15:53
  • @nidhoeggr09, If you click on stdClass, the object returned will open with all fields related with row. Please check the screenshot I just added – Kr1 Jul 06 '16 at 16:02
  • Ah ok DISTINCT will of course not work when the author_title is different in each row. Can you please write down your desired result, i think i not really get what you really want. – c0delama Jul 06 '16 at 16:04
  • @nidhoeggr09, OK I updated my post, is that clear now? Sorry if it wasn't before... – Kr1 Jul 06 '16 at 16:13
  • I updated my answer. It's not the solution but i think this link answers exactly what you need to solve your problem. – c0delama Jul 06 '16 at 16:21
  • @nidhoeggr09, thank you for the answer. I checked the link, but I think I will dismiss this possibility at this point. It's much more complicated than I thought. I can't pass so much time on this feature, unfortunately there is no budget for that :) I think I will explore another way to achieve that, by saving the data in another way for example.. – Kr1 Jul 06 '16 at 17:13
  • What you can do of course is just getting the title, nid and type with one query and loop over the result. In the loop you can easily query the authors and concatenate the result in your code. That will afford much more queries than the other solution, but is also a lot easier. – c0delama Jul 06 '16 at 17:35
  • Yeah I know I also considered this option, but concluded it was very very bad for performance. But why not? May be I can have a try! – Kr1 Jul 06 '16 at 22:47
0

You can't suppress "duplicates" when joining in that manner, assuming the RHS has multiple rows that join to the given LHS tuple on the indicated join attributes.

I would suggest that you are using the inner join incorrectly. Its standard use is to unite master-detail relationships. If you are using it to achieve any other semantic, you can't complain about odd results, as it's doing exactly what it is supposed to do in the situation (which you imposed) for which it was not originally intended to serve.

Bruce David Wilner
  • 463
  • 2
  • 4