I have a question on a select query, which doesn’t seem as straight forward as it might first seem. Taking the below tables as a point of reference.
tblStore
id | store
1 | sofaworld
2 | toysRmine
3 | books
tblStoreItem
id | item | fkid
1 | hello | 1
2 | world | 1
3 | foo | 2
4 | bar | 2
5 | there | 3
6 | that | 3
tblItemRating
id | itemrating | trusted | ratedbyId | fkid
1 | 5 | Y | 1 | 1
2 | 2 | null | 2 | 2
3 | 4 | Y | 3 | 2
4 | 5 | Y | 4 | 3
5 | 1 | null | 5 | 4
6 | 1 | null | 6 | 4
7 | 4 | Y | 7 | 5
8 | 5 | Y | 8 | 6
9 | 4 | N | 9 | 6
tblUser
id | name
1 | bob
2 | jim
3 | jack
4 | jon
5 | him
6 | her
7 | joe
8 | bill
9 | jill
I’m trying to retreive the data from tblStore and the list of items from tblStoreItem, which is fine as a simple inner join accomplishes this.
The interesting part is trying to add tblItemRating data into the select query when trusted=‘Y’ (so it only selects the one item rating against a single item), but there is also the possibility that the rating value can be null for one or more rows associated to an Item (as with id=4 and id=5 for tblItemRating), I’d still want a row returned but the fields be null.
Somethings like this should be returned:
storeid | store | itemid | item | ratingid | itemrating | trusted | ratedby.name
1 | sofaworld | 1 | hello | 1 | 5 | Y | bob
1 | sofaworld | 2 | world | 3 | 4 | Y | jack
2 | toysRmine | 3 | foo | 4 | 5 | Y | jon
2 | toysRmine | 4 | bar | null | null | null | null
3 | books | 5 | there | 7 | 4 | Y | joe
3 | books | 6 | that | 8 | 5 | Y | bill
I’m not sure it can be done using an outer join, because when doing trusted=‘Y’ into the where clause it won’t pick that record up at all. I thought might be easily answered by a subselect until I realised that a subselect can only return one column.
Does anyone have any suggestions? Hopefully what I explained made some sense!