0

I thought this would be a simple query, but I'm obviously having problems with the query logic for outputting the data as desired.

I'd like to query two tables that have a key to relate the tables, and output the results from table1 (one row/record output) and then output the results from table2 (multiple rows/records output).

I've tried UNIQUE, DISTINCT and subqueries to no avail.

Here is the table info. and fields:

tblfilm:

filmID (primary key)

film

film1 (record)

tblfilmimage:

imageID

filmID (foreign key, i.e. primary key from tblfilm)

image

image1 (record)

image2 (record)

And here is the most recent SELECT statement I've tried:

SELECT film, image FROM tblfilm, tblfilmimage
WHERE tblfilm.filmID = 2

filmID = 2 is unique to one film in tblfilm, and = to multiple images in tblfilmimages, that show actors from that film. Thus the overall goal is to be able to output multiple images for one film.

Current Undesired Output:

film1 image1 

film1 image2

*I'm trying to eliminate the second film1 record from being output.

Desired Output:

film1  image1 image2

I'm pretty new to using mysql when querying multiple tables. And I'm sure there is an easy solution to this but I just can't seem to wrap my head around the logic. Thus any comments or suggestions appreciated. Thank you.

UPDATE: I now realize my original question logic was flawed ... and my example not very clear. The solution given by those that responded was correct for what I originally wrote. Thank you. I will try to clarify what I am trying to accomplish.

TableA has a list of many films (example):

Wizard of OZ

Gone with the Wind ... etc.

TableB has a list of images (example):

Wizard of Oz Image1

Wizard of Oz Image2

Wizard of Oz Image3 ...etc.

Gone with the Wind Image1

Gone with the Wind Image2 ... etc.

The desired output of the query would be:

Wizard of OZ

Wizard of Oz Image1

Wizard of Oz Image2

Wizard of Oz Image3

Gone with the Wind

Gone with the Wind Image1

Gone with the Wind Image2

... and so on with hundreds of films and hundreds more images.

The solution to my original question showed me how to prevent multiple instances of the film name record within the output, but requires knowing and coding the ImageID for every image that is associated with a film. Every film has at least one image to associate. Most films have many and a varying number of images associated with the film. But the images associated with each film are unique to one film. Thus I need a way to iterate through each film, find all the images associated with each film via the (filmID) which is a common field to each table (TableA - films & TableB - images). I think I will need a variable to store each unique film and a way to associate that film variable with a variable that stores all of the different images that have the same filmID as the film. I will keep trying to figure out how to do that, but if anyone wants to point me in the right direction it would be appreciated. Thank you.

obcbeatle
  • 81
  • 1
  • 6

1 Answers1

1

Use correlated subqueries within your queries by common column filmID to produce new columns :

SELECT film, 
       (SELECT image FROM tblfilmimage WHERE imageID=1 and filmID = f.filmID ) as image1, 
       (SELECT image FROM tblfilmimage WHERE imageID=2 and filmID = f.filmID ) as image2
  FROM tblfilm f
 WHERE f.filmID = 2;

or, use conditional aggregation directly as a better option:

SELECT film, 
       MAX(CASE WHEN i.imageID=1 THEN i.image END ) as image1, 
       MAX(CASE WHEN i.imageID=2 THEN i.image END ) as image2
  FROM tblfilm f
  JOIN tblfilmimage i on i.filmID = f.filmID
 WHERE f.filmID = 2
 GROUP BY film;

Where the values for ImageID columns are just presumed by me.

In your case you had a CROSS JOIN by using comma-seperated tables. So, there were multiple rows generated without correlation through that. This style is not suggested, and considered as old-syntax for SQL Select Statements. Whenever JOIN is needed, prefer using this JOIN syntax which is called ANSI-92 standard, easier to read, understand, and maintain.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • the general way of pivoting in MySQL is with GROUP BY / MAX(CASE END) which is generally is faster then using co-related subqeuries.. Also this method works when joins are needed.. – Raymond Nijland Jul 14 '19 at 21:26
  • 1
    uhmm.. your conditional aggregation query is invalid as you can't mix aggregated columns with a non aggregated column without using group by.. – Raymond Nijland Jul 14 '19 at 21:32
  • 1
    `your conditional aggregation query is invalid` was enough :) – Barbaros Özhan Jul 14 '19 at 21:35
  • Thank you @Barbaros Özhan. Unfortunately, when I tried the preferred "conditional aggregation" statements I received the mysql error - #1054 - Unknown column 'imageID' in 'field list' - I suspect because imageID only resides in the table tblfilmimage, which has not being defined here. It's unclear to me how to add tblfilmimage to the two MAX (CASE ... statements so that they can reference the imageID column. I have tried to add tblfilmimage to the "conditional aggregation" statements you shared but I think I'm getting the syntax incorrect within the MAX(CASE ... statements. – obcbeatle Jul 15 '19 at 19:47
  • @obcbeatle excuse me, of course we need a JOIN among those tables. I fixed. – Barbaros Özhan Jul 15 '19 at 20:04
  • Thank you Barbaros Özhan & Raymond Nijland, that fixed the mysql error and I believe I now understand how to use JOIN and GROUP BY / MAX(CASE END). However .. I realize now my original question was a bit flawed because of my unclear example. My apologies. I will attempt to update my original question with a more precise example of what I am trying to achieve. Thank you. – obcbeatle Jul 15 '19 at 21:54