Problem with SQL statement in Views D7

vfranklyn's picture

Hi there, I'm hoping a SQL ninja will find it in his or her heart to provide me with some advice.

I've created a page view which does the following:

  1. Pulls in nodes from two different content types
  2. Filters by two different term references, one for each content type.

It's not working, although in my mind (and limited sql understanding), it should. I think maybe there's an issue with the inner joins, because it seems the sql statement would working if they weren't there.

Anyways, I've attached two images, one of the api and one of the actual sql statement.

Any suggestions are greatly appreciated.

AttachmentSize
sql-statement.jpg77.63 KB
sql-statement-2.jpg75.41 KB

Comments

Inner Joins

mpruitt's picture

Inner Joins only return results if the data you're joining on is present. Otherwise, no row.

Are you getting any errors on the page?

If you edit the view and preview it, do you see any data there?

Lastly, have you checked the reports section of the site (must be logged in with admin privileges) to see if there are are errors recorded from trying to display the view?

Regards,

Mark

Mark Pruitt
Developer of The Linux Foundation (retainer)
Columbia, SC area

As Mark said, Inner joins

franperr's picture

As Mark said, Inner joins means MySQL will return results if you have a value in both table.
Here views is doing an inner join between the nodes table (which contains all the nodes), the field_data_field_literature table (which contains the value of the field literature) and the field_data_field_genres (which contains the value of the field genre).
To have a result, you need to have nodes that have values in both fields and these values must verify the criterias you put into the "filter part" of the view.

Thank you so much, you guys,

vfranklyn's picture

Thank you so much, you guys, for helping me better understand inner joins. That helps me a lot. I also got a phone call (!) from a very nice guy named Joseph who talked me through setting up the term references as Relationships and using Contextual Filters instead. I didn't get it to work yet, but I'm still messing with it. He also suggested using an Attachment view instead, which I will be definitely doing on other views, and this one if I can't get Relationships to work.

Mark, I love your troubleshooting ideas and will definitely use them with future views issues.

Really guys, thanks again!!

V

Thank you so much, you guys, <duplicate, sorry!>

vfranklyn's picture

Thank you so much, you guys, for helping me better understand inner joins. That helps me a lot. I also got a phone call (!) from a very nice guy named Joseph who talked me through setting up the term references as Relationships and using Contextual Filters instead. I didn't get it to work yet, but I'm still messing with it. He also suggested using an Attachment view instead, which I will be definitely doing on other views, and this one if I can't get Relationships to work.

Mark, I love your troubleshooting ideas and will definitely use them with future views issues.

Really guys, thanks again!!

V

Denver / Boulder Colorado (DBUG)

Group categories

Group events

Add to calendar

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds:

Hot content this week