Hi all
First time I’ve really tried subqueries (I’m sure this is what I need) but having difficulty getting my head around it.
Table - logoSponsorTypes
Table - largeLogos
Table - smallLogos
I need to be able to display all large logos, then all small logos for each logoSponsorType.
This is a simplistic view of what I am trying to do, but if I can understand this, I may be better placed to crack how to do it in my system.
You could accomplish this in a variety of ways, generally with multiple queries. What exactly would you like the data coming out of this single query to contain?
who told you you need subqueries?
SELECT logoTypeId
, 'L' AS logo_size
, LargeLogoName AS name
FROM largeLogos
UNION ALL
SELECT logoTypeId
, 'S'
, smallLogoName
FROM smallLogos
ORDER
BY logoTypeId
, logo_size
Thanks very much for taking a look at this!!
I’ll expand on what I have
-------1-----------sponsor------------1
-------2---------media partner-------3
-------3---------gold sponsor---------2
-------1---------------3-----------------43-------------jeff.jpg-------------1
-------2---------------1-----------------43-------------pete.jpg-------------2
-------3---------------2-----------------43-------------fred.jpg-------------1
-------4---------------8-----------------43-------------toby.jpg-------------3
-------5---------------9-----------------42-------------heth.jpg-------------1
-------6---------------1-----------------54-------------paul.jpg-------------2
logosSmall
logoSmallId | logoSmallType | logoSmallEvent | logoSmallSrc | logoSmallOrder
-------1---------------3-----------------43----------company1.jpg-------------1
-------2---------------2-----------------43----------company2.jpg-------------2
-------3---------------3-----------------43----------company3.jpg-------------3
-------4---------------1-----------------43----------company4.jpg-------------4
-------5---------------1-----------------19----------company5.jpg-------------1
-------6---------------1-----------------25----------company6.jpg-------------1
Right, just explaining it to someone else has made it a bit clearer in my head, but essentially, I need to:
List the logoTypeTitle for the first logotypeId, then
List all of the large logos of logoType 1 in the order specified in the logosLarge table, then
List all of the small logos of logo type 1 in the order specified in the logosSmall table, then
Repeat for each logo type and ORDER BY logoTypeOrder ASC
Hopefully this all makes sense. It makes much more sense to me now, but I’m still not sure of the best way of doing it.
Many thanks again
did you even look at the query i wrote for you?
obviously, it does not include anything about logoTypeOrder or logoLargeOrder or logoSmallOrder, because you neglected to mention them in your first post, so you’re going to have to graft them onto my query yourself
Hi Rudy
I thought that after my appalling attempt to explain this the first time, things may have changed since I explained this better. :D:D
I’m working through your suggestion now.
Many thanks
Just to complicate things a bit more 
…
Why do you have two identically structured tables for the logos?
Wouldn’t one Logos table with an extra column (logoSize or something like that) be easier?
Hi Guido
A realised that as soon as I started this, but I am rebuilding a system that has been in place for some time and the way the images are handled at the front end wouldn’t make this possible.
I have been doing some further reading round and experimenting and have come up with the following:
SELECT * FROM `logoTypes` INNER JOIN `logoLarge` ON logoTypes.logoTypesId = logosLarge.logosLargeType
UNION
SELECT * FROM `logoTypes` INNER JOIN `logosSmall` ON logoTypes.logoTypeId = logosSmall.logosSmallType
This passes the query test in phpmyadmin where I’m testing, but does not return any results from the logosSmall table.
Not sure of the efficiency of this either, but I think I’m getting closer.
Rudy, reading through your query, would I not have to have one query for each of the logoTypeIds? I just cant get my head around it. Maybe it was my poor initial explanation.
yes it does, those results are there, but you might have overlooked them because the column names of the result set are taken from the first SELECT in the union, so it just appears that only the first SELECT ran – but if you think about it, you can’t suddenly have a column name in a result set change half way through
no
the column names of the result set are taken from the first SELECT in the union
I see, does that mean I’m doing it wrong? Is there a way to handle this so I can identify which results are from the largeLogos table and which are from the small? Or where the cut off is? Otherwise I may as well stick them all in the same table.
no
AAGGHH I just cant understand it. Time for some more coffee.
sure
there’s an example of how to do that in the query i posted